4 Replies Latest reply on Apr 13, 2018 2:35 PM by alang

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

    rupdegraff

      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

        • Re: How to a query for past alerts that have since cleared?
          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.

          • Re: How to a query for past alerts that have since cleared?
            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.

              • Re: How to a query for past alerts that have since cleared?
                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

                  • Re: How to a query for past alerts that have since cleared?
                    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.