Thursday, February 10, 2011

SSRS 2008 Execution Log - Report Auditing

I wrote this query to tell me how many times a report gets excecuted, who ran it last, and when it was ran last. It's a simple, but effective query:


USE reportserver;

WITH LastExecution
AS
(
  
SELECT e.ReportID
      
,  MAX(e.timestart) AS LastExecution
      
FROM ExecutionLog  e
      
GROUP BY e.ReportID
)
,
ExecutionCount
AS
(
  
SELECT e.ReportID
      
,  COUNT(*) AS ExecutionCount
      
FROM ExecutionLog  e
      
GROUP BY e.ReportID
)
SELECT c.Path
  
, c.Name
  
, le.LastExecution
  
, ec.ExecutionCount
  
, e.UserName AS LastExecutingUser
  
FROM ExecutionLog e
  
JOIN Catalog c
      
ON e.ReportID = c.ItemID
  
JOIN LastExecution le
      
ON e.ReportID = le.ReportID AND e.TimeStart = le.LastExecution
  
JOIN ExecutionCount ec
      
ON e.ReportID = ec.ReportID;


1 comment:

  1. Very good code to audit SSRS usage, with minor changes help me to achieve my request.

    Thanks a lot!

    ReplyDelete