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.

Unlocking Insights with SQL Server Reporting Services Execution Logs

SQL Server Reporting Services (SSRS) is a powerful platform for generating and managing reports. However, to get the most out of it, you need a way to analyze report usage, performance, and trends. Fortunately, the ExecutionLog table in the SSRS ReportServer database provides a goldmine of information. In this blog post, we’ll explore a series of SQL queries designed to extract valuable insights from this data.

From understanding report usage patterns to identifying performance bottlenecks, these queries help you make data-driven decisions to optimize your reporting environment.

There is one place where the text DOMAIN_NAME is referenced. That is intended to be replaced with your current domain.

1. Basic Overview of Execution Logs

Sometimes, you just need a quick overview of the log data. The following query provides the total number of entries in the log and the earliest recorded activity:

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);

2. Listing Detailed Report Executions

Need to dive into specific execution details? This query lists the most recent 100 report executions, including metadata like the user who ran the report, execution times, and formats:

-- 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;

3. Report Usage Patterns

Understanding which reports are used most frequently—and when they were last accessed—can guide decisions about resource allocation and report maintenance:

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];

4. Incorporating Data Sources

For reports that rely on specific data sources, this query extends usage metrics to include the associated data sources for each report. 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];

5. Reports and Users

By identifying not only the reports but also the users who most frequently access them, you can tailor your support and training efforts: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];

6. Identifying SSRS Reports Not Used Recently

Cleaning up unused reports can reduce clutter and improve performance. This query lists reports that haven’t been run since the execution log was cleared.

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;

Next Steps

These ssrs report usage queries represent just a glimpse of what you can do with SSRS Execution Logs. By using this information effectively, you can optimize report performance, manage user access, and even rationalize your report inventory.

If you want to learn more about SQL Server performance tuning or need help managing your SQL Server environment, check out Stedman Solutions’ Managed Services. We can help ensure your SQL Servers—and your SSRS environment—are running at peak performance. And don’t forget to explore Database Health Monitor for continuous monitoring and insights.

 

---

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!