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