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: