Monitoring and logging Reportserver

,

This Stored Procedure gives you live information of the workload on the reportserver and information about who uses wich report and how often a report is started
A report based on this SP with a short refresh time gives you a live monitoring tool.

if exists (select 1 from sysobjects where name = 'report_usage' and user_name(uid) = 'dbo' and xtype = 'P ')
   drop procedure [report_usage]
go
 

create proc [report_usage] @report varchar (75), @user varchar (15)
 
as
 
 
 

select  -- report path and name 
	C.path, 
	
	--user
	E.username,
	
	--used parameters
	E.parameters,

	E.timestart,
	E.timeend,
	
	--successfull completed or not
	E.status,
	
	C.creationdate,
	C.modifieddate,
  	
	--total number of distinct users executed a report
	(select count(distinct (E.username) )from reportserver.dbo.executionlog E)N_total_users,
	
	--total number of distinct reports
  	(select count(distinct (C.name) )from reportserver.dbo.catalog C  where type = 2)N_total_reports,
  	
	--number of distinct users who used a given report
	(select count(distinct (E.username) )from reportserver.dbo.ExecutionLog E 
  		join reportserver.dbo.catalog C on E.reportid = C.itemid
  		where c.name = @report)user_report,
  	
	--variety of reports used by a given user
	(select count(distinct (C.name) )from reportserver.dbo.ExecutionLog E 
         join reportserver.dbo.catalog C on E.reportid = C.itemid  
	  where C.type = 2 
	 
	and right(E.username,3) = @user)report_user
 	--considering your ADS accounts has  3 character names
  
from         reportserver.dbo.ExecutionLog E 
		join reportserver.dbo.catalog C on E.reportid = C.itemid

where       (C.name = @report or @report = '<all>')
	    and (right(E.username,3) = @user or @user = '<all>')

order by  TimeStart DESC
 


--execute report_usage '<all>','<all>'


/*
--to feed your report parameter @report
select name from reportserver.dbo.catalog where  type = 2
union
select '<all>'

--to feed your report parameter @user
select distinct username  from reportserver.dbo.ExecutionLog 
union
select '<all>'
*/

Rate

Share

Share

Rate