I created an SWQL query which I wanted to share with the community. This table will populate once a node status is Down and then give some information which is useful to troubleshoot or email a group as per a notification. If you have a custom property which is desired to be used as a column, this use this line code and replace the word "Name" with your custom name: Nodes.Customproperties.Namet As [Name]
Here is the code:
SELECT '' as Status, '/Orion/images/StatusIcons/Small-' + StatusIcon AS [_IconFor_Status], '/Orion/images/StatusIcons/Small-' + ' ' + StatusIcon AS [_IconFor_Node Name],
Nodes.NodeName AS Node,
Nodes.IP_Address,
Nodes.DetailsUrl AS [_LinkFor_Node],
CONCAT( ( MINUTEDIFF(T1.DownTime, GetDate())/1440) ,'d ',(( MINUTEDIFF(T1.DownTime, GetDate())/60)%24), 'h ' , (MINUTEDIFF(T1.DownTime, GetDate()) %60), 'm') as Duration,
T1.DownTime as [Down Since],
LASTBOOT FROM (SELECT MAX(tolocal(EventTime)) AS DownTime,
NetObjectID,
NetworkNode
FROM Orion.Events
WHERE (EventType = 1)
AND (NetObjectType = 'N')
GROUP BY NetObjectID, NetworkNode)
AS T1 INNER JOIN Orion.Nodes
ON T1.NetworkNode = Nodes.NodeID
WHERE Nodes.Status <> '1'
ORDER BY [DownTime] DESC,
Nodes.NodeName,
T1.DownTime ASC,
Nodes.NodeID
