4 Replies Latest reply on Aug 20, 2018 10:26 AM by mesverrum

    SQL Help, Alert History filtered by custom property

    xaviermace

      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
          mesverrum

          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
          ,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}%')
          --and p.customproperties.customername like 'whatever'
          
          order by ah.timestamp desc

           

           

          2 of 2 people found this helpful