Determining who is viewing reports in SQL Server 2012 Reporting Services
By: Dallas Snider | Comments (4) | Related: > Reporting Services Security
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.
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.
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
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
- I have provided you with some basic queries to start understanding your user behaviors, change up the query to better meet your needs.
- Check out these additional resources:
About the author
View all my tips