we have the following lines in a SQL report and it provides a entry in the report for every time the device has a type 5 event which is the device is responding again.
SELECT
StartTime.EventTime AS StartTime,
Nodes.Caption AS Node,
DATEDIFF(hour, StartTime.EventTime,
(SELECT TOP 1
EventTime
FROM Events AS EndTime
WHERE EndTime.EventType = 1
AND EndTime.EventTime > StartTime.EventTime
AND EndTime.NetObjectType = 'N'
AND EndTime.NetworkNode = StartTime.NetworkNode
ORDER BY EndTime.EventTime))
AS NodeUpTime
FROM Events StartTime INNER JOIN Nodes ON StartTime.NetworkNode = Nodes.NodeID
WHERE (StartTime.EventType = 5)
AND (StartTime.NetObjectType = 'N')
AND eventtime between dateadd(month, -3, getdate()) and getdate()
AND (Nodes.SysName LIKE 'DEVICE%')
ORDER BY Nodes.Caption
---------------------------------------------------------------------------------------------
what I would like to do is improve on this to handle small outages caused by less than stellar telco performance. the goal is that if the device reports down and then comes back up in less than 15 min then ignore those two events and continue on to the next down event.
consider the following...
if the device responds at 1am and then has a 5 min outage at 1:30 and then goes down at 2am the report should have one entry that shows the device was up for 1 hour.
Any help is appreciated.
