SSRS – Queries for monitoring report usage

Iuse these queries as a good starting point to assess a Reporting Services instance in terms of how long various reports take to run and who is using them. They are also useful to track usage by day.

These queries can also form the basis of a report that reports on reports. A meta-report as it were.

I probably wrote these based on a post by someone else a few years ago but I can’t recall the original source. The four queries are based on the same one with slight changes in order to show quite different information.

I hope they are as useful to someone else as they have been to me.


 
USE [ReportServer] --Or Report Server DB name

 

/* 1 General Report stats */

select

cat.path

,cat.name

,CONVERT(nvarchar(10), min(ex.TimeStart), 103) as Earliest_run

,CONVERT(nvarchar(10), max(ex.TimeStart), 103) as Most_Recent_run

,sum(case when ex.Format = 'RPL' then 1 else 0 end) as OnScreen

,sum(case when ex.Format = 'EXCEL' then 1 else 0 end) as Excel

,sum(case when ex.Format = 'MHTML' then 1 else 0 end) as MHTML

,sum(case when ex.Format = 'PDF' then 1 else 0 end) as PDF

,sum(case when ex.Format = 'CSV' then 1 else 0 end) as CSV

,sum(case when ex.Format = 'HTML4.0' then 1 else 0 end) as HTML4

,sum(case when ex.Format = 'ATOM' then 1 else 0 end) as ATOM

,sum(case when ex.Format = 'IMAGE' then 1 else 0 end) as [IMAGE]

,sum(case when ex.Format = 'WORD' then 1 else 0 end) as WORD

,sum(case when ex.Format = 'XML' then 1 else 0 end) as [XML]

,sum(case when ex.Format NOT in

('EXCEL','MHTML','PDF','CSV','HTML4.0','RPL','ATOM','IMAGE','WORD','XML')

then 1 else 0 end) as Other

,AVG(Datediff(ms,ex.timestart,ex.timeend)/1000.0) as execution_duration_seconds_average,

Max(Datediff(ms,ex.timestart,ex.timeend)/1000.0)

- Min(Datediff(ms,ex.timestart,ex.timeend)/1000.0) as execution_duration_seconds_variance

,AVG(ByteCount) as Bytes

FROM ExecutionLog AS ex JOIN Catalog AS cat ON ex.ReportID = cat.ItemID

group by  cat.path, cat.name

Order by sum(case when ex.Format = 'RPL' then 1 else 0 end) desc;

 

/*2 Show list of Users by highest usage*/

 

/* Optional - limit to one report

Declare @ReportName nvarchar(100)

Set @ReportName = 'MyActivity' */

select x.UserName, sum(OnScreen) as Reports_Viewed from

(

select

cat.path

,cat.name

,ex.UserName

,CONVERT(nvarchar(10), ex.TimeStart, 103) as Run_Date

,sum(case when ex.Format = 'RPL' then 1 else 0 end) as OnScreen

,sum(case when ex.Format = 'EXCEL' then 1 else 0 end) as Excel

,sum(case when ex.Format = 'MHTML' then 1 else 0 end) as MHTML

,sum(case when ex.Format = 'PDF' then 1 else 0 end) as PDF

,sum(case when ex.Format = 'CSV' then 1 else 0 end) as CSV

,sum(case when ex.Format = 'HTML4.0' then 1 else 0 end) as HTML4

,sum(case when ex.Format = 'ATOM' then 1 else 0 end) as ATOM

,sum(case when ex.Format = 'IMAGE' then 1 else 0 end) as [IMAGE]

,sum(case when ex.Format = 'WORD' then 1 else 0 end) as WORD

,sum(case when ex.Format = 'XML' then 1 else 0 end) as [XML]

,sum(case when ex.Format NOT in

('EXCEL','MHTML','PDF','CSV','HTML4.0','RPL','ATOM','IMAGE','WORD','XML')

then 1 else 0 end) as Other

,AVG(Datediff(ms,ex.timestart,ex.timeend)/1000.0) as execution_duration_seconds

,AVG(ByteCount) as Bytes

FROM ExecutionLog AS ex JOIN Catalog AS cat ON ex.ReportID = cat.ItemID

--and cat.name = @ReportName

group by  cat.path, cat.name ,ex.UserName

, CONVERT(nvarchar(10), ex.TimeStart, 103)

) x

