Just wanted to post this in case it would be useful for any one here.
This is a SWQL query that can be used for a dashboard to have all node outages listed with start and end times as well as total downtime.
SELECT
ST.Nodes.Caption AS [Device], --Device Name
'/Orion/images/StatusIcons/Small-' + ST.Nodes.StatusIcon AS [_IconFor_Device], --Status Icon for device
ST.nodes.detailsurl as [_linkfor_Device], --Link for Device
TOLOCAL(ST.EventTime) AS [Down Event], --Time Device went Down
TOLOCAL(ET.EventTime) AS [Up Event], --Time Device went Up
CONCAT(--Downtime formatted
(CASE WHEN (SECONDDIFF(ST.EventTime,ISNULL(ET.EventTime, GETUTCDATE()))/60/60/24) >= 1 --Days
THEN CONCAT(SECONDDIFF(ST.EventTime,ISNULL(ET.EventTime, GETUTCDATE()))/60/60/24, 'd ') ELSE '' END),
(CASE WHEN (SECONDDIFF(ST.EventTime,ISNULL(ET.EventTime, GETUTCDATE()))/60/60) >= 1 --Hours
THEN CONCAT(SECONDDIFF(ST.EventTime,ISNULL(ET.EventTime, GETUTCDATE()))/60/60%24, 'h ') ELSE '' END),
(CASE WHEN (SECONDDIFF(ST.EventTime,ISNULL(ET.EventTime, GETUTCDATE()))/60) >= 1 --Minutes
THEN CONCAT(SECONDDIFF(ST.EventTime,ISNULL(ET.EventTime, GETUTCDATE()))/60%60, 'm ') ELSE '' END),
(CASE WHEN (SECONDDIFF(ST.EventTime,ISNULL(ET.EventTime, GETUTCDATE()))) >= 1 --Seconds
THEN CONCAT(SECONDDIFF(ST.EventTime,ISNULL(ET.EventTime, GETUTCDATE()))%60, 's ') ELSE '' END)) AS [Downtime]
FROM
Orion.Events AS ST --Get StartTime on Events
LEFT JOIN
Orion.Events AS ET ON ET.NetObjectID = ST.NetObjectID --Match Events with the next UP event after the DOWN event
AND ET.EventTime = (SELECT TOP 1 x.EventTime FROM Orion.Events AS [x] WHERE
x.EventTime >= ST.EventTime AND x.EventType = 5
AND x.NetObjectType = 'N' AND x.NetObjectID = ST.NetObjectID
ORDER BY x.EventTime) --SubQuery to get the next UP Event for the current device
WHERE
ST.EventType = 1 --DownEvent
AND ST.NetObjectType = 'N' --Node Events
AND DAYDIFF(ST.eventtime,GETUTCDATE()) < 30 --Get Last X days
AND ST.Nodes.CustomProperties._DeviceType = 'Branch Router' --Branch routers only
AND ST.Nodes.Status <> 9 --Status is not unamanaged
--AND ST.Nodes.caption like '%${SEARCH_STRING}%' --Used if search enabled
ORDER BY
[Down Event] DESC --Sort by most recent down event
If there are any questions or any improvements I could make please feel free to share.
