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

Tracking unassigned events

Im trying to create a daily report that would capture any critical alerts that have come in but haven't been acknowledged within 15 minutes. I've worked through some of the custom report rules but cant quite figure it out. Could anyone point me in the right direction.

Tags (1)
0 Kudos
1 Reply
Highlighted

Re: Tracking unassigned events

This is one of my SWQL reports reworked for your conditions, show all alerts that took longer than 15 min to ack or reset.

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 

ah.eventtype=0

and ac.Severity=2 --critical

and (minutediff(ah.TimeStamp,ack.timestamp) > 15

or minutediff(ah.TimeStamp,reset.timestamp) > 15

or (ah.TimeStamp < ADDMINUTE(-15,GETUTCDATE()) and reset.timestamp is null and ack.timestamp is null)

)

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

order by ah.timestamp desc

- Marc Netterfield, Github
0 Kudos