Monitoring and logging Reportserver

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>'
*/