SSRS Report Usage Queries

Download PDF

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;

Posted in SSRS Tagged with: , , , , , , ,
3 comments on “SSRS Report Usage Queries
  1. Souvik Kumar Bari says:

    Thank you very much!

  2. Alex says:

    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

Leave a Reply

Your email address will not be published. Required fields are marked *

*

Time limit is exhausted. Please reload CAPTCHA.