SSRS Report Usage Queries
This last week I had the opportunity to do some work with SSRS, determining some stats on report usage. I ended up digging up some queries that I wrote a couple years ago against the ReportServer database, and thought they would be worthwhile to share, so here they are. These have been tested against SQL Server 2008, and 2008R2 databases.
I hope these can save you some time if you need to track down details on SSRS report usage.
There is one place where the text DOMAIN_NAME is referenced. That is intended to be replaced with your current domain.
Find out how many reports executions are in the log, and the oldest TimeStart
-- Written by Steve Stedman http://SteveStedman.com SELECT COUNT(*), MIN(ExecutionLog.TimeStart) FROM [ReportServer].[dbo].ExecutionLog(NOLOCK);
Just looking at what is in the log.
-- Written by Steve Stedman http://SteveStedman.com SELECT TOP 100 c.Name, c.[Path], l.InstanceName, l.ReportID, l.UserName, l.RequestType, l.Format, l.Parameters, l.TimeStart, l.TimeEnd, l.TimeDataRetrieval, l.TimeProcessing, l.TimeRendering, l.Source, l.Status, l.ByteCount, l.[RowCount] FROM [ReportServer].[dbo].[ExecutionLog](NOLOCK) AS l INNER JOIN [ReportServer].[dbo].[Catalog](NOLOCK) AS c ON l.ReportID = C.ItemID WHERE c.Type = 2 -- Only show reports 1=folder, 2=Report, 3=Resource, 4=Linked Report, 5=Data Source ORDER BY l.TimeStart DESC;
List the reports with the number of executions and time last run:
-- Written by Steve Stedman http://SteveStedman.com SELECT c.Name, c.[Path], COUNT(*) AS TimesRun, MAX(l.TimeStart) AS [LastRun] FROM [ReportServer].[dbo].[ExecutionLog](NOLOCK) AS l INNER JOIN [ReportServer].[dbo].[Catalog](NOLOCK) AS c ON l.ReportID = C.ItemID WHERE c.Type = 2 -- Only show reports 1=folder, 2=Report, 3=Resource, 4=Linked Report, 5=Data Source GROUP BY l.ReportId, c.Name, c.[Path];
List the reports with the number of executions and time last run, including datasources.
-- Written by Steve Stedman http://SteveStedman.com SELECT c.Name, c.[Path], COUNT(*) AS TimesRun, MAX(l.TimeStart) AS [LastRun], ( SELECT SUBSTRING( ( SELECT CAST(', ' AS VARCHAR(MAX))+CAST(c1.Name AS VARCHAR(MAX)) FROM [ReportServer].[dbo].[Catalog] AS c INNER JOIN [ReportServer].[dbo].[DataSource] AS d ON c.ItemID = d.ItemID INNER JOIN [ReportServer].[dbo].[Catalog] c1 ON d.Link = c1.ItemID WHERE c.Type = 2 AND c.ItemId = l.ReportId FOR XML PATH('') ), 3, 10000000) AS list ) AS DataSources FROM [ReportServer].[dbo].[ExecutionLog](NOLOCK) AS l INNER JOIN [ReportServer].[dbo].[Catalog](NOLOCK) AS c ON l.ReportID = C.ItemID WHERE c.Type = 2 -- Only show reports 1=folder, 2=Report, 3=Resource, 4=Linked Report, 5=Data Source GROUP BY l.ReportId, c.Name, c.[Path];
List the reports with the number of executions and time last run, including datasources and who has been using the report.
-- Written by Steve Stedman http://SteveStedman.com SELECT c.Name, c.[Path], COUNT(*) AS TimesRun, MAX(l.TimeStart) AS [LastRun], ( SELECT SUBSTRING( ( SELECT CAST(', ' AS VARCHAR(MAX))+CAST(c1.Name AS VARCHAR(MAX)) FROM [ReportServer].[dbo].[Catalog] AS c INNER JOIN [ReportServer].[dbo].[DataSource] AS d ON c.ItemID = d.ItemID INNER JOIN [ReportServer].[dbo].[Catalog] c1 ON d.Link = c1.ItemID WHERE c.Type = 2 AND c.ItemId = l.ReportId FOR XML PATH('') ), 3, 10000000) AS list ) AS DataSources, ( SELECT SUBSTRING( ( SELECT CAST(', ' AS VARCHAR(MAX))+CAST(REPLACE(t.UserName, 'DOMAIN_NAME\', '') AS VARCHAR(MAX)) FROM ( SELECT TOP 100000 l2.UserName+'('+CAST(COUNT(*) AS VARCHAR(100))+')' AS UserName FROM [ReportServer].[dbo].[ExecutionLog](NOLOCK) AS l2 WHERE l2.ReportID = l.ReportId GROUP BY l2.UserName ORDER BY COUNT(*) DESC ) AS t FOR XML PATH('') ), 3, 10000000) ) AS UsedBy FROM [ReportServer].[dbo].[ExecutionLog](NOLOCK) AS l INNER JOIN [ReportServer].[dbo].[Catalog](NOLOCK) AS c ON l.ReportID = C.ItemID WHERE c.Type = 2 -- Only show reports 1=folder, 2=Report, 3=Resource, 4=Linked Report, 5=Data Source GROUP BY l.ReportId, c.Name, c.[Path];
Reports that haven’t been run since the last time the log was cleared.
-- Written by Steve Stedman http://SteveStedman.com SELECT c.Name, c.[Path] FROM [ReportServer].[dbo].[ExecutionLog](NOLOCK) AS l RIGHT OUTER JOIN [ReportServer].[dbo].[Catalog](NOLOCK) AS c ON l.ReportID = C.ItemID WHERE c.Type = 2 -- Only show reports 1=folder, 2=Report, 3=Resource, 4=Linked Report, 5=Data Source AND l.ReportID IS NULL;
-- Reports by count that have been run in the last 24 hours -- Written by Steve Stedman http://SteveStedman.com SELECT c.Name, c.[Path], COUNT(*) AS TimesRun, MAX(l.TimeStart) AS [LastRun] FROM [ReportServer].[dbo].[ExecutionLog](NOLOCK) AS l INNER JOIN [ReportServer].[dbo].[Catalog](NOLOCK) AS c ON l.ReportID = C.ItemID WHERE c.Type = 2 -- Only show reports 1=folder, 2=Report, 3=Resource, 4=Linked Report, 5=Data Source AND l.TimeStart > GETDATE() - 1 GROUP BY l.ReportId, c.Name, c.[Path];
-- Reports by count that have been run in the last 7 days -- Written by Steve Stedman http://SteveStedman.com SELECT c.Name, c.[Path], COUNT(*) AS TimesRun, MAX(l.TimeStart) AS [LastRun] FROM [ReportServer].[dbo].[ExecutionLog](NOLOCK) AS l INNER JOIN [ReportServer].[dbo].[Catalog](NOLOCK) AS c ON l.ReportID = C.ItemID WHERE c.Type = 2 -- Only show reports 1=folder, 2=Report, 3=Resource, 4=Linked Report, 5=Data Source AND l.TimeStart > GETDATE() - 7 GROUP BY l.ReportId, c.Name, c.[Path];
Report usage by user
-- Written by Steve Stedman http://SteveStedman.com SELECT l.UserName, COUNT(*) AS TimesRun, MAX(l.TimeStart) AS [LastReportRun] FROM [ReportServer].[dbo].[ExecutionLog](NOLOCK) AS l INNER JOIN [ReportServer].[dbo].[Catalog](NOLOCK) AS c ON l.ReportID = C.ItemID GROUP BY l.UserName; -- Reports Datasource -- Written by Steve Stedman http://SteveStedman.com SELECT c.name, c1.Name datasource, c.ItemId FROM [ReportServer].[dbo].[Catalog] AS c INNER JOIN [ReportServer].[dbo].[DataSource] AS d ON c.ItemID = d.ItemID INNER JOIN [ReportServer].[dbo].[Catalog] c1 ON d.Link = c1.ItemID WHERE c.Type = 2;
Long Running Reports by average execution time
-- Written by Steve Stedman http://SteveStedman.com SELECT TOP 100 c.Name, c.[Path], AVG(l.TimeDataRetrieval + l.TimeProcessing + l.TimeRendering) / 1000.0 [AverageExecutionTimeSeconds], SUM(l.TimeDataRetrieval + l.TimeProcessing + l.TimeRendering) / 1000.0 [TotalExecutionTimeSeconds], SUM(l.TimeDataRetrieval + l.TimeProcessing + l.TimeRendering) / 1000.0 / 60 [TotalExecutionTimeMinutes], COUNT(*) TimesRun FROM [ReportServer].[dbo].[ExecutionLog](NOLOCK) AS l INNER JOIN [ReportServer].[dbo].[Catalog](NOLOCK) AS c ON l.ReportID = C.ItemID WHERE c.Type = 2 -- Only show reports 1=folder, 2=Report, 3=Resource, 4=Linked Report, 5=Data Source GROUP BY c.Name, c.[Path], l.InstanceName, l.ReportID HAVING AVG(l.TimeDataRetrieval + l.TimeProcessing + l.TimeRendering) / 1000.0 > 1 ORDER BY AVG(l.TimeDataRetrieval + l.TimeProcessing + l.TimeRendering) DESC; -- List the reports with the last time run -- Written by Steve Stedman http://SteveStedman.com SELECT c.Name, c.[Path], MAX(l.TimeStart) AS [LastRun] FROM [ReportServer].[dbo].[ExecutionLog] AS l WITH (NOLOCK) INNER JOIN [ReportServer].[dbo].[Catalog] AS c WITH (NOLOCK) ON l.ReportID = C.ItemID WHERE c.Type = 2 -- Only show reports 1=folder, 2=Report, 3=Resource, 4=Linked Report, 5=Data Source GROUP BY l.ReportId, c.Name, c.[Path] ORDER BY [LastRun] DESC;
More from Stedman Solutions:
Steve and the team at Stedman Solutions are here for all your SQL Server needs.
Contact us today for your free 30 minute consultation..
We are ready to help!
Thank you very much!
Hi Steve,
I’ve created a simple report ‘Dashboard’ for our office which shows some of the stuff you have here. However, I’ve been using the Report Usage by User and wondered if there was a field which contains usergroup so I can find out which departments are using the reports and not just the user?
I’ve had a quick look around the site and found the Database Health Monitor application so I’ll check that out too, looks really nice!
Thanks
Thanks. I hope you enjoy the Database Health Monitor. Let me know if you have any questions.
-Steve Stedman
Steve, these statements are a godsend! Thanks so much!
Thanks for the feedback.
-Steve Stedman
Very helpful Steve, thank you. I have a question, is there a way to tell if the subscription sends a link or not. I am trying to create a query that could filter it down that way, but I just don’t know how.
These scripts are really helpful! I’ve created some on my own, but this library is so comprehensive…I’ll go here first next time, instead of reinventing!
Thank you for your generosity.
Donna
Thanks Donna, I am glad you found them helpful.
-Steve Stedman
does anyone know how to keep some reports out of the audit, meaning I don’t want some reports being logged in execution history? I have 5 reports that are scheduled to run every min automatically and I would like not to log that execution history.
hi steve, thanks a lot for valuable information, and i would like to know curring runing reports/subscriptions through ssms , can you please provide me any query to check ?
Thank you !!!
Hi Steve, your scripts are perfect for what I need to do…
any idea why my Execution log ( as well as log2&3) would have only 2 entries?
My report server settings are all correct
Great stuff. Do you know how I can get the datasource description in the query. I have looked in the datasource table and a few other spots to no avail. Thanks in Advance.
Good job Steve. Very helpful query set.
Really helpful, thank you!
Excellent reports! Thank you very much!
You da real mvp
Amazing work this!!
Great post Steve…
One of my requirement to find dependency objects of SSRS reports.
Are you able to help us?
I can certainly see the need for that. Sorry but I don’t have a query handy to show the dependent objects in SSRS reports.
-Steve Stedman
Great post Steve. All the queries are really helpful.
I am working on a task in which i am trying to get all the dependent objects(Table, Stored Procedures, Function, Views) for a report. The report which have stored procedure are quiet direct where i can use sp_depend or similar dmv to get the dependencies of the report stored procedure but in case of the report with an inline text query I am also extracting all the inline queries of Rdl through XML parsing and need to get dependent objects of the inline query.
I was able to Parse the XML and extract inline query but i am stuck where i have to get dependent objects of the inline text query.
Is there any Query/Method/Tool to extract Dependent Objects of the Inline Query of an SSRS report?
Here is the Question i posted on stackoverflow:
https://stackoverflow.com/questions/55875056/extract-db-objects-dependencies-from-ssrs-dataset-with-inline-query-query-type
Hi – I don’t currently have a query or easy way to do what you are asking. Perhaps someone will post a response to your stack overflow question. Sorry I couldn’t be of more help.
-Steve Stedman
Thank you for your response. Question was posted 2 months ago and no response yet so i seek help from here. I have started a bounty to see if someone can provide answer for that as well. Any help from you will be of great help.
It can be done or not?
If yes, then what methods. i tried sp_describe_first_result_set and helps for simple straight forward queries but not for queries which have little complex logic with user defined data types and all.
This is gold. Thanks so much.
The first query you gave had the date of the oldest execution. That is very good to know, and I totally neglected it. I was looking to purge unused reports, so I did a count and figured I’d ax anything that hadn’t been run in a year. Turns out I only had 3 months of data.
Good work, what about Power BI reports. I tried adjusting the DOMAIN variable but unsuccessfully.
Hi Steve,
Fantastic!!
this is just want I wanted and more.
Many thanks!
Thank you Steve. Really appreciate your work. One of the query helped me identify the reports using by particular source with their last run time.
It’s amazing. Can you help me with Admin day-to-day log activities like, if user got deleted by some one who has admin permissions and how to track those info.
Very useful set of queries. In “Reports by count that have been run in the last 24 hours” and “Reports by count that have been run in the last 7 days”, shouldn’t “>” in the WHERE clause be changed to the > symbol; and ditto in the HAVING clause of “Long Running Reports by average execution time”?
Thanks for bringing that to our attention! It seemed to be a small glitch but it’s all fixed now.
Great post Steve, still very useful as we ae running Power BI Report Server on-premises.
I had two issues which I’m happy I’ve resolved.
The count of a report in ExcutionLog3 was much greater than expected as there are actions for each data connection. I’ve filtered to ItemAction = ASModelStream as near enough to the data being retrieved, and indicator the report has run.
The other was identifying who has ability to run the report (not just who has run it).The basic query I have gone with is
select U.username, C.name, c.path
from Catalog C, Roles R, PolicyUserRole PUR, Users U
where C.path = ‘the specific report’
and R.RoleName = ‘Browser’
and PUR.PolicyID = C.PolicyID
and PUR.RoleID = R.RoleID
and U.UserID = PUR.UserID
I used path instead of name as there could be copies in different locations
Thanks for the update. I appreciate the input. -Steve Stedman
This is a fantastic and valuable scripts. Thank you so much!