How to Audit Report Execution in SSRS

Home > Blogs > SQL Server > How to Audit Report Execution in SSRS

How to Audit Report Execution in SSRS

4 1 Peter Avila
Added by February 21, 2014

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.


Videos You May Like

Agile Methodology in Project Management

0 166 0

In this video, you will gain an understanding of Agile and Scrum Master Certification terminologies and concepts to help you make better decisions in your Project Management capabilities. Whether you’re a developer looking to obtain an Agile or Scrum Master Certification, or you’re a Project Manager/Product Owner who is attempting to get your product or … Continue reading Agile Methodology in Project Management

JavaScript for C# Developers – September 24, 2014

0 495 3

Is JavaScript worth taking the time to learn if I’m a server-side .NET developer? How much of C# carries over to JavaScript? In this recorded video from Dan Wahlin’s webinar on September 24,2014, Dan answers these questions and more while also discussing similarities between the languages, key differences, and the future of JavaScript (ES6). If … Continue reading JavaScript for C# Developers – September 24, 2014

How to clone a Windows Server 2012 or 2012 R2 Domain Controller

3 1632 3

One of the coolest new features in Window Server 2012 and Windows Server 2012 R2 is the ability to clone a Domain Controller. In the past, if we had virtualized Domain Controllers and we actually took a snapshot of it and then rolled back to that snapshot, it would break the logon service on that … Continue reading How to clone a Windows Server 2012 or 2012 R2 Domain Controller

Write a Comment

See what people are saying...

    Share your thoughts...

    Please fill out the comment form below to post a reply.