Tracking report usage

Question

Is there an easy way to track who is running a given report in SSRS 2005, and at what time they are running that report? We have about 80 reports in our SSRS implementation, and are trying to see if there's any that we can safely put out to pasture. If we could easily see somehow which reports aren't being used, that would help us. Any ideas?

Belgium

Answer 1

There is some good advice and queries for generating reports on this in the following article.

For example, if you want to see the most used reports, you can do the following:

SELECT COUNT(Name) AS ExecutionCount,
       Name,
       SUM(TimeDataRetrieval) AS TimeDataRetrievalSum,
       SUM(TimeProcessing) AS TimeProcessingSum,
       SUM(TimeRendering) AS TimeRenderingSum,
       SUM(ByteCount) AS ByteCountSum,
       SUM([RowCount]) AS RowCountSum
  FROM (SELECT TimeStart,
               Catalog.Type,
               Catalog.Name,
               TimeDataRetrieval,
               TimeProcessing,
               TimeRendering,
               ByteCount,
               [RowCount]
          FROM Catalog
               INNER JOIN 
               ExecutionLog
                 ON Catalog.ItemID = ExecutionLog.ReportID
         WHERE Type = 2
       ) AS RE
GROUP BY Name
ORDER BY COUNT(Name) DESC,
         Name;

One thing to note is that by default the execution log will only keep 2 months worth of data. You can control this behaviour with the ExecutionLogDaysKept server property, see this technet article.

Suggest

I think your link to wrox is broken. I've tried to find it, but failed. – Reviel

Looks that way. Don't know if they've re-orged the forums over there, that's why I put a sample in the answer. – Suggest

updated wrox link to an archived version of the page from 2017 – Massa

@Massa - awesome, why didn't I think of that? – Suggest

Answer 2

I know this question is so old it has whiskers, but the code below will list each report once with the last time it was run. I highly recommend you create a new folder called "obsolete reports" and move old reports there rather than delete them. That will remove the clutter but still keep them available in case the Accounting Department comes after you for that report they obviously need to run once every 3.26 years.

WITH RankedReports
AS
(SELECT ReportID,
        TimeStart,
        UserName, 
        RANK() OVER (PARTITION BY ReportID ORDER BY TimeStart DESC) AS iRank
   FROM dbo.ExecutionLog t1
        JOIN 
        dbo.Catalog t2
          ON t1.ReportID = t2.ItemID
)
SELECT t2.Name AS ReportName,
       t1.TimeStart,
       t1.UserName,
       t2.Path,
       t1.ReportID
  FROM RankedReports t1
       JOIN 
       dbo.Catalog t2
         ON t1.ReportID = t2.ItemID
 WHERE t1.iRank = 1
ORDER BY t1.TimeStart;

Refluent

I upvoted this purely for the reference to the obscure reports required by the Accounting Department... too many times I've been given less than 24 hours notice to redevelop something nobody knows anything about! – Hearten

@Russell Fox as far as i know the execution log does not keep track of any sub reports called from the parent report. If that is true, i may end up obsoleting sub reports too.. Any ideas of how to exclude active sub reports from the query? – Casual

@Casual - that's a tough one, but I handle it by just naming the reports accordingly: MainReportName_Sub_WhatTheSubDoes. Numbering your reports in the name also helps this, and it makes it easier to search by the report number: "DailyFinance9427_Sub_QtrTotal". Then you can WHERE t1.iRank = 1 AND ReportName NOT LIKE '%_Sub_%' – Refluent

Answer 3

i always found the report logs are a bit hard to use. Reporting services keeps a record of all its activity in a table in the reporting database called ExecutionLog

I have a couple of reports i use that query this table, so you can find out what reports are actually used, and who the heaviest users are

Bombe

This is useful for easy & light reporting, but you are limited to the history that the server stores (I believe 3 months) and the security on the report database is limited to only certain authorized users. Still - it's an easy first step that will get you started. – Crochet

Answer 4

You can monitor the report usage using execution logs. Please check this http://technet.microsoft.com/en-us/library/aa964131(SQL.90).aspx

You can also run a query to find report usage. Check Maz's reply in this link http://www.sqlservercentral.com/Forums/Topic433562-150-1.aspx

cheers

Equation

Answer 5

This SQL will also give you the data source, user and the request type:

select row_number() over (order by LogEntryId) as Id,  LogEntryId, 
        r.Name AS Report_Name, r.Path AS Report_Path, c2.Name AS Data_Source, 
        replace(c2.ConnectString,';Unicode=True','') as ConnectString,
        SUBSTRING(r.Path, 2, LEN(r.Path) - LEN(r.Name) - 2) AS Folder_Path,
        ex.UserName, ex.Format, ex.TimeProcessing, ex.TimeRendering, ex.[RowCount],
        CAST (ex.TimeStart as date) AS TimeStart,
        DATEPART (hour, ex.TimeStart) AS StartHour,
        DATEPART (minute, ex.TimeStart) AS StartMinute,
        case  
            when ex.RequestType = 0 then 'Interactive'  
            when ex.RequestType = 1 then 'Subscription'  
            when ex.RequestType = 2 then 'Refresh Cache'  
        else 'Unknown' end RequestType,
        u.UserName as CreatedBy,
        ex.Status
    from ExecutionLogStorage ex (nolock) --exec log
        join Catalog (nolock) r on ex.ReportID = r.ItemID and r.Type = 2 --report
        join DataSource ds with (nolock) ON ds.ItemID = r.ItemID  --report to connection link
       join (select ItemID, Name, SUBSTRING(Content, CHARINDEX('<ConnectString>',Content) + 15, CHARINDEX('</ConnectString>',Content) - CHARINDEX('<ConnectString>',Content) - 15) AS ConnectString
                from  ( select ItemID, Name, CONVERT(NVARCHAR(MAX),CONVERT(XML,CONVERT(VARBINARY(MAX),Content))) As Content 
                        from Catalog with (nolock) where Type = 5) x
        ) c2  ON ds.Link = c2.ItemID -- connection
        left join Users u on u.UserID = r.CreatedByID

Langbehn

Answer 6

USE ReportServer
SELECT c.Name AS ItemName
    ,  CASE c.Type
        WHEN 1 THEN 'Folder'
        WHEN 2 THEN 'Report'
        WHEN 3 THEN 'Resource'
        WHEN 4 THEN 'Linked Report'
        WHEN 5 THEN 'Data Source'
        ELSE CAST(c.Type AS VARCHAR(100))
        END AS ItemType
    ,  c.Path AS ItemPath
    ,  ( SELECT TOP 1 TimeStart FROM dbo.ExecutionLog t1 WHERE t1.ReportID = c.ItemID ORDER BY TimeStart DESC ) AS LastRunDate
    ,  ( SELECT TOP 1 UserName FROM dbo.ExecutionLog t1 WHERE t1.ReportID = c.ItemID ORDER BY TimeStart DESC ) AS LastUser
 FROM Catalog AS c WITH (NOLOCK)
 WHERE 1=1
    --AND c.Type IN (1,2)

--uncomment if searching for reports and folders only

Sheila