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.

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

  • 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.

  • 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.

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

    pastedImage_0.png

    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

    pastedImage_2.png

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

    pastedImage_3.png

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

    pastedImage_8.png

    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

    pastedImage_6.png

    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

    pastedImage_7.png

    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

  • 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.