Home > 2010, SQL Server, SSRS > Who ran what?

Who ran what?

Request came in from above to see how many times the Finance Reports have been run and who ran them.

I took a look a look at the ReportServer database and started looking at the tables until I found the two tables that gave me what I needed.  Found that I could get this data out of the Executionlog table and join it to the Catalog table to get what I needed.

The following Script give me a list of of each report, when it was run, and who ran it.

SELECT C.NAME as ReportName, E.UserName, E.TimeStart, E.Format, E.Status
FROM EXECUTIONLOG E JOIN CATALOG C ON (E.ReportID = C.ItemID)
WHERE PATH LIKE '%Finance%'
ORDER BY [ByteCount] DESC

Report view 1

This is good, but the follow up question will be, can you get me a list of how many times a report has been run?  Why yes, I can do that too.

SELECT COUNT(Name) AS [Report Count], Name AS [Report Name]
FROM Catalog C JOIN ExecutionLog E ON C.ItemID = E.ReportID
WHERE PATH LIKE '%Finance%'
GROUP BY Name
ORDER BY COUNT(Name) DESC, Name

Report view 2

I have blacked out the names to protect the innocent and my job.

Advertisements
Categories: 2010, SQL Server, SSRS
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: