Tuesday, July 17, 2012

SQL Reporting Services (SSRS) 2008 R2 Administration Scripts

Below are a few SQL Reporting Services administration scripts that I've created to help me audit usage of the system.

1. Report Usage Count By User For The Previous 30 Days

SELECT  UserName
      
,COUNT(*) AS TimesExecuted

FROM    ReportServer.dbo.ExecutionLog3
WHERE   ItemPath LIKE '%<ReportName>%'
        
AND TimeStart > GETDATE() - 30

GROUP BY UserName
ORDER BY 2 DESC

2. Report Usage With Parameters, Row Count, Execution Time, Format and User Name

SELECT  TimeStart
      
,Source
      
,[RowCount]
      
,Parameters
      
,UserName
      
,RequestType
      
,Format
      
,TimeDataRetrieval + TimeProcessing + TimeRendering [TimeInMilliseconds]

FROM    dbo.ExecutionLog3
WHERE   ItemPath LIKE '%<ReportName>%'
        
AND TimeStart BETWEEN '3/10/12' AND '6/11/12'

ORDER BY 1 DESC

3. Report Location with Creation Date, Last Execution Date, and Usage Count

SELECT  Name
      
,CreationDate
      
,Path
      
,E.TimesExecuted
      
,E.LastExecutedFROM    Catalog
        
INNER JOIN (SELECT  ItemPath
                          
,COUNT(*) AS TimesExecuted
                          
,MAX(TimeStart) AS LastExecuted
                    
FROM    ExecutionLog3
                    
GROUP BY ItemPath) E
        
ON CATALOG.PATH = E.ItemPathWHERE   Path LIKE '/Reports%'
        
AND TYPE = 2ORDER BY 2 DESC

No comments:

Post a Comment