Who’s running my SSRS reports?

If you ever questioned who’s running your SSRS reports, or wanted to check execution details (execution time, type, etc.), then here is the good news – you can easily get all kind of different information regarding execution history.

Magic happens within two special views which reside in ReportServer database on your reporting server. These two specially created views are ExecutionLog and ExecutionLog3, which retrieve data from the underlying table ExecutionlogStorage. Here is the basic example on how you can check execution log for the report called Sales by Year, and walk through all executions since beginning of this year until the moment of the query run. Results are displayed sorted in descending order, starting from the latest execution.

USE [ReportServer] 
SELECT c.[name] as reportName 
       ,e.username as userExec 
       ,e.TimeStart 
       ,e.TimeEnd 
       ,DATEDIFF(ss,e.TimeStart,e.TimeEnd) as TimeInSeconds 
       ,e.Parameters 
       ,c.ModifiedDate as ReportLastModified 
       ,u.username as userCreated 
FROM catalog c 
       INNER JOIN executionlogstorage e on c.itemid = e.reportid 
       INNER JOIN users u on c.modifiedbyid = u.userid 
WHERE e.TimeStart >= '2020-01-01' 
       AND e.TimeEnd < getdate() 
       AND c.[name] = 'SalesByYear' 
ORDER BY timestart DESC

In short, when you run the query, you should be able to see report name, user that created the report, when the report was last modified, user that performed specific execution and, most important, time that report needed for rendering and parameters which were used. This should give you a brief overview of things happening in the background.

Get more in-depth view

In case you need to check execution log in more depth, you can run the following query:

USE [ReportServer] 
SELECT c.[Name] as reportname 
      ,e.InstanceName 
      ,e.ItemPath 
      ,e.UserName as userExec 
      ,e.RequestType --Interactive or Subscription 
      ,e.Parameters 
      ,e.ItemAction --Render or Sort 
      ,e.TimeStart 
      ,e.TimeEnd 
      ,e.TimeDataRetrieval 
      ,e.TimeProcessing 
      ,e.TimeRendering 
      ,e.Source 
      ,e.Status 
      ,e.ByteCount 
      ,e.RowCount 
FROM Catalog c 
      INNER JOIN ExecutionLog3 e ON e.ItemPath = c.Path 
WHERE c.name = 'SalesByYear' 
      AND e.TimeStart >= '2020-01-01' 
      AND e.TimeEnd < getdate() 
ORDER BY e.TimeStart DESC

Who needs them?

I also like to have an overview of which reports are being used and which can be considered as “dead wood”. Therefore, I created a simple Power BI report, which imports data from the following query in the background:

USE [ReportServer] 
SELECT distinct a.* 
      ,l.UserName 
FROM
( 
     SELECT c.[name] as reportName 
           ,SUBSTRING(SUBSTRING(path, CHARINDEX('/', path) + 1, LEN(path)),0,CHARINDEX('/',SUBSTRING(path, CHARINDEX('/', path) + 1, LEN(path)))) Folder 
           ,CASE(RequestType) WHEN 0 THEN 'Interactive' WHEN 1 THEN 'Subscription' WHEN 2 THEN 'Refresh Cache' ELSE 'Unknown' END AS RequestType 
           ,MAX(l.TimeStart) lastRunDate 
     FROM Catalog c 
           INNER JOIN ExecutionLogStorage l ON l.ReportID = c.ItemID 
     WHERE c.Type NOT IN (1,3,5,8) 
           AND l.ReportAction IN(1,13) 
     GROUP BY c.[name] 
          ,SUBSTRING(SUBSTRING(path, CHARINDEX('/', path) + 1, LEN(path)),0,CHARINDEX('/',SUBSTRING(path, CHARINDEX('/', path) + 1, LEN(path)))) 
          ,CASE(RequestType) WHEN 0 THEN 'Interactive' WHEN 1 THEN 'Subscription' WHEN 2 THEN 'Refresh Cache' ELSE 'Unknown' END 
     HAVING MAX(l.TimeStart) < getdate() - 90 
) a 
INNER JOIN ExecutionLogStorage l ON l.TimeStart = a.lastRunDate

This query returns list of reports that were not run in last 90 days. Of course, you can adjust date range according to your needs.

Last Updated on November 17, 2020 by Nikola

Spread the music: