Example: most frequent alarm per hour

This query reports the most frequent alarms for each hour:

DECLARE @StartTime as varchar(60)

DECLARE @EndTime as varchar(60)

SET @StartTime = '2017-11-10 12:00:00'

SET @EndTime = '2017-11-10 12:10:00'

DECLARE @AlarmRaise table

(

EventTime nvarchar(60),

ID nvarchar(50),

AlarmState nvarchar(20),

SourceArea nvarchar(20),

SourceObject nvarchar(20),

SourceConditionVariable nvarchar(40)

)

INSERT @AlarmRaise select EventTime,Alarm_ID,Alarm_State,Source_Area,Source_Object,Source_ConditionVariable from Events where EventTime > @StartTime and EventTime < @EndTime and Alarm_State='UNACK_ALM'

--======================--

SELECT CAST(EventTime as date) AS ForDate,

DATEPART(hour,EventTime) AS OnHour,

Count(*) AS "Count per Hour",

SourceObject + SourceConditionVariable AS "Alarm Attribute"

FROM @AlarmRaise

GROUP BY CAST(EventTime as date),

DATEPART(hour,EventTime),

SourceObject,

SourceConditionVariable

ORDER BY ForDate ASC,OnHour,[Alarm Attribute]

This resulting report looks like this: