SQL Report Server OPS monitor

Time for something completely different, I'm of the opinion that whilst Qlikview is great for dash-boarding, sometimes having a SQL reporting server gives you functionality that can be useful.

I've created a useful dashboard that allows monitoring of the SQL reporting execution logs.


Edit: now including screenshots of what's included



Here's the document, it's fairly basic at the moment but allows a decent level of analysis of what the pain points are.

Some of the key charts can be found below 

List of the "Biggest Users", the people that are running the most reports and "Most Popular Reports" - what they are running

"User Run Reports" - are certain days of the week causing choke points? Do I need to be careful with running subscriptions on certain days?

Format/Status Rundown, how are people consuming the data - is it all in excel? With status, am I getting failure on runtime?

Monthly Usage Comparison - which days of the month are we seeing greater traffic?

Reports Performance, which are my slowest running reports? Which reports are run the most and could be cached to increase speed? What's the difference between the slowest and the fastest run of the report?

The code behind this is as below

//Insert SQL Connection, check out this entry if you don't know how to do this

ReportsLog:
LOAD AdditionalInfo
,ByteCount
,ExecutionId
,Format
,InstanceName
,ItemAction
,if(ItemPath='','User Run Query',ItemPath) as ItemPath //If itempath is blank, this was a validation query
,Parameters
,RequestType
,RowCount
,Source
,Status
,TimeDataRetrieval
,TimeEnd
,TimeProcessing
,TimeProcessing2
,TimeRendering
,TimeStart
,UserName
,if(left(ItemPath,9)='/Datasets','Datasets','Reports') as ItemType //use the datasets prefix to separate reports from datasets
,Date(floor(TimeStart)) as ReportRunDate 
,MonthName(TimeStart) as ReportRunMonth
,Day(TimeStart) as ReportRunDay
,WeekDay(TimeStart) as ReportRunDayName
;
select *
,TimeProcessing2 = datediff(Second,TimeStart,TimeEnd)
 from ReportServer.dbo.ExecutionLog3;

//Split out path items to provide context
DataRaw:
LOAD ItemPath, SubField(ItemPath&'.rpt', '/') AS Folders
RESIDENT ReportsLog;

Folders:
LOAD ItemPath
,Folders
,1 as FolderCount
RESIDENT DataRaw where Folders<>''
and right(Folders,4)<>'.rpt';

//split out report name from path items
ReportName:
LOAD ItemPath
,left(Folders,(len(Folders)-4)) as ReportName
,1 as FolderCount
RESIDENT DataRaw where right(Folders,4)='.rpt';

DROP Table DataRaw;

//Split out based on parameters run
ParametersRaw:
LOAD ExecutionId
, SubField(Parameters, '&') AS Parameters2
RESIDENT ReportsLog;

Parameters:
LOAD ExecutionId
, Parameters2
, 1 as ParameterCount
RESIDENT ParametersRaw
where Parameters2<>'';


DROP Table ParametersRaw;