5 Replies Latest reply on May 8, 2019 10:26 AM by retseladrec

    SQL Help, Alert History filtered by custom property


      I could use some assistance on this.  I'm pretty rusty on doing anything SQL, and have never really done joins, and I don't have much experience manually digging around the SolarWinds database.  I'm looking to create a report that shows the last 30 (or maybe 90?) days of alerts with for a specific client.  We have a custom property for the client name.  I was able to get a query to pull most of what I need from the Alert History View, but I want the report to show the time the alert triggered, when it was acknowledged, and ideally when it reset.  That's where I'm getting stuck.  That info isn't in the AlertHistoryView so I'm assuming I need to another table.  The AlertActive table seems to have the data I need and the AlertActiveID appears to link the two but I'm apparently missing something.


      SELECT ah.EventTypeWord, ah.Message, ah.TimeStamp, ah.RelatedNodeCaption FROM AlertHistoryView ah

      INNER JOIN NodesCustomProperties ncp

      ON ah.RelatedNodeID =ncp.NodeID

      WHERE ncp.CustomerName = 'Client Name' AND ah.EventTypeWord = 'Triggered' AND ah.TimeStamp >= DATEADD(month,-1,GETDATE())

      ORDER BY ah.TimeStamp


      But when I add a second join, I get no results.


      INNER JOIN AlertActive aa

      ON ah.AlertActiveID=aa.AlertActiveID


      If I manually search for a specific AlertActiveID number in both tables, I get the data I would expect, so what am I not understanding about doing multiple joins?  Or am I completely approaching this the wrong way entirely?

        • Re: SQL Help, Alert History filtered by custom property

          Use this in a custom query resource in the web console, sounds like it has everything you are looking for


          --report on alerts triggered 
          select ac.Name
          ,'/Orion/NetPerfMon/ActiveAlertDetails.aspx?NetObject=AAT:'+ToString(AlertObjectID) as [_linkfor_Name]
          ,EntityCaption as [Trigger Object]
          ,EntityDetailsUrl as [_linkfor_Trigger Object]
          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
          and ah.eventtype=0
          --and (ac.Name like '%${SEARCH_STRING}%' or EntityCaption like '%${SEARCH_STRING}%' or RelatedNodeCaption like '%${SEARCH_STRING}%')
          --and p.customproperties.customername like 'whatever'
          order by ah.timestamp desc



          4 of 4 people found this helpful
          • Re: SQL Help, Alert History filtered by custom property



            Thank you for posting the query this is helpful.  I just want to add one more thing but can't find the ac.? or where to look and where to add this to the query. "Object that triggered this alert" 

            Object that triggered this alert

            any help is appreciate it.


            Thanks in Advance,

            Lester C