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
Store

How to a query for past alerts that have since cleared?

I had an incident with a Volume on a NetApp filling up very quickly. When the issue was identified, the Volume was increased to resolve the issue. I wanted to look in SRM for space alerts on the Volume in question to see if SRM caught it and when. Since the Volume is currently not in an alert state, any previous alerts would have cleared.

I see no obvious way to show cleared alerts anywhere in the Alerts or Message Center screens. I see a table in the database called AlertHistory and a View called AlertHistoryView which seems to have some level of that history. I'm seeing both Triggered and Reset event types. Can I access this data from within Orion through a view or Report?

Thanks

Find more posts tagged with

Accepted answers

All comments

xtraspecialj

If you aren't afraid of a little SWQL, this should give you all the info you need: Alerts · solarwinds/OrionSDK Wiki · GitHub

You'll basically want to query the AlertHistory table for AlertObjectID's that have both a 1 and a 2 EventType. You can join (or navigate through SWQL) to the AlertObject and AlertConfiguration tables to get extra data in your results as needed. You can also use SQL as a lot of the fields will be the same in the SQL AlertHistory table, but just remember that using SQL queries opens you up to having broken reports, alerts, or views in the future should SolarWinds change the database schema, and they can do this on any upgrade, so you should always use SWQL when possible. I only use SQL when I can't get what I need out of SWQL.

alang

I cannot believe that after nearly three years this has not been answered. Apologies to @xtraspecialj, not all of us are programmers or familiar with sql or swql, or even want to go into that direction. If I wanted to go into that direction I would have gone with Nagios. It seems that a simple checkbox on the "Alerts" page to "Show inactive Alerts" or similar should be easy enough to implement. You can't learn from or investigate the past if you can't see it.

mesverrum

In the GUI you can always just go to the Message Center and see alert resets or triggers like so,

But I find that I prefer to roll my own custom query stuff for them because I like things the way I like them and I'm sure there are probably lots of ways to accomplish this request but these are the ones I use. I've posted these resources elsewhere on Thwack in the past but I find them to be invaluable for knowing what's going on with alerting. In a perfect world everything would be available exactly how I want it out of the box, but one of the major strengths of Solarwinds is that it is an open enough platform that you can almost always find a way to make it do what you want, and odds are pretty good that someone on thwack has already solved whatever questions you are running into.

Counts of past alerts

--report on alerts triggered

select ac.Name

--,ah.Message

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

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

,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]

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

,'/Orion/images/StatusIcons/Small-' + p.StatusIcon AS [_IconFor_Parent Node]

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

WHERE

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

and eventtype=0

group by name, [Trigger Object], RelatedNodeCaption

order by [Alert Count] desc

Counts of emails sent by alerts

--report on alerts Email actions triggered over last 30 days

select ac.Name

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

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

--,ah.Message

,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]

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

,'/Orion/images/StatusIcons/Small-' + n.StatusIcon AS [_IconFor_Trigger Object]

,'/Orion/images/StatusIcons/Small-' + p.StatusIcon AS [_IconFor_Parent Node]

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 Orion.Nodes n on n.caption=EntityCaption

left join Orion.Nodes p on p.caption=RelatedNodeCaption

WHERE

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

and a.actiontypeid='Email'

group by name, [Trigger Object], RelatedNodeCaption

order by [Email Count] desc

Alert Trigger/Ack/Reset Timespans

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

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

and ah.eventtype=0

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

order by ah.timestamp desc

Alert History for Node Details View

select AlertHistory.AlertObjects.AlertConfigurations.Name as [Alert Name],

Message,

AlertHistory.AlertObjects.EntityCaption as [Triggering Object],

ToLocal(Timestamp) as [Time],

AlertHistory.AlertObjects.RelatedNodeCaption as [Related Node],

'https://insert server here/Orion/NetPerfMon/ActiveAlertDetails.aspx?NetObject=AAT:'+ToString(AlertObjectID) as [_linkfor_Message],

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

from Orion.AlertHistory

where AlertHistory.AlertObjects.RelatedNodeID='${NodeID}'

and EventType = 0

order by TimeStamp desc

Alert History for SAM Applications

select AlertHistory.AlertObjects.AlertConfigurations.Name as [Alert Name],

Message,

AlertHistory.AlertObjects.EntityCaption as [Triggering Object],AlertHistory.AlertObjects.EntityDetailsUrl as [_linkfor_Triggering Object],

ToLocal(Timestamp) as [Time],

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

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

from Orion.AlertHistory

where EventType = 0

and AlertHistory.AlertObjects.Entityuri like '%/ApplicationID='+tostring(${applicationid})+'/%'

order by TimeStamp desc

alang

Thank you, I really appreciate your detailed response. Again, I am not looking to get into SQL or SWQL and also don't think that I should have to if I spend a lot of money on a supposedly easy to use product. The Message Center btw does not show any of the triggered alerts, even if I select "Show acknowledged". I can list my test server and not apply any filters and it will only show the events that led to the alert, not the alert itself. I don't understand why this is even an issue, there is also an option to show or hide acknowledged alerts, why not also inactive alerts? Feels like the ancient WordPerfect days where you had to script to get simple formatting features.