Example: showing average time to clearing an alarm

This query reports the average time to clear Critical, High, Medium, and Low alarms per hour.

DECLARE @start DateTime2

SET @start = '2017-12-11'

DECLARE @end DateTime2

SET @end = '2017-12-12'

-- ack time per severity per hour for high, medium and low

SELECT DATEADD(hour, DATEDIFF(hour, 0, e.EventTime), 0) as hour,

e.Severity,

avg(Alarm_UnAckDurationMs) as avg_unack,

count(*) as count

FROM Events e

WHERE

e.EventTime < @end

AND e.EventTime >= @start

AND e.Severity <=3 -- critical = 1, high = 2 medium = 3 low = 4

AND e.Type = 'Alarm.Acknowledged'

GROUP BY

DATEADD(hour, DATEDIFF(hour, 0, e.EventTime), 0),

severity

ORDER BY

DATEADD(hour, DATEDIFF(hour, 0, e.EventTime), 0),

e.severity

-- ack time by user by hour

SELECT DATEADD(hour, DATEDIFF(hour, 0, e.EventTime), 0) as hour,

avg(Alarm_UnAckDurationMs) as avg_unack,

e.User_Name,

count(*) as count

FROM Events e

WHERE

e.EventTime < @end

AND e.EventTime >= @start

AND e.Type = 'Alarm.Acknowledged'

GROUP BY

DATEADD(hour, DATEDIFF(hour, 0, e.EventTime), 0),

e.User_Name

ORDER BY

DATEADD(hour, DATEDIFF(hour, 0, e.EventTime), 0),

e.User_Name

This query results in two reports. The first one looks like this:

The second report looks like this: