Elmah SQL Log Analysis

ElmahErrors.sql

/* Useful for analyzing a Elmah logs stored in a SQL Server */
/* Version 1 */
/* --------------------------------------------------------------*/

DECLARE @StartDateTime datetime
DECLARE @EndDateTime datetime
DECLARE @HourlyThreshold int -- If error count for an hour is greater than this number display in the threshold overflow report

SET @HourlyThreshold = 10
SET @StartDateTime = DATEADD(d, -7, CAST(GETDATE() AS date)) 
SET @EndDateTime = GETDATE()

--- This query will return a total error count for each application within the date boundary
SELECT 
	Application, COUNT(*) TotalErrors 
FROM Elmah_Error 
WHERE TimeUtc > @StartDateTime AND TimeUtc < @EndDateTime
GROUP BY Application
ORDER BY TotalErrors DESC


-- Errors by hour -- summary
SELECT 
	Application, CAST(TimeUtc AS DATE) DateErrorOccured, DATEPART(hh, TimeUtc) ErrorHour, COUNT(*) TotalErrors 
FROM Elmah_Error 
WHERE TimeUtc > @StartDateTime AND TimeUtc < @EndDateTime
GROUP BY Application,CAST(TimeUtc AS DATE), DATEPART(hh, TimeUtc)
ORDER BY TotalErrors DESC


-- Errors by hour but only application that has an avg + threshold will show up on this report
SELECT Application, [Hour], AVG(Totals) AS [Avg]
FROM
(
    SELECT 
	Application, 
      [Hour] = DATEPART(HOUR,    TimeUtc),
      Totals = COUNT(*)
    FROM ELMAH_Error
      WHERE TimeUtc > @StartDateTime AND TimeUtc < @EndDateTime  
    GROUP BY 
		Application, 
		DATEPART(HOUR,    TimeUtc)
  ) AS q
GROUP BY Application, [Hour]
ORDER BY Application

-- Grouped by type and application
-- Errors by hour -- summary
SELECT 
	Application, Type, COUNT(*) TotalErrors 
FROM Elmah_Error 
WHERE TimeUtc > @StartDateTime AND TimeUtc < @EndDateTime
GROUP BY Application, Type
ORDER BY TotalErrors DESC