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;

Tagged with: , , , , , , ,
11 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

  3. Mike Murphy says:

    Steve, these statements are a godsend! Thanks so much!

  4. Shan says:

    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.

  5. Donna says:

    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

  6. prakash says:

    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.

  7. omkar says:

    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 ?

  8. Dan says:

    Thank you !!!

Leave a Reply

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

*

Time limit is exhausted. Please reload CAPTCHA.