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

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

Jump to solution

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

1 Solution

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}%')

- Marc Netterfield, Github

View solution in original post

17 Replies

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.

pastedImage_3.png

--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

- Marc Netterfield, Github

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

0 Kudos

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}%')

- Marc Netterfield, Github

View solution in original post

KMSigma

In need of help converting the query mesverrum​ provided to a report I can export in Excel format.

Thanks,

MC

0 Kudos

You looking for last 24 hours or something else?

"Shoot for the stars to reach the moon"
0 Kudos

Hello,

     The last 24 hours is what I'm looking for; the last reply from mesverrum​ worked wonderfully but we were using the "custom query" resource within the web report which does not allow for exporting into Excel format. I would like to be able to export the results into Excel in order to work with the data but I don't know how to edit the query provided by mesverrum​ to make it work with the standard table resource.

Thanks,

MC

0 Kudos

Take a peek at Acknowledgement Report , which I just uploaded.  Since you only need the last 24 hours, you can delete the other two portions of the report.

"Shoot for the stars to reach the moon"

KMSigma, is there a way to add the username that acknowledged the alert as well?

0 Kudos

Thanks KMSigma

0 Kudos

I should have got on top of this myself to harvest those sweet sweet Thwack points

With winter coming I need to get some socks

- Marc Netterfield, Github

Thanks for your efforts as well mesverrum​ , hope you can score those socks soon!

Thanks for all your help Marc, is there any way I can get this query to work on a custom web report in addition to the view? When I tried it I got an invalid query error.

Thanks again,

MC

0 Kudos

You can just add custom query as a resource to the Web report,  most people think you can only do table or chart but any orion resource can be used on the reports if you look at the box on the left where it asks you if you want a table or chart

- Marc Netterfield, Github

Marc,

Looks like I hit a bump on the road with exporting the report as CSV or Excel format (those formats are only supported for Custom Table resource or Report Writer); is there any way we could make this work so I could have it in Excel or CSV format?

Thanks again for all the help,

MC

0 Kudos

Ah when exporting it to excel it requires you to use the standard table resource. I don't have time right now to remove all the bits that resource doesn't like, but at the very least it will need to remove anything relating to a column with an Iconfor or linkfor name, it also doesn't like some other types of joins and subselects and things that I use often and I can't recall off the top of my head how to fix those.

- Marc Netterfield, Github
0 Kudos

Marc,

Thanks for your support with this report and the information provided; I will provide the report as is to my management but if you ever get bored and would like to work on the Excel export it would be greatly appreciated. 🙂

Thanks,

MC

0 Kudos

Thanks again for the lesson. 🙂

MC

0 Kudos