9 Replies Latest reply on Oct 16, 2017 9:59 AM by mesverrum

    In need of a report that shows the span of time when an alert is triggered and when its acknowledged.

    mcordero

      We are looking to implement some Operational Level Agreements within our NOC regarding the time it takes to acknowledge an alert once it has been triggered. As the question states I'm looking for a report which would illustrate the time delta for all triggered alerts and when they were acknowledged. For example CPU alert was triggered at 9:00am and acknowledged at 9:10am.

       

      Thanks,

      MC

        • Re: In need of a report that shows the span of time when an alert is triggered and when its acknowledged.
          mesverrum

          This should give you somewhere to start, use it in the custom query resource, given your description I would probably add in some case logic to highlight any alerts that have exceeded the SLA.

           

           

          --report on alerts triggered

          select ac.Name

          ,ah.Message

          ,'/Orion/NetPerfMon/ActiveAlertDetails.aspx?NetObject=AAT:'+ToString(AlertObjectID) as [_linkfor_Name]

          ,EntityCaption as [Trigger Object]

          ,EntityDetailsUrl as [_linkfor_Trigger Object]

          ,case

          WHEN RelatedNodeCaption=EntityCaption THEN 'Self'

          When RelatedNodeCaption!=EntityCaption THEN RelatedNodeCaption

          End as [Parent Node]

          ,RelatedNodeDetailsUrl as [_linkfor_Parent Node]

          ,'/Orion/images/StatusIcons/Small-' + p.StatusIcon AS [_IconFor_Parent Node]

          ,tostring(tolocal(ah.TimeStamp)) as [Trigger Time]

          ,case when ack.timestamp is null then 'N/A'

          else tostring(minutediff(ah.TimeStamp,ack.timestamp))

          end as [Minutes Until Acknowledged]

          ,ack.Message as [Note]

          ,case when reset.timestamp is null then 'N/A'

          else tostring(minutediff(ah.TimeStamp,reset.timestamp))

          end as [Minutes Until Reset]

           

           

          FROM Orion.AlertHistory ah

          left join Orion.AlertObjects ao on ao.alertobjectid=ah.alertobjectid

          left join Orion.AlertConfigurations ac on ac.alertid=ao.alertid

          left join Orion.Actions a on a.actionid=ah.actionid

          left join Orion.Nodes p on p.nodeid=RelatedNodeID

          left join (select timestamp, AlertActiveID, AlertObjectID,message from orion.alerthistory ah where eventtype=2) ack on ack.alertactiveid=ah.AlertActiveID and ack.alertobjectid=ah.AlertObjectID

          left join (select timestamp, AlertActiveID, AlertObjectID from orion.alerthistory ah where eventtype=1) reset on reset.alertactiveid=ah.AlertActiveID and reset.alertobjectid=ah.AlertObjectID

           

           

          WHERE

          daydiff(ah.timestamp,GETUTCDATE())<30

          and ah.eventtype=0

          --and (ac.Name like '%${SEARCH_STRING}%' or EntityCaption like '%${SEARCH_STRING}%' or RelatedNodeCaption like '%${SEARCH_STRING}%')

           

           

          order by ah.timestamp desc

           

          -Marc Netterfield

              Loop1 Systems: SolarWinds Training and Professional Services

          1 of 1 people found this helpful
            • Re: In need of a report that shows the span of time when an alert is triggered and when its acknowledged.
              mcordero

              This is awesome Marc, thanks for the feedback and support. I'm not very good with queries, is there a way we could add a filter to only show Critical and Warning alerts? Also what is the time frame the query is using for selecting alerts to show in the report? My first run came back with over 8K alerts, can we filter that as well to only say the last 24 hours?

               

              Thanks again for your support,

              MC

                • Re: In need of a report that shows the span of time when an alert is triggered and when its acknowledged.
                  mesverrum

                   

                  The line where i defined the time period is this one, it is set to look back up to 30(technically 29.9999 days but you can adjust it (if you have 8000 alerts this month there are likely some adjustments you want to make to your settings haha.)

                  daydiff(ah.timestamp,GETUTCDATE())<30

                   

                   

                  to do 24 hours I would do

                  hourdiff(ah.timestamp,getutcdate())<24

                   

                   

                   

                  To bring in info about severities lets try this

                   

                   

                  --report on alerts triggered

                  select ac.Name

                  ,CASE

                  WHEN ac.Severity = 2 then 'Critical'

                  WHEN ac.Severity = 3 then 'Serious'

                  WHEN ac.Severity = 1 then 'Warning'

                  WHEN ac.Severity = 0 then 'Informational'

                  WHEN ac.Severity = 5 then 'Notice'

                  END AS [Severity]

                  ,CASE

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

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

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

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

                  WHEN ac.Severity = 5 THEN '/Orion/images/ActiveAlerts/Notice.png'

                  END AS [_iconfor_Severity]

                  ,ah.Message

                  ,'/Orion/NetPerfMon/ActiveAlertDetails.aspx?NetObject=AAT:'+ToString(AlertObjectID) as [_linkfor_Name]

                  ,EntityCaption as [Trigger Object]

                  ,EntityDetailsUrl as [_linkfor_Trigger Object]

                  ,case

                  WHEN RelatedNodeCaption=EntityCaption THEN 'Self'

                  When RelatedNodeCaption!=EntityCaption THEN RelatedNodeCaption

                  End as [Parent Node]

                  ,RelatedNodeDetailsUrl as [_linkfor_Parent Node]

                  ,'/Orion/images/StatusIcons/Small-' + p.StatusIcon AS [_IconFor_Parent Node]

                  ,tostring(tolocal(ah.TimeStamp)) as [Trigger Time]

                  ,case when ack.timestamp is null then 'N/A'

                  else tostring(minutediff(ah.TimeStamp,ack.timestamp))

                  end as [Minutes Until Acknowledged]

                  ,ack.Message as [Note]

                  ,case when reset.timestamp is null then 'N/A'

                  else tostring(minutediff(ah.TimeStamp,reset.timestamp))

                  end as [Minutes Until Reset]

                   

                   

                  FROM Orion.AlertHistory ah

                  left join Orion.AlertObjects ao on ao.alertobjectid=ah.alertobjectid

                  left join Orion.AlertConfigurations ac on ac.alertid=ao.alertid

                  left join Orion.Actions a on a.actionid=ah.actionid

                  left join Orion.Nodes p on p.nodeid=RelatedNodeID

                  left join (select timestamp, AlertActiveID, AlertObjectID,message from orion.alerthistory ah where eventtype=2) ack on ack.alertactiveid=ah.AlertActiveID and ack.alertobjectid=ah.AlertObjectID

                  left join (select timestamp, AlertActiveID, AlertObjectID from orion.alerthistory ah where eventtype=1) reset on reset.alertactiveid=ah.AlertActiveID and reset.alertobjectid=ah.AlertObjectID

                   

                   

                  WHERE

                  hourdiff(ah.timestamp,GETUTCDATE())<24

                  and ah.eventtype=0

                  and ac.Severity in (1,2,3)

                  --and (ac.Name like '%${SEARCH_STRING}%' or EntityCaption like '%${SEARCH_STRING}%' or RelatedNodeCaption like '%${SEARCH_STRING}%')

                   

                   

                   

                   

                  2 of 2 people found this helpful