When I inherit an unruly SSRS environment (and they’re all unruly) I want to answer a few questions:
- What users are running reports?
- What reports are being executed?
The ReportServer database is very easy to understand but a majority of the queries I run hit the ExecutionLog table, or one of the ExecutionLog views. Microsoft has changed the schema of the ExecutionLog table over the years. They took the original ExecutionLog schema and created a view out of it, which tells us that there’s some integration point that couldn’t handle the schema change. They’ve also given us several views that perform some useful joins for us. Rather than re-invent the wheel, I almost always go straight to ExecutionLog2.
What users are running reports?
I use this query to quickly find the users that have executed a report in the last year. I change the time filter around but I like 1 year in environments where year-end reports are used.
SELECT DISTINCT [UserName] FROM [dbo].[ExecutionLog2] WHERE TimeStart >= GETDATE()-365
I use the results of this query to start communicated with the users and explaining the changes I may make to the SSRS environment.