How to check who executed SCCM Configmgr reports for auditing or troubleshooting purpose

If you want to know who executed (number of times ) the Configuration manager reports (or any other SSSRS reports) or if anyone compliant that some of the Configmgr reports are running slow or timed out ,how do you find the such reports and take action ?

I receive requests from remote users saying that they have issue while running some of the custom reports but when i run ,they works fine but not for remote users. There could be so many reasons why the report loads slow (network or SQL code issues etc) which am not going to discuss here.

Recently i was checking on this to see what are the reports ran by users most of the times and ,how many times they have run ,what are the top most reports and how long these report take time to execute .

When you run the SSRS reports ,it will log lot of information back into the report executionlog .

This report execution log is stored in the Reportserver database that by default is named ReportServer .if you have custom database name ,then you must that database to run the query.

As you can see in the following reportserver ,there are 3 SQL views that contain the information about execution log

image

Below is the take from Microsoft article about these executionlog sql views.

image

Now ,lets try SQL query to pull the information about the SSRS reports with its execution time, users ,time start etc.

Use  ReportServer
select * from ExecutionLog3
order by TimeStart desc

Results using above SQL Query:

SNAGHTML287b1fd3

Below query help you to find number of times that each user run the report.

Use ReportServer
select ItemPath,UserName,count(*) [No of times executed] from ExecutionLog3
group by ItemPath,UserName
order by 3 desc

image

One of the ways to reduce the disks space/cpu I/O is to enable cache use cache option in reports ,further reading ,please have a look at https://www.enhansoft.com/blog/how-to-setup-report-caching-for-a-ssrs-report

More information about the columns and its description can be found from http://www.sqlchick.com/entries/2011/2/6/querying-the-report-server-execution-log.html

Hope it helps!

 

Leave a Reply