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;

 

More from Stedman Solutions:

SteveStedman5
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!

32 Comments on “SSRS Report Usage Queries

  1. 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

  2. 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.

  3. 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

  4. 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.

  5. 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 ?

  6. 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

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

  8. Great post Steve…
    One of my requirement to find dependency objects of SSRS reports.
    Are you able to help us?

  9. 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.

  10. 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.

  11. 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.

  12. 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.

  13. 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”?

  14. 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

Leave a Reply

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

*