Do you need to know how often a report runs, who or what processes run the report, how long it takes to run, statistics on report parameters, which reports are not being used, and so on? SQL Server Reporting Services has built-in audit logging that can give you this type of information pretty easily.

Audit logging is called execution logging in SSRS but I’ll just call it logging in this article.

This article shows you how logging works in SSRS 2012. If you are using a previous version of SSRS, you will find similar capabilities. Logging in SSRS 2008 R2 is almost identical to logging in SSRS 2012.

Overview of Logging

Here’s how logging works: Logging is enabled and configured in the Reporting Services server. When it is enabled, execution audit data are recorded in the dbo.ExecutionLogStorage table in the ReportServer database. To access audit data, you can query that table directly, or you can use one of three views in the ReportServer database: ExecutionLog, ExecutionLog2, and ExecutionLog3.

Note that you’ll have to interact with two locations when you work with logging in SSRS: The Reporting Services server to enable and configure logging, and the ReportServer database where audit data are collected and from which they are accessed.

Enabling and configuring logging

Logging is enabled and configured by changing a few properties on the Reporting Services server. To access the server’s properties, we’ll need to run SQL Server Management Studio with administrative privileges. One way to do this is to right-click on a shortcut to SSMS and select Run as administrator.

In the Connect to Server window, select Reporting Services in the Server type dropdown. Also, verify that your server name and authentication method are correct before you click the Connect button.

Once you are connected to the Reporting Services server, right-click on it in Object Explorer and select Properties.

In the Logging page, verify that logging is enabled. Note that you can also specify the number of days that log entries are kept on the system.

Select the Advanced page next. Notice that the same properties from the previous step are also available here under Other as the EnableExecutionLogging and ExecutionLogDaysKept properties.

There is one more logging configuration property on this page. If you scroll all the way down the list of properties, you will see one called ExecutionLogLevel. This property can have two values: Normal and Verbose (there’s no dropdown containing those values; you just have to type in the value you want). Verbose means that additional audit data about datasources and datasets will be collected (more on this coming up).

What audit data are collected and where

Execution audit data are recorded in the dbo.ExecutionLogStorage table in the ReportServer database each time a report is executed. To see this table, we’ll need to connect to SQL Server using the Database Engine server type.

Once connected, find the dbo.ExecutionLogStorage table in the ReportServer database.

Examine the columns to see the logging schema.

Notice in particular the last column, AdditionInfo. It is an XML column. When the value of the ExecutionLogLevel property we saw earlier is set to Verbose, additional xml nodes that give you information on the datasources and datasets will become available in this column.

All the columns, including the AdditionalInfo columns are described on this Microsoft Technet page Report Server Execution Log and the ExecutionLog3 view.

Support for audit data consumption

Of course, you can just query the dbo.ExecutionLogStorage table directly when you want to find out how your reports are being used, but the ReportServer database also includes a few views that you can use instead and that were developed to make data more readable. The views are ExecutionLog, ExecutionLog2, and ExecutionLog3. These are all different versions of the same view that has evolved over the different SSRS versions. They all pull data from the same dbo.ExecutionLogStorage table, though in slightly different ways. For any new development, you’ll probably want to use ExecutionLog3 that is more developed. The other two are there for compatibility.

You can find detailed information on each of the views on the same Microsoft Technet page Report Server Execution Log and the ExecutionLog3 view.

There are also a number of sample SSRS reports that Microsoft makes available. Among those are audit reports that you can use, modify, learn from, etc. Querying and Reporting on Report Execution Log Datawill show you how to get the sample reports as well as other useful information about logging.

That should help you get started with audit logging in SSRS.

Enjoy!