4 Replies Latest reply on Aug 15, 2012 5:15 AM by PeterLyttle

    Advanced Alert: Custom SQL Query

    PeterLyttle

      Hello,

       

      After getting nowhere with support who are refusing to assist with this I was hoping some other users of NPM may be able to assist since "SQL Querys are outside of support scope", even though I dont agree the SQL is at fault.

       

      Basically I am trying to alert for certain IP Addresses where 4 of my 5 custom properties are blank.  The Custom Properties being - DEVUATLIVE, KeySystem, Region and Segment)

       

      SELECT Nodes.NodeID AS NetObjectID, Nodes.Caption AS Name

      FROM Nodes

      WHERE (IP_Address LIKE '%10.120.4.%' OR IP_Address LIKE '%10.218.0.%' OR IP_Address LIKE '%10.218.1.%' OR IP_Address LIKE '%10.218.2.%' OR IP_Address LIKE '%10.218.3.%' OR IP_Address LIKE '%10.218.4.%' OR IP_Address LIKE '%10.218.5.%' OR IP_Address LIKE '%10.218.6.%' OR IP_Address LIKE '%10.218.7.%' OR IP_Address LIKE '%10.218.8.%' OR IP_Address LIKE '%10.218.9.%' OR IP_Address LIKE '%10.218.10.%' OR IP_Address LIKE '%10.218.11.%' OR IP_Address LIKE '%10.218.12.%' OR IP_Address LIKE '%10.218.13.%')

      AND (DEVUATLIVE IS NULL OR KeySystem IS NULL OR Region IS NULL OR Segment IS NULL)

       

      Checks every 12hrs and alerts only if condition exists for more than 1hr.

       

      The problem is that when the alert does detect nodes it triggers and I get notified about the missing values.  I then set the values and wait for the reset to occur, this doesnt happen; I have to go in manually and clear the alert from Advanced Alert Manager.

       

      I have executed the SQL via MS SQL Management Studio and Solarwinds DB Manager (once the values have been added after the alert was triggered) and it returns 0 rows

       

       

       

      If anyone can test a similar query or point out what is wrong please can you do so.

       

      Thanks,

      Peter

        • Re: Advanced Alert: Custom SQL Query
          pacetti

          PeterLyttle,

           

          Try reformulating your conditions


          DEVUATLIVE IS NULL OR KeySystem IS NULL OR Region IS NULL OR Segment IS NULL


          into an alert suppression. Conditions on the Alert Suppression tab of the Advanced Alert Manager function the same way they do on the Trigger condition tab.


          HTH,

          Andrew

          1 of 1 people found this helpful
            • Re: Advanced Alert: Custom SQL Query
              PeterLyttle

              Hi Andrew,

              Thanks for the reply.  If I am understanding correctly, on the "Reset Condition" tab I have tried both options -

               

              Reset when trigger conditions are no longer true

              Reset this alert when the following conditions are met:

              SELECT Nodes.NodeID as NetObjectID, Nodes.Caption as Name

              FROM Nodes

              WHERE NOT

              (IP_Address LIKE '%10.120.4.%' OR IP_Address LIKE '%10.218.0.%' OR IP_Address LIKE '%10.218.1.%' OR IP_Address LIKE '%10.218.2.%' OR IP_Address LIKE '%10.218.3.%' OR IP_Address LIKE '%10.218.4.%' OR IP_Address LIKE '%10.218.5.%' OR IP_Address LIKE '%10.218.6.%' OR IP_Address LIKE '%10.218.7.%' OR IP_Address LIKE '%10.218.8.%' OR IP_Address LIKE '%10.218.9.%' OR IP_Address LIKE '%10.218.10.%' OR IP_Address LIKE '%10.218.11.%' OR IP_Address LIKE '%10.218.12.%' OR IP_Address LIKE '%10.218.13.%')

              AND (DEVUATLIVE IS NOT NULL OR KeySystem IS NOT NULL OR Region IS NOT NULL OR Segment IS NOT NULL)

               

              I have just noticed that when you set a SQL Reset trigger it automatically negates the trigger query (WHERE NOT), I'm going to see if this resolves the problem.  (I assume "Reset when trigger conditions are no longer true" also uses this method behind the scenes)

                • Re: Advanced Alert: Custom SQL Query
                  netlogix

                  That query is a little long for me, what about:

                  Trigger:

                  Where

                  (   Left(IP_Address,9)  in ('10.120.4.','10.218.0.','10.218.1.','10.218.2.','10.218.3.','10.218.4.','10.218.5.','10.218.6.','10.218.7.','10.218.8.','10.218.9.')

                  or Left(IP_Address,10) in ('10.218.10.','10.218.11.','10.218.12.','10.218.13.')

                  ) AND (DEVUATLIVE IS NULL OR KeySystem IS NULL OR Region IS NULL OR Segment IS NULL)

                   

                  Reset:

                  Where

                  Not DEVUATLIVE IS NULL and Not KeySystem IS NULL and NOT Region IS NULL and NOT Segment IS NULL)

                   

                  You don't need to re-evaluate the IP unless you would want the system changing subnet to reset the alert as well.

                  2 of 2 people found this helpful