SSRS Auditing

SQL 2008 R2

SSRS tracks report executions in the “ReportServer” database. Here a few queries that can be used on the “ReportServer” database to do auditing. This may be helpful if you need to collect auditing or reporting statistics.

Who is using reports and how regularly? – This query counts the number of distinct executions by users ordered by highest first:

SELECT UserName, COUNT(*)as Count FROM ExecutionLog
Group by Username
Order by COUNT desc

Which reports are most frequently used? – This query counts the number of distinct reports accessed order by highest first:

SELECT CL.Path, CL.Name, COUNT(*)as Count FROM ExecutionLog as EL
JOIN Catalog as CL ON CL.ItemID = EL.ReportID
Group by CL.Path, CL.Name
Order by COUNT desc

What was the most recent reports executed? – This query shows executions by most recent reports accessed:

SELECT CL.Path, CL.Name, EL.Username, EL.TimeEnd FROM ExecutionLog as EL
JOIN Catalog as CL ON CL.ItemID = EL.ReportID
Order by EL.TimeEnd Desc