Hi,
we used below query to get node status reports for last 7 days, but it is not giving us accurate outages information.
some nodes shows it was down for 7506 mints in actual it was not down.
some node shows it was down for 2-3 mint outage time is 0
SELECT * FROM (
SELECT
Nodes.StatusLED,
Nodes.Caption,
Nodes.NodeID,
StartTime.Message,
--added convert so i can group by days
CONVERT(DATE,StartTime.EventTime) AS [DATE],
StartTime.EventTime AS DownEventTime,
( SELECT TOP 1 EventTime
FROM Events AS EndTimeTable
where EndTimeTable.EventTime >= StartTime.EventTime
AND EndTimeTable.EventType = 11
AND EndTimeTable.NetObjectType = 'I'
AND EndTimeTable.NetworkNode = StartTime.NetworkNode
AND EventTime IS NOT NULL
AND EventTime > DATEADD(DAY,-7,GETDATE())
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 = 10 AND EndTime.NetObjectType = 'I'
AND EndTime.NetworkNode = StartTime.NetworkNode AND EventTime > DATEADD(DAY,-7,GETDATE()) ORDER BY EndTime.EventTime)
) AS OutageDurationInMinutes
FROM Events StartTime
INNER JOIN Nodes ON StartTime.NetworkNode = Nodes.NodeID
---here is the custom property called n_type
WHERE (StartTime.EventType = 10) AND EventTime > DATEADD(DAY,-7,GETDATE())
) AS UpTimeTable
where outageDurationInMinutes IS NOT NULL
ORDER BY Caption ASC, DownEventTime DESC
