I searched online for a long time to Frankenstein queries together and nothing ever worked like I wanted, so I started from scratch and put together a no-nonsense, to-the-point report for downtime. Hope this will help someone, wish I had done this a long time ago!
SELECT *, CAST(SUM(OutageDurationInMinutes) / 1440 AS NVARCHAR)+' Days '+CAST((SUM(OutageDurationInMinutes) - CAST(SUM(OutageDurationInMinutes) / 1440 AS INT) * 1440) / 60 AS NVARCHAR)+'hr '+CAST((SUM(OutageDurationInMinutes)) - (CAST(SUM(OutageDurationInMinutes) / 1440 AS INT) * 1440 + CAST((SUM(OutageDurationInMinutes) - CAST(SUM(OutageDurationInMinutes) / 1440 AS INT) * 1440) / 60 AS INT) * 60) AS NVARCHAR)+'m' AS [Down Duration]
FROM
(
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
FROM Events StartTime
INNER JOIN Nodes ON StartTime.NetworkNode = Nodes.NodeID
WHERE(StartTime.EventType = 1)
) AS UpTimeTable
group by Caption,DownEventTime,OutageDurationInMinutes,UpEventTime
ORDER BY 'DownEventTime' Desc
