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

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

•                 LinkedIN: Loop1 Systems
•                 Facebook: Loop1 Systems
•                 Twitter: @Loop1Systems

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

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

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

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

Thanks again for the lesson. :-)

MC

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

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,

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

KMSigma​

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

Thanks,

MC