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.

001-How-to-Audit-Report-Execution-in-SQL-Server-Reporting-Services-SSRS

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.

002-Audit-Report-Execution-in-SQL-Server-SSRS

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

003-properties-Audit-Report-Execution-in-SQL-Server-SSRS

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.

004-enable-properties-Audit-Report-Execution-in-SQL-Server-SSRS

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.

005-advanced-properties-Audit-Report-Execution-in-SQL-Server-SSRS

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).

006-advanced-properties-Audit-Report-Execution-in-SQL-Server-SSRS

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.

007-dbo-Audit-Report-Execution-in-SQL-Server-SSRS

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

008-reportserver-Audit-Report-Execution-in-SQL-Server-SSRS

Examine the columns to see the logging schema.

009-object-explorer-schema-Audit-Report-Execution-in-SQL-Server-SSRS

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.

010-object-explorer-schema-Audit-Report-Execution-in-SQL-Server-SSRS

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!

Videos You May Like

A Simple Introduction to Cisco CML2

0 3877 0

Mark Jacob, Cisco Instructor, presents an introduction to Cisco Modeling Labs 2.0 or CML2.0, an upgrade to Cisco’s VIRL Personal Edition. Mark demonstrates Terminal Emulator access to console, as well as console access from within the CML2.0 product. Hello, I’m Mark Jacob, a Cisco Instructor and Network Instructor at Interface Technical Training. I’ve been using … Continue reading A Simple Introduction to Cisco CML2

Creating Dynamic DNS in Network Environments

0 641 1

This content is from our CompTIA Network + Video Certification Training Course. Start training today! In this video, CompTIA Network + instructor Rick Trader teaches how to create Dynamic DNS zones in Network Environments. Video Transcription: Now that we’ve installed DNS, we’ve created our DNS zones, the next step is now, how do we produce those … Continue reading Creating Dynamic DNS in Network Environments

Cable Testers and How to Use them in Network Environments

0 724 1

This content is from our CompTIA Network + Video Certification Training Course. Start training today! In this video, CompTIA Network + instructor Rick Trader demonstrates how to use cable testers in network environments. Let’s look at some tools that we can use to test our different cables in our environment. Cable Testers Properly Wired Connectivity … Continue reading Cable Testers and How to Use them in Network Environments

Write a Comment

See what people are saying...

    Share your thoughts...

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