Query for specific Node related alerts

Bseth01

SELECT caption, TriggerTimeStamp, ObjectName, AlertMessage
FROM Orion.AlertStatus oas
INNER JOIN Orion.AlertObjects oao ON oas.AlertObjectID = oao.AlertObjectID
INNER JOIN Orion.NodesCustomProperties cp on cp.nodeid = oao.RelatedNodeId
INNER JOIN Orion.Nodes N on N.NodeID = cp.NodeID
WHERE cp.{custom prperty field name E.g. Team/site name - fill the text and replace the curly braces} LIKE '%Node%'

Bseth01

This once found on Thwack with bit rich details. 

SELECT

o.AlertConfigurations.Name AS [ALERT NAME]

,'/Orion/NetPerfMon/ActiveAlertDetails.aspx?NetObject=AAT:' + ToString(o.AlertObjectID) AS [_LinkFor_ALERT NAME]

,CASE

WHEN o.AlertConfigurations.Severity = 2 THEN '/Orion/images/ActiveAlerts/Critical.png'

WHEN o.AlertConfigurations.Severity = 3 THEN '/Orion/images/ActiveAlerts/Serious.png'

WHEN o.AlertConfigurations.Severity = 1 THEN '/Orion/images/ActiveAlerts/Warning.png'

WHEN o.AlertConfigurations.Severity = 0 THEN '/Orion/images/ActiveAlerts/InformationalAlert.png'

WHEN o.AlertConfigurations.Severity = 4 THEN '/Orion/images/ActiveAlerts/Notice.png'

END AS [_iconfor_ALERT NAME]

,o.EntityCaption AS [ALERT OBJECT]

,o.EntityDetailsURL AS [_LinkFor_ALERT OBJECT]

,case

WHEN o.RelatedNodeCaption=EntityCaption THEN 'Self'

When o.RelatedNodeCaption!=EntityCaption THEN RelatedNodeCaption

End as [RELATED NODE]

,o.RelatedNodeDetailsURL AS [_LinkFor_RELATED NODE]

,ToLocal(o.AlertActive.TriggeredDateTime) AS [ALERT TRIGGER TIME]

,'/Orion/images/StatusIcons/Small-' + p.StatusIcon AS [_IconFor_RELATED NODE]

,CASE

when minutediff(o.AlertActive.TriggeredDateTime,GETUTCDATE())>1440 then (tostring(round(minutediff(o.AlertActive.TriggeredDateTime,GETUTCDATE())/1440.0,1)) + ' Days')

when minutediff(o.AlertActive.TriggeredDateTime,GETUTCDATE())>60 then (tostring(round(minutediff(o.AlertActive.TriggeredDateTime,GETUTCDATE())/60.0,1)) + ' Hours')

else (tostring(minutediff(o.AlertActive.TriggeredDateTime,GETUTCDATE())) + ' Minutes')

end as [Time Active]

,aa.AcknowledgedBy

,ah.Message as [Note]

From Orion.AlertActive aa

join Orion.AlertObjects o on aa.alertobjectid=o.alertobjectid

LEFT join Orion.Nodes p on p.nodeid=relatednodeid

left join orion.alerthistory ah on ah.AlertActiveID=aa.AlertActiveID and ah.EventType in (2,3)

INNER JOIN Orion.NodesCustomProperties as NCP ON P.NodeID = NCP.NodeID 

where NCP.Supported_app like '%backup%'

ORDER by o.AlertActive.TriggeredDateTime DESC