SQL Server Reporting Services (SSRS) is a fantastically useful product that enables a clean and clear interface for end-users to pull data out via a clean web interface (particularly since SQL Server 2016 updates). However, I found myself pushing out report after report and wondering how useful were these reports? And who was using them? And how often? Sure you’d know a report was important if it broke or had an issue and the help desk requests would roll in, but how does one know day to day usage of reports that are not having issues? That report built four years ago, have you not heard about it because it is churning along doing it’s business value-add thing? Or has it not been used for months and is just cluttering up the system? Or worse it has become not useful, but no one has requested a change?
The good news is that this has been thought of and that data is stored in a queryable data store on the SQL Server that has SSRS installed. Data is stored in a table called ExecutionLogStorage and is queryable like any other data source. The ReportServer database has a number of other tables such as the Catalog table, that holds details on the report itself, that can be brought in to make the report more detailed. I’ve provided a few reports that I’ve found useful here.
This is an overview, summary data extract of the SSRS instance with the main purpose of providing information on the reports that are accessed on the server. It also provides a set of data and information around those reports providing overall usage data for the period. The purpose is to highlight how much usage and what type of usage each report gets as well as providing some additional overview data on that reports.
SELECT [ReportName],
[RequestType],
COUNT(*) as [ExecutionCount],
SUM([TimeDataRetrieval] + [TimeProcessing] + [TimeRendering]) as [TotalExecutionTime],
SUM([TimeDataRetrieval] + [TimeProcessing] + [TimeRendering]) / COUNT(*) as [AverageExecutionTime],
SUM([RowCount]) as [TotalRows],
MIN(TimeStart) as [EarliestRequest],
MAX(TimeStart) as [LatestRequest]
FROM (
SELECT [ExecutionLogStorage].[LogEntryId],
[Catalog].[Name] as [ReportName],
[ExecutionLogStorage].[TimeStart],
[ExecutionLogStorage].[TimeEnd],
[ExecutionLogStorage].[Format],
CASE
WHEN [ExecutionLogStorage].[RequestType] = 0 THEN ‘Interactive’
WHEN [ExecutionLogStorage].[RequestType] = 1 THEN ‘Subscription’
WHEN [ExecutionLogStorage].[RequestType] = 2 THEN ‘Refresh Cache’
ELSE ‘Unknown’
END [RequestType],
[ExecutionLogStorage].[Status],
[ExecutionLogStorage].UserName,
[ExecutionLogStorage].TimeDataRetrieval,
[ExecutionLogStorage].TimeProcessing,
[ExecutionLogStorage].TimeRendering,
[ExecutionLogStorage].[ByteCount],
[ExecutionLogStorage].[RowCount]
FROM ReportServer.dbo.[ExecutionLogStorage]
INNER JOIN ReportServer.dbo.[Catalog] ON [ExecutionLogStorage].ReportID = [Catalog].[ItemId] and [Catalog].[Type] = 2 /* type 2 is reports */
) as RawData
GROUP BY ReportName, RequestType
ORDER BY ReportName, RequestType
The data points for each report are:
The report is grouped by ReportName and RequestType to provide that summary of how usage is occurring and for which reports. The Earliest and Latest Request fields are heavily dependent on how much data the execution log is collecting (see Limited to 60 Days by Default below).
Now that you’ve reviewed your SSRS instance and have some idea about usage, you might want to dig deeper and get more information about the reports and their usage. The following extract gets right to the raw data and provides that raw data to be used in further investigations. This extract can be refined further as needed, say to look at one specific report and the instances of usage; to look at all reports of a specific request type; or to look at reports that are taking longer, maybe by time of day even. Finally this extract is particularly useful when used with external systems, such as PowerPivot in Excel or Power BI tools or Tableau Desktop or similar.
SELECT [ExecutionLogStorage].[LogEntryId],
[Catalog].[Name] as [ReportName],
[ExecutionLogStorage].[TimeStart],
[ExecutionLogStorage].[TimeEnd],
[ExecutionLogStorage].[Format],
CASE
WHEN [ExecutionLogStorage].RequestType = 0 THEN ‘Interactive’
WHEN [ExecutionLogStorage].RequestType = 1 THEN ‘Subscription’
WHEN [ExecutionLogStorage].RequestType = 2 THEN ‘Refresh Cache’
ELSE ‘Unknown’
END [RequestType],
[ExecutionLogStorage].[Status],
[ExecutionLogStorage].[UserName],
[ExecutionLogStorage].[TimeDataRetrieval],
[ExecutionLogStorage].[TimeProcessing],
[ExecutionLogStorage].[TimeRendering],
[ExecutionLogStorage].[ByteCount],
[ExecutionLogStorage].[RowCount]
FROM ReportServer.dbo.[ExecutionLogStorage]
INNER JOIN ReportServer.dbo.[Catalog] on [ExecutionLogStorage].ReportID = [Catalog].[ItemId] and [Catalog].[Type] = 2 /* type 2 is reports */
The above is great at getting snapshots and deeper insights into the usage of our SSRS instance, but what if a report isn’t in usage (i.e. no subscriptions running, no one running the report in interactive mode, etc…). In those cases those reports will not show up in the above queries, which are focused on usage and reporting on usage. However, in many cases a listing of the reports that haven’t been used is also very valuable.
SELECT [Catalog].[Name],
[Catalog].[Path],
MAX([ExecutionLogStorage].TimeStart) as [LastExecutedOn]
FROM ReportServer.dbo.[Catalog]
LEFT JOIN ReportServer.dbo.[ExecutionLogStorage] ON [ExecutionLogStorage].[ReportID] = [Catalog].[ItemId]
WHERE [Catalog].[Type] = 2 /* exclude down to only showing reports */
GROUP BY [Catalog].[Name], [Catalog].[Path]
HAVING MAX([ExecutionLogStorage].TimeStart) IS NULL
ORDER BY [Catalog].[Name], [Catalog].[Path]
note: check out the section on “Limited to 60 Days by Default” as this query is really “reports not used during the data retention limit of the execution log” so if a report is only run once a year it might show up here if that wasn’t run recently
One final item to be aware of is that the ExecutionLogStorage data defaults to only retain data from the last sixty days. Depending on your users usage and/or your desire to report on data, that might be perfect, too much, or too little. This can be extended via Server Properties as required with details in the standard Microsoft documentation.