cancel
Showing results for 
Search instead for 
Did you mean: 
Create Post
Level 9

Looking for Unacknowledged active alerts older than 24 hours custom SQL report

Looking for Unacknowledged active alerts older than 24 hours custom SQL report.

PLEASE HELP.

0 Kudos
1 Reply

This is SWQL, not SQL and is meant to be used in the Custom Query resource of the web console

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]

,o.AlertActive.TriggeredMessage AS [ALERT MESSAGE]

,'/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)

where aa.acknowledgedby is null and o.AlertActive.TriggeredDateTime < addday(-1,getutcdate())

ORDER by o.AlertActive.TriggeredDateTime DESC

- Marc Netterfield, Github
0 Kudos