Monitoring all kinds of Reports

I got this from an article a while back and tweaked it a little.


If you want to view failed reports, you would run this:

SELECT TOP 20 C.Path, C.Name, EL.UserName, EL.Status, EL.TimeStart, EL.[RowCount], EL.ByteCount, (EL.TimeDataRetrieval+EL.TimeProcessing+EL.TimeRendering)/1000 AS TotalSeconds, EL.TimeDataRetrieval, EL.TimeProcessing, EL.TimeRendering FROM ExecutionLog EL INNER JOIN Catalog C ON EL.ReportID=C.ItemID WHERE EL.Status not like 'rsSuccess' ORDER BY TimeStart DESC

Well, I found a lot of these nifty monitoring reports, so I made this script

DECLARE @query VARCHAR(max) select @query='failed_reports' -- change to 'recent' , 'active'... IF(@query='recent') SET @query='SELECT TOP 20 C.Path, C.Name, EL.UserName, EL.Status, EL.TimeStart, EL.[RowCount], EL.ByteCount, (EL.TimeDataRetrieval+EL.TimeProcessing+EL.TimeRendering)/1000 AS TotalSeconds, EL.TimeDataRetrieval, EL.TimeProcessing, EL.TimeRendering FROM ExecutionLog EL INNER JOIN Catalog C ON EL.ReportID=C.ItemID' IF(@query='active') SET @query='SELECT TOP 10 EL.UserName, Count(*) AS ReportsRun, Count(DISTINCT [Path]) AS DistinctReportsRun FROM ExecutionLog EL INNER JOIN Catalog C ON EL.ReportID=C.ItemID WHERE EL.TimeStart>Datediff(d, GetDate(), -28) GROUP BY EL.UserName ORDER BY Count(*) DESC '  IF(@query='popular_reports') SET @query='SELECT TOP 10 C.Path, C.Name, Count(*) AS ReportsRun, AVG((EL.TimeDataRetrieval+EL.TimeProcessing+EL.TimeRendering)) AS AverageProcessingTime, Max((EL.TimeDataRetrieval+EL.TimeProcessing+EL.TimeRendering)) AS MaximumProcessingTime, Min((EL.TimeDataRetrieval+EL.TimeProcessing+EL.TimeRendering)) AS MinimumProcessingTime FROM ExecutionLog EL INNER JOIN Catalog C ON EL.ReportID=C.ItemID WHERE EL.TimeStart>Datediff(d, GetDate(), -28) GROUP BY C.Path, C.Name ORDER BY Count(*) DESC' IF (@query='failed_reports') SET @query='SELECT TOP 20 C.Path, C.Name, EL.UserName, EL.Status, EL.TimeStart, EL.[RowCount], EL.ByteCount, (EL.TimeDataRetrieval + EL.TimeProcessing + EL.TimeRendering)/1000 AS TotalSeconds, EL.TimeDataRetrieval, EL.TimeProcessing, EL.TimeRendering FROM ExecutionLog EL INNER JOIN Catalog C ON EL.ReportID = C.ItemID WHERE EL.Status not like ''rsSuccess'' ORDER BY TimeStart DESC' print (@query)