This discussion has been locked. The information referenced herein may be inaccurate due to age, software updates, or external references.

You can no longer post new replies to this discussion. If you have a similar question you can start a new discussion in this forum.

Reporting on Alert actions?

ahbrook over 3 years ago

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?

Top Replies

mesverrum over 3 years ago +1

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…

0 mesverrum over 3 years ago

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