Group by x.UserName

Order by sum(OnScreen) Desc;

 

 

 

/* 3 Show total usage by report by date */

/* Preferable - limit to one report */

Declare @ReportName nvarchar(100)

Set @ReportName = 'MyActivity'

select

cat.path

,cat.name

,Convert(Datetime,CONVERT(nvarchar(10), ex.TimeStart, 103),103) as Run_Date

,sum(case when ex.Format = 'EXCEL' then 1 else 0 end) as Excel

,sum(case when ex.Format = 'MHTML' then 1 else 0 end) as MHTML

,sum(case when ex.Format = 'PDF' then 1 else 0 end) as PDF

,sum(case when ex.Format = 'CSV' then 1 else 0 end) as CSV

,sum(case when ex.Format = 'HTML4.0' then 1 else 0 end) as HTML4

,sum(case when ex.Format = 'ATOM' then 1 else 0 end) as ATOM

,sum(case when ex.Format = 'IMAGE' then 1 else 0 end) as [IMAGE]

,sum(case when ex.Format = 'WORD' then 1 else 0 end) as WORD

,sum(case when ex.Format = 'XML' then 1 else 0 end) as [XML]

,sum(case when ex.Format NOT in

('EXCEL','MHTML','PDF','CSV','HTML4.0','RPL','ATOM','IMAGE','WORD','XML')

then 1 else 0 end) as Other,sum(case when ex.Format = 'RPL' then 1 else 0 end) as OnScreen

,AVG(Datediff(ms,ex.timestart,ex.timeend)/1000.0) as execution_duration_seconds_average,

Max(Datediff(ms,ex.timestart,ex.timeend)/1000.0)

- Min(Datediff(ms,ex.timestart,ex.timeend)/1000.0) as execution_duration_seconds_variance

,AVG(ByteCount) as Bytes

FROM ExecutionLog AS ex JOIN Catalog AS cat ON ex.ReportID = cat.ItemID

and cat.name = @ReportName

group by  cat.path, cat.name

, CONVERT(nvarchar(10), ex.TimeStart, 103)

Order by Convert(Datetime,CONVERT(nvarchar(10), ex.TimeStart, 103),103);

 

 

 

/* 4 Show total usage by report by date by user */

Declare @Report nvarchar(100)

Set @Report = 'MyActivity'

select

cat.path

,cat.name

,ex.UserName

,CONVERT(nvarchar(10), min(ex.TimeStart), 103) as Earliest_run

,CONVERT(nvarchar(10), max(ex.TimeStart), 103) as Most_Recent_run

,CONVERT(nvarchar(10), ex.TimeStart, 103) as Run_Date

,sum(case when ex.Format = 'RPL' then 1 else 0 end) as OnScreen

,sum(case when ex.Format = 'EXCEL' then 1 else 0 end) as Excel

,sum(case when ex.Format = 'MHTML' then 1 else 0 end) as MHTML

,sum(case when ex.Format = 'PDF' then 1 else 0 end) as PDF

,sum(case when ex.Format = 'CSV' then 1 else 0 end) as CSV

,sum(case when ex.Format = 'HTML4.0' then 1 else 0 end) as HTML4

,sum(case when ex.Format = 'ATOM' then 1 else 0 end) as ATOM

,sum(case when ex.Format = 'IMAGE' then 1 else 0 end) as [IMAGE]

,sum(case when ex.Format = 'WORD' then 1 else 0 end) as WORD

,sum(case when ex.Format = 'XML' then 1 else 0 end) as [XML]

,sum(case when ex.Format NOT in

('EXCEL','MHTML','PDF','CSV','HTML4.0','RPL','ATOM','IMAGE','WORD','XML')

then 1 else 0 end) as Other

,AVG(Datediff(ms,ex.timestart,ex.timeend)/1000.0) as execution_duration_seconds

,AVG(ByteCount) as Bytes

FROM ExecutionLog AS ex JOIN Catalog AS cat ON ex.ReportID = cat.ItemID

and cat.name =  @Report

group by  cat.path, cat.name ,ex.UserName

, CONVERT(nvarchar(10), ex.TimeStart, 103)

/* Optional only show records since this date */

HAVING max(ex.TimeStart) >= convert(Datetime,'25/05/2012',103);

Leave a Reply

Your email address will not be published. Required fields are marked *