Example: evaluating response time for alarms

This query reports when an alarm is raised, acknowledged, and cleared. The report lists both times and duration.

DECLARE @StartTime datetime2

DECLARE @EndTime datetime2

DECLARE @TagName nvarchar(100)

SET @EndTime = getutcdate()

SET @StartTime = dateadd(n,-5000,@EndTime)

DECLARE @AlarmState TABLE

(

EventTime datetime2 NOT NULL,

Alarm_ID uniqueidentifier NOT NULL,

TypeID int,

Duration int,

Alarm_State nvarchar(10),

AckedBy nvarchar(50),

AckedFromNode nvarchar(50),

primary key clustered (EventTime, Alarm_ID, TypeID)

)

INSERT @AlarmState

SELECT EventTime, Alarm_ID,

TypeID=CASE Type WHEN 'Alarm.Clear' then 1 WHEN 'Alarm.Acknowledged' then 2 END,

Duration=CASE Type WHEN 'Alarm.Clear' then Alarm_DurationMs WHEN 'Alarm.Acknowledged' then Alarm_UnAckDurationMs END,

Alarm_State, User_Account, User_NodeName

FROM Events

WHERE EventTime > @StartTime AND EventTime < @EndTime

AND Type IN ('Alarm.Clear','Alarm.Acknowledged')

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

SELECT

e.EventTime as AlarmTime,

e.Source_ProcessVariable as TagName,

e.Alarm_Type as Type,

e.ValueString as Value,

e.Alarm_LimitString as Limit,

e.Priority,

e.Source_Area as Area,

CASE

WHEN a.EventTime IS NULL and c.EventTime IS NOT NULL then c.Alarm_State

WHEN a.EventTime IS NOT NULL and c.EventTime IS NULL then a.Alarm_State

WHEN a.EventTime > c.EventTime then a.Alarm_State

WHEN a.EventTime < c.EventTime then c.Alarm_State

ELSE e.Alarm_State END as AlarmState,

c.EventTime as ClearTime,

a.EventTime as AcknowledgeTime,

c.Duration/1000.0 as SecsInAlarm,

a.Duration/1000.0 as SecsUnAck,

e.Source_Object as Object,

e.Source_HierarchicalArea as HierarchicalArea,

a.AckedFromNode,

a.AckedBy

FROM Events e

LEFT OUTER JOIN @AlarmState c

ON c.Alarm_ID=e.Alarm_ID

AND c.TypeID=1

LEFT OUTER JOIN @AlarmState a

ON a.Alarm_ID=e.Alarm_ID

AND a.TypeID=2

WHERE e.EventTimeUtc BETWEEN @StartTime AND @EndTime

AND e.Type='Alarm.Set'

ORDER BY AlarmTime ASC

The results look like this: