6 Replies Latest reply on Mar 19, 2018 2:27 PM by mesverrum

    Query for alerts of a specific node

    lafrank

      I would like to create an SQL query - targeting NPM 12.2 database - for retrieving the alert history of a specific node given by name (or NodeID or IP_Address, etc...)

      I found below query on this thread what I could use a starting point, except it does not work with current database schema. How can I update this to work with current version ?

       

      Select Nodes.Caption, Nodes.IP_Address, AlertLog.LogDateTime From Nodes

      Join AlertLog on Nodes.NodeID = AlertLog.ObjectID

      Where AlertLog.ObjectType = 'Node' and ..........

       

       

      Thank you.

        • Re: Query for alerts of a specific node
          neomatrix1217

          I'm not much of a SQL guy but you could try something like this.

           

          Select Nodes.Caption, Nodes.IP_Address, AlertHistoryView.Message From Nodes

          Join AlertHistoryView on Nodes.NodeID = AlertHistoryView.RelatedNodeID

          Where Nodes.Status = 2 and AlertHistoryView.ObjectType = 'Node'

          • Re: Query for alerts of a specific node
            mesverrum

            I literally just happened to be copy pasting this query in my client's environment today, it is written to be used on the Node Details page, but if you remove the line with ${nodeid} then it would work as a summary overall.

             

             

             

             

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

            case when EventType=0 then concat('Triggered - ',ah.Message)

            when EventType=1 then concat('Alert Reset',ah.message)

            when EventType=2 then concat('Acknowledged - ',ah.Message)

            when EventType=3 then concat('Notes - ',ah.Message)

            end as [Message],

            ah.AlertObjects.EntityCaption as [Triggering Object],

            ToLocal(ah.Timestamp) as [Time],

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

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

             

            from Orion.AlertHistory ah

            where

            eventtype=0

            --EventType in (0,1,2,3)

            and ah.AlertObjects.RelatedNodeID='${NodeID}'

             

            --and (Message like '%${SEARCH_STRING}%'

            --or Timestamp like '%${SEARCH_STRING}%')

             

            order by TimeStamp desc

             

            -Marc Netterfield

                Loop1 Systems: SolarWinds Training and Professional Services

              • Re: Query for alerts of a specific node
                lafrank

                Thank you, it seems really promising, only I am having issues executing this.

                To narrow down the problem, I simplified above to :

                 

                select ah.AlertObjects.AlertConfigurations.Name,ah.Message,ah.AlertObjects.EntityCaption,ah.Timestamp,

                from Orion.AlertHistory ah

                 

                But still receiving message : RunQuery failed, check fault information

                Unfortunately I don't know where to find the referenced "fault information" so uncertain what is causeing the error.

                 

                Any ideas please ?