Community
- Command Central
- MVP Program
- Monthly Mission
- Blogs
- Groups
- Events
- Media Vault
Products
- Observability
- Network Management
- Application Management
- IT Security
- IT Service Management
- System Management
- Database Management
Content Exchange
- SolarWinds Platform
- Server & Application Monitor
- Database Performance Analyzer
- Server Configuration Monitor
- Network Performance Monitor
- Network Configuration Manager
- SQL Sentry
- Web Help Desk
Free Tools & Trials

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!

Find more posts tagged with

Accepted answers

All comments

mesverrum

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

rmullal

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.

mesverrum

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

rmullal

much appreciated mesverrum! works like a charm

gerretwalczak

@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!