Hello, hope you all are doing fine.
We urgently need to develop a report which shows power outage of each node for SLA purpose. The power outage should not be mixed with normal link outage due to which node goes down. The algorithm we created in SQL does not seem to be achieving the desired result. Can anyone please assist us in this? Thanking you in advance.
SELECT
Nodes.Caption
StartTime.EventTime AS DownEventTime,
(
SELECT TOP 1 EventTime
FROM Events AS EndTimeTable
where EndTimeTable.EventTime >= StartTime.EventTime
AND EndTimeTable.EventType = 5
AND EndTimeTable.NetObjectType = 'N'
AND EndTimeTable.NetworkNode = StartTime.NetworkNode
AND EventTime IS NOT NULL
ORDER BY EndTimeTable.EventTime
) AS UpEventTime,
DATEDIFF(Mi, StartTime.EventTime,
(SELECT TOP 1
EventTime
FROM Events AS Endtime
WHERE EndTime.EventTime > StartTime.EventTime AND EndTime.EventType = 5
AND EndTime.NetObjectType = 'N'
AND EndTime.NetworkNode = StartTime.NetworkNode
ORDER BY EndTime.EventTime)) AS OutageDurationInMinutes,
DATEDIFF(Mi, StartTime.EventTime,
(SELECT TOP 1
EventTime
FROM Events AS Endtime
WHERE EndTime.EventTime > StartTime.EventTime AND EndTime.EventType = 14
AND EndTime.NetObjectType = 'N'
AND EndTime.NetworkNode = StartTime.NetworkNode
ORDER BY EndTime.EventTime)) AS OutageDurationInMinutesByPower
FROM Events StartTime INNER JOIN Nodes ON StartTime.NetworkNode = Nodes.NodeID
WHERE (StartTime.EventType = 1) AND (StartTime.NetObjectType = 'N') AND
eventtime between dateadd(month, -1, getdate()) and getdate()
ORDER BY Nodes.Caption ASC
