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

Report for how many times a specific alert was triggered the last 7 days.

I would like to create a report for how many times a specific alert was triggered. Right now, I have the alert writing to the NPM event log. I figured I'd have it write to the event log and then pick an out of the box resource in the report writer in the web console for how many times an alert was triggered, but i don't see anything like that.

How would i go about accomplishing this?

Thanks!

0 Kudos
5 Replies

I've got a swql query I use the the custom query resource for cases like this,

select distinct ac.Name as [Alert Name]

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

--,ah.Message

,count(ah.message) as [Alert Count 30 days]

,today.[Alert count] as [Alert Count 24 hours]

,EntityCaption as [Trigger Object]

,EntityDetailsUrl as [_linkfor_Trigger Object]

,RelatedNodeCaption as [Parent Node]

,RelatedNodeDetailsUrl as [_linkfor_Parent Node]

,tolocal(max(ah.TimeStamp)) as [Most Recent Trigger]

FROM Orion.AlertHistory ah

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

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

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

left JOIN (

select distinct ac.Name as AlertName

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

--,ah.Message

,count(ah.message) as [Alert Count]

,EntityCaption as [Trigger Object]

,EntityDetailsUrl as [_linkfor_Trigger Object]

,RelatedNodeCaption as [Parent Node]

,RelatedNodeDetailsUrl as [_linkfor_Parent Node]

,tolocal(max(ah.TimeStamp)) as [Most Recent Trigger]

FROM Orion.AlertHistory ah

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

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

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

WHERE 

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

and ah.timestamp < getutcdate()

group by name,  [Trigger Object], [Parent Node]

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

WHERE 

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

and ah.timestamp < getutcdate()

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

group by name,  [Trigger Object], [Parent Node]

order by [Alert Count] desc

- Marc Netterfield, Github

this is awesome! thank you mesverrum​!

what if i wanted to report on just one specific alert? or even just limit this query to the top 100?

i'm not dangerous enough with SWQL yet to accomplish this one.

0 Kudos

So this one actually has 2 queries, one for the 7 day count and one for the 24 hour count, just filter both parts like so

select distinct ac.Name as [Alert Name]

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

--,ah.Message

,count(ah.message) as [Alert Count 30 days]

,today.[Alert count] as [Alert Count 24 hours]

,EntityCaption as [Trigger Object]

,EntityDetailsUrl as [_linkfor_Trigger Object]

,RelatedNodeCaption as [Parent Node]

,RelatedNodeDetailsUrl as [_linkfor_Parent Node]

,tolocal(max(ah.TimeStamp)) as [Most Recent Trigger]

FROM Orion.AlertHistory ah

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

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

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

left JOIN (

select distinct ac.Name as AlertName

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

--,ah.Message

,count(ah.message) as [Alert Count]

,EntityCaption as [Trigger Object]

,EntityDetailsUrl as [_linkfor_Trigger Object]

,RelatedNodeCaption as [Parent Node]

,RelatedNodeDetailsUrl as [_linkfor_Parent Node]

,tolocal(max(ah.TimeStamp)) as [Most Recent Trigger]

FROM Orion.AlertHistory ah

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

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

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

WHERE

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

and ah.timestamp < getutcdate()

and ac.name like '%your alert name here%' --<******************************

group by name,  [Trigger Object], [Parent Node]

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

WHERE

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

and ah.timestamp < getutcdate()

and ac.name like '%your alert name here%' --<*********************************

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

group by name,  [Trigger Object], [Parent Node]

order by [Alert Count 30 days] desc

- Marc Netterfield, Github

@mesverrum We have a large use case for this, but need to filter the results to only include business hours (we're looking for servers requiring regular reboots during production).

How would you filter this by hour, say 8AM to 5PM? And if possible how to filter by day as well, say Monday through Friday? I've taken a number of cracks at this, but I know when to stop and ask for help. Thanks!

0 Kudos

much appreciated mesverrum​! works like a charm

0 Kudos