In SQL Server 2008 R2, the Reporting Services execution log table is called dbo.ExecutionLogStorage (whereas in 2000 and 2005, it was called ExecutionLog). This table logs an entry each time the Report Server interacts with a report after it's been deployed to the Report Server.
Execution Log Views
Since Microsoft recommends against querying the Report Server tables directly, it provides 3 standard views in the ReportServer database.
dbo.ExecutionLog: for backwards compatibility
dbo.ExecutionLog2: for SQL Server 2008
dbo.ExecutionLog3: for SQL Server 2008 R2 (same as ExecutionLog2, with 2 fields renamed: ReportPath is now ItemPath, and ReportAction is now ItemAction)
Querying the ExecutionLog3 View
Selecting all fields will return the fields shown in the following 3 screen shots.
SELECT *
FROM dbo.ExecutionLog3
ORDER BY TimeStart DESC
Robert Bruckner’s blog entry does a great job of describing each column: ExecutionLog2 View – Analyzing and Optimizing Reports
If you script out the ExecutionLog3 view, you’ll find a number of CASE statements which decode numeric values into descriptions:
Request Type
- 0: Interactive
- 1: Subscription
- 2: Refresh Cache
Report Action
I find this field particularly useful so I can only count initial rendering, so the # of executions aren’t overstated by rows when the user interacts with a toggle.
- 1: Render
- 2: Bookmark Navigation
- 3: Document Map Navigation
- 4: Drill Through
- 5: Find String
- 6: Get Document Map
- 7: Toggle
- 8: Sort
- 9: Execute
Source
I find this field useful when I’m comparing Time Rendering for the different options – i.e., if I’m deciding if it’s worth it to cache a report.
- 1: Live
- 2: Cache
- 3: Snapshot
- 4: History
- 5: Ad Hoc (i.e., Report Builder)
- 6: Session (i.e., another request within existing session)
- 7: RDCE (i.e., Report Definition Customization Extension)
When are the Execution Log Views Useful?
For those implementations where reports are delivered through Report Manager (i.e., Native Mode), the Report Server execution logs are invaluable.
I have one client who is currently exposing its SSRS reports through SharePoint 2010 Foundation – and Foundation doesn’t support Web Analytics, so we are using the Report Server execution logs rather than SharePoint logs.
Reporting on Historical Report Execution Data
After installation of SSRS, the default amount of history to be retained within the dbo.ExecutionLogStorage table is 60 days. To keep history longer, you have a couple of options:
1. Create an ETL job to copy the history out of dbo.ExecutionLogStorage into a table that’s intended to be historical. This is the ideal situation. I have not used either, but there are a couple of different Codeplex projects available to get you started:
Server Management Report Samples (SQL Server 2008)
SQL Reporting Services Audit, Log, Management & Optimization Analysis
2. Extend the amount of time history is retained within dbo.ExecutionLogStorage. If you need to buy some time before #1 can be implemented, at least this will save the history for you. To change the default:
USE ReportServer
GO
UPDATE ConfigurationInfo
SET Value = '365'
WHERE Name = 'ExecutionLogDaysKept'
If you opt to report directly from the Execution Log tables and/or views, the SQL Server Reporting Services Recipes book has a chapter, starting on page 341, devoted to Creating a Report Server Usage Report.
Finding More information
Robert Bruckner’s Blog: ExecutionLog2 View – Analyzing and Optimizing Reports
MSDN: Querying and Reporting on Execution Log Data