:::: MENU ::::

Scripts for SSRS reports usage analysis

  • Oct 03 / 2013
  • 0
dbDigger, Monitoring and Analysis, Reporting Services SSRS, T-SQL Scripts

Scripts for SSRS reports usage analysis

Here are some handy and useful scripts that may be used to analyze the report usage of your SSRS report server. All these are select scripts and are using NOLOCK hint. So feel free to add filters and customize according to your specific requirements.
To get the last Time a Report was generated we have following script. Further filters may be applied if required.

 SELECT DISTINCT C.NAME AS [Report Name]  
   ,MAX(EL.TIMESTART) AS [LAST START TIME]  
 FROM REPORTSERVER.DBO.EXECUTIONLOG(NOLOCK) EL  
 INNER JOIN REPORTSERVER.DBO.CATALOG (NOLOCK) C ON EL.REPORTID = C.ITEMID  
 GROUP BY C.NAME  
 ORDER BY C.NAME  

If you want to analyze that how many reports were viewed on a specific day then we have following script. It generates report usage Stats by Date. It is advisable to insert the name of the account that runs SSRS. The reason is that if you have a report that is run off of cache or has subscription, the counts will show here. So if you want to see reports that have been run by users only then filter out the account that runs the SSRS service.

SELECT CONVERT(VARCHAR(25), TIMESTART, 101) AS [SPECIFIC DATE]  
   ,COUNT(*) AS [Gneerated Number]  
 FROM REPORTSERVER.DBO.EXECUTIONLOG(NOLOCK)  
 --WHERE USERNAME NOT IN  
 GROUP BY CONVERT(VARCHAR(25), TIMESTART, 101)  
 ORDER BY CONVERT(VARCHAR(25), TIMESTART, 101) DESC  

Get the report server usage during the hours.

SELECT DATEPART(HOUR, TIMESTART) AS HOUR  
   ,COUNT(*) AS [Gneerated Number]  
 FROM REPORTSERVER.DBO.EXECUTIONLOG(NOLOCK)  
 --WHERE USERNAME NOT IN  
 GROUP BY DATEPART(HOUR, TIMESTART)  
 ORDER BY DATEPART(HOUR, TIMESTART)  

To get the reports generated by users we have following script.

SELECT EL.USERNAME  
   ,C.NAME  
   ,COUNT(1) AS [Gneerated Number]  
 FROM REPORTSERVER.DBO.EXECUTIONLOG(NOLOCK) EL  
 INNER JOIN REPORTSERVER.DBO.CATALOG (NOLOCK) C ON EL.REPORTID = C.ITEMID  
 GROUP BY EL.USERNAME  
   ,C.NAME  
 ORDER BY EL.USERNAME  
   ,C.NAME  
Consult us to explore the Databases. Contact us