About Me

My photo
Northglenn, Colorado, United States
I'm primarily a BI Developer on the Microsoft stack. I do sometimes touch upon other Microsoft stacks ( web development, application development, and sql server development).

Tuesday, March 18, 2014

Pull what reports were ran and by whom.

A very basic query to look at the ReportServer database to pull what reports there are, who has ran it, and how many times.

Code Snippet
  1. SELECT
  2. c.NAME,
  3. el.UserName,
  4. el.LATEST_RUN_DATE,
  5. el.NUM_TIMES_RAN,
  6. c.[path]
  7. FROM
  8. DBO.[CATALOG] AS c
  9. LEFT JOIN
  10. (
  11.     SELECT
  12.     EL.REPORTID
  13.     ,EL.UserName
  14.     ,COUNT(EL.TIMESTART) NUM_TIMES_RAN
  15.     ,MAX(EL.TIMESTART) AS LATEST_RUN_DATE
  16.     FROM
  17.     dbo.EXECUTIONLOG AS el
  18.     GROUP BY
  19.     el.REPORTID, EL.UserName
  20. )el on el.ReportID = c.ItemID
  21. WHERE
  22. C.[TYPE] = 2
  23. ORDER BY c.NAME, el.UserName