Learn more about SQL Server tools



solving sql server problems for millions of dbas and developers since 2006 join MSSQLTips for free SQL Server tips













































   Got a SQL tip?
            We want to know!

Determining who is viewing reports in SQL Server 2012 Reporting Services

MSSQLTips author Dallas Snider By:   |   Read Comments (3)   |   Related Tips: > Reporting Services Security
Problem

Much time, effort and money can be spent on providing reports to end users, so frustration can ensue when the intended audience doesn't bother to view the reports. Also, for security purposes it is a good practice to review who is looking at reports in SQL Server Reporting Services (SSRS) in case the user no longer has a need to know the information contained in the reports.  How can we find out who has accessed SSRS reports?  Check out this tip to learn more.

Solution

As in previous versions, SQL Server 2012 Reporting Services provides us with a number of tables and views to let us see who has accessed what report and when they accessed the report.

The T-SQL query below selects from the ExecutionLog2 view in the ReportServer database.

USE ReportServer;
GO
SELECT el2.username, 
el2.InstanceName, 
el2.ReportPath, 
el2.TimeStart, 
el2.TimeEnd, 
el2.[Status],
isnull(el2.Parameters, 'N/A') as Parameters 
FROM ExecutionLog2 el2
GO

The first 13 rows output from the T-SQL code are shown below. As you can see in this image, we can see who viewed which report, when they viewed the report, and which parameters if any were passed to the report. This query itself could be used within an SSRS report and then published to the report server.

The first 13 rows output from the T-SQL code are shown below.

Using a query similar to the one shown below, we can look at the TimeEnd column to determine when the reports are being viewed. This will help us gain insight into when our processes that are generating the data behind the reports should complete. We can also look at the results of this query to determine behavioral patterns of the report users.

USE ReportServer;
GO
SELECT username, convert(varchar(25),TimeEnd, 120) as AccessTime
FROM ExecutionLog2
WHERE status='rsSuccess'
AND username='STAFF\user01' 
AND ReportPath='/Sales/YTDSalesByProductCategory'
ORDER BY AccessTime desc
GO


We can look at the TimeEnd column to determine when the reports are being viewed.

The query shown below will return the report access counts per user and report for the current month.

USE ReportServer;
GO
SELECT username, ReportPath, count(*) as ViewCount
FROM ExecutionLog2
WHERE status='rsSuccess'
AND TimeEnd>=DATEADD(mm, DATEDIFF(mm, 0, GETDATE()), 0)
GROUP BY username, ReportPath
ORDER BY username, ViewCount desc
GO


The query shown below will return the report access counts per user and report for the current month.
Next Steps


Last Update: 9/26/2013


About the author
MSSQLTips author Dallas Snider
Dr. Dallas Snider is an Assistant Professor in the Computer Science Department at the University of West Florida and has 18+ years of SQL experience.

View all my tips
Related Resources


print tip Print  
Become a paid author





join MSSQLTips for free SQL Server tips     



Learn more about SQL Server tools
Post a comment or let the author know this tip helped you.

       All comments are reviewed, so stay on subject or we may delete your comment.

*Name   *Email Notify for updates



       Note: your email address is not published. Required fields are marked with an asterisk (*)


Get free SQL tips:

*Enter Code refresh code     



Friday, November 01, 2013 - 2:29:28 PM - Craig Guyer Read The Tip

Mohammed, there have been a few updates to the execution log tables over various releases.  Please see the following:

Report Server Execution Log and the ExecutionLog3 View>>  http://technet.microsoft.com/en-us/library/ms159110.aspx#bkmk_executionlog2

Reporting Services LogViewer  >> http://www.microsoft.com/en-us/download/details.aspx?id=24774

I hope that helps


Thursday, September 26, 2013 - 10:19:57 PM - Mohammed Read The Tip

Does this not work in SQL Server 2005. Is ExecutionLog2 something newly introduced? Thanks.


Thursday, September 26, 2013 - 1:25:02 PM - Gene Wirchenko Read The Tip
It seems to me that this would be very useful to counter claims that a report is necessary. "Excuse me, but the last time this report was used was six months ago, and it is a monthly report."



 
Sponsor Information







Copyright (c) 2006-2014 Edgewood Solutions, LLC All rights reserved
privacy | disclaimer | copyright | advertise | about
authors | contribute | feedback | giveaways | free t-shirt | user groups | community | events | first timer?
Some names and products listed are the registered trademarks of their respective owners.