T-SQL Tuesday #025 – Reporting on Reports

This month's T-SQL Tuesday blog party is being hosted by Allen White (Blog | Twitter) and the theme for the party is 'Sharing your T-SQL Tricks'.

This topic sounds like an easy topic to blog about but when I sat down and thought about it the first thing that had me stumped was the concept of what is considered a 'trick'. What I consider a trick might not be a trick to some people so I decided to stop thinking about tricks and start focusing on the concept of what makes someone's life/job easier. Finding out about information that I didn't know existed and learning how to take advantage of existing information always creates an 'Ah-ha' moment in my life. This brings me to my T-SQL Tuesday focus on Reporting Services.

Many people access data via Reporting Services but have you ever wondered who accessed the reports on your server or when they accessed it? If you have installed and setup Reporting Services then you would have created two databases that have the default  name reportserver and reportservertempdb. In the 'reportserver' database there is a table called Executionlog that contains data on the reports that were executed, however looking at the data in the table does not provide complete information about reports so you need to use data from other tables in order to provide useful information.

Below is an example of a query that I use for monitoring and auditing purposes:

Select  c.Name, e.UserName, e.TimeStart, e.TimeEnd,
             e.TimeDataRetrieval, e.TimeProcessing, e.[RowCount], e.Status,
             e.Format

from ExecutionLog e,[Catalog] c

where e.ReportID=c.ItemID

order by TimeStart

This query uses the data from the ExecutionLog table and the Catalog table in order to provide information about the reports. Notice that the Catalog table contains the names of the reports and all the other information comes from the ExecutionLog table.  This query can tell you who ran the report, when it was executed, how long it took to return the data and process the report, the number of rows that was returned, whether the report executed successfully or not and the rendering format. Besides using this query for auditing you can also see what reports takes the most time to be generated as well as which reports are failing to be executed.

It should be noted that the ExecutionLog table only stores 60 days of data by default. For more information about the ExecutionLog table please visit MSDN.

You should be able to create other T-SQL scripts to assist with monitoring and auditing Reporting Services by looking at some of the other tables in the 'reportserver' database!