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

Reporting on Alert actions?

I'm just getting started with writing my own custom reports, and I'm looking to see if the vast knowledge of this place could help me out. 🙂

 

We're currently trying to get a sense for how often our alerts escalate. Right now, we have a series of 24x7 alerts that start by sending an email out to admins, and then will send an email to a site that generates a phone call after about 10-15 minutes.  Technically we have up to 6 escalation levels, as the system calls first on-call, then team leads, then managers and up the chain.

I'm trying to see if I can get a report of every time that escalation level has been fired, and on what machines, and who the communications were sent out to. Basically I'm looking for a log.

I'm trying to get started using a custom table, scoped to alert history where Triggered Instances are greater than 0. I've gotten a bit of luck out of this, but it really isn't shaping up to give me exactly what I want.

My columns are currently:

  • Message
  • TriggeredCount
  • Related Node Caption
  • Last Triggered Date Time
  • Last Executed Escalation Level
  • Alerting Email (Node)
  • Alerting Email (Application)

I am grouping by Message and Triggered Count.

Because of the width of the report, I've left off some columns I'd really like to track, such as who acknowledged the alert and when. Again, the idea is to make an inventory of our triggered alert actions to see when they fired.

 

Anyone have experience with this?

Labels (2)
1 Reply

This might be useful as a reference, i don't really use escalations in my case but its a model for how to get around the alert history and related tables

 

--report on alerts triggered  
 
select ac.Name 
,ah.Message 
,'/Orion/NetPerfMon/ActiveAlertDetails.aspx?NetObject=AAT:'+ToString(AlertObjectID) as [_linkfor_Name] 
,case When RelatedNodeCaption!= EntityCaption THEN concat(isnull(RelatedNodeCaption,''),' - ',isnull(EntityCaption,''))
else EntityCaption
End AS [ALERT OBJECT] 
,EntityDetailsURL AS [_LinkFor_ALERT OBJECT]
--end as [Minutes Until Acknowledged] 
--,ack.Message as [Note] 
,tostring(tolocal(ah.TimeStamp)) as [Trigger Time] 
--,case when ack.timestamp is null then 'N/A' 
--else tostring(minutediff(ah.TimeStamp,ack.timestamp)) 
,tostring(isnull(tolocal(reset.TimeStamp),'Still Active')) as [Clear Time] 
,case when reset.timestamp is null then minutediff(ah.TimeStamp,GETUTCDATE())
else minutediff(ah.TimeStamp,reset.timestamp)
end as [Minutes Until Clear] 

 
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 (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 in (1,8)) reset on reset.alertactiveid=ah.AlertActiveID and reset.alertobjectid=ah.AlertObjectID 
 
WHERE  
ah.eventtype=0 
--and (ac.Name like '%${SEARCH_STRING}%' or EntityCaption like '%${SEARCH_STRING}%' or RelatedNodeCaption like '%${SEARCH_STRING}%') 
 
order by ah.timestamp desc

 

This one also might be relevant

 

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() 
 
group by name,  [Trigger Object], [Parent Node] 
order by [Alert Count] desc 
- Marc Netterfield, Github