1 Reply Latest reply on Dec 6, 2018 3:20 PM by mesverrum

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

    etcentene

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

       

       

      PLEASE HELP.

        • Re: Looking for Unacknowledged active alerts older than 24 hours custom SQL report
          mesverrum

          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