Tuesday, 23 October 2012
What reports are being read in SSRS
If you want to check which reports are being run (or not) in SSRS, or details about which users are running reports, you can pull this information from the ReportServer database.
If you need to check you are on the right database, you can check the SSRS machine name and instance the ReportServer database relates to by running:
WHERE Client = 1
Now to find out who's being doing what ... and SQL keeps it all nicely for us in the table "ExecutionLog".
You need to join this back to Catalog to get the report names / paths, but you can then easily start to get information on the most frequently run reports, which users are running certain reports, or even which reports are slow to render.
E.g. to get details on how often a report is run:
SELECT c.Name, c.path , count(*) as 'ReportRun'
FROM ExecutionLog e
JOIN Catalog c on e.ReportID = c.ItemID
GROUP BY c.Path, c.Name
to find out which users are running a certain report:
SELECT userName, COUNT(*)
FROM ExecutionLog e
JOIN CATALOG c ON e.ReportID = c.ItemID
WHERE c.Name = 'ReportName'
GROUP BY e.UserName
I'm sure you can find other details to pull out of this table that may prove useful.
By default the retention period for the ExecutionLog table is 60 days. However this is also adjustable by updating the ConfigurationInfo table for the ExecutionLogDaysKept row. Set the number of days, or use "0" to keep data indefinitely.
SET VALUE = '-1'
WHERE Name = 'ExecutionLogDaysKept'
NOTE - Ignore books online which says this should be set to 0 - if you look at the clean up proc in the ReportServer database, namely ExpireExecutionLogEntries, you can see it actually only leaves the data if the value is set to -1, and not 0.
I would also recommend you download the SSRS Reports Dashboard. This plugs into SSMS and once installed, you just right click on a ReportServer database, and it gives you access to all sorts of info relating to the relevant SSRS instance.