Report Usage Tracking

Question

Is there an easy way to keep track of who runs this report in SSRS 2005 and what time they run this report? We have about 80 reports in our implementation of SSRS, and we are trying to find out if there are any that we can safely release into the pasture. If we could easily see which reports are not used, this will help us. Any ideas?

Answer 1

The following article has some helpful tips and queries for reporting on this subject.

For example, if you want to view the most frequently 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; 

It should be noted that, by default, the execution log stores data for 2 months. You can control this behavior using the ExecutionLogDaysKept server property, see this technet article .

Answer 2

I know this question is so old that it has sideburns, but the code below will list each report once with the last run. I highly recommend that you create a new folder called "obsolete reports" and move the old reports there, rather than deleting them. This will remove the clutter, but still save them if the Accounting Department comes after you for this report, which obviously needs to be run 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; 

Answer 3

I have always found that report logs are a bit complicated to use. Reporting Services stores a record of all its activities in a table in a report database named ExecutionLog

I have several reports that I use to query this table, so you can find out which reports are actually used and who are the heaviest users

Answer 4

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

You can also run a query to find report usage. Check the answer of the Magician in this link http://www.sqlservercentral.com/Forums/Topic433562-150-1.aspx

amuses

Answer 5

This SQL will also give you the data source, user and query 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 

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 when searching only reports and folders