1 Reply Latest reply on Jun 24, 2015 5:36 PM by dhanson

    If-Then-Else Alerting

    jasadelll
    jasadelll Jun 24, 2015 3:03 PM

      I'm setting up alerts in NPM. Here are the scenarios:


      If the LOCATION=ABC, and exactly 1 node is DOWN, then ALERTX

      If the LOCATION=ABC, and 2-4 nodes are DOWN, then ALERTY (burt don't ALERTX)

      If the LOCATION=ABC, and 5 or more nodes are DOWN, then ALERTZ (but don't ALERTX or ALERTY)

       

      My goal is this: This company has several locations. All of their SNMP configurations have the LOCATION field populated with the location where the node resides. If at a given location, more than 5 nodes are down, then I only want alertZ. If at a given location, between 2 and 4 nodes are down, then I only want alertY. If at a given location only 1 node is down, then I only want alertX.

       

      How can I accomplish this?

        • Re: If-Then-Else Alerting
          dhanson
          dhanson Jun 24, 2015 5:36 PM (in response to jasadelll)

          You can create a SQL alert like below. This one, as configured, would trigger if any one node had a status other than 1 (1 is "up").


          By the way, the AND statement, that entire row is optional. If you only want to alert on specific locations, and not apply these rules to others, you'd have to define this AND statement. Otherwise, you can remove that whole line, and it'll still function. To further clarify, If site "NOP" does not get the same treatment as sites "ABC", "XYZ" or "NMO", then the AND statement is required. If "NOP" (and all other locations) are treated identically, then remove the entire line that begins with AND.

           

          SELECT n.Location, count(n.Status) AS Quantity FROM Nodes AS n

          WHERE n.Status NOT LIKE '1'

          AND n.Location = 'ABC' OR n.Location = 'XYZ' OR n.Location = 'NMO'

          GROUP BY n.Location

          HAVING count(n.Location) = 1

           

          This would satisfy your 2-4 rule.

           

          SELECT n.Location, count(n.Status) AS Quantity FROM Nodes AS n

          WHERE n.Status NOT LIKE '1'

          AND n.Location = 'ABC' OR n.Location = 'XYZ' OR n.Location = 'NMO'

          GROUP BY n.Location

          HAVING count(n.Location) > 1 AND count(n.Location) < 4

           

          And here's your 5 or more.

           

          SELECT n.Location, count(n.Status) AS Quantity FROM Nodes AS n

          WHERE n.Status NOT LIKE '1'

          AND n.Location = 'ABC' OR n.Location = 'XYZ' OR n.Location = 'NMO'

          GROUP BY n.Location

          HAVING count(n.Location) > 4

           

          You might still have situations where all 3 alerts will trigger, should a cascading failure occur at a site. If, within the same polling cycle, multiple devices are declared down, this would work for you as you intend.

           

          I'm sure the SQL Guru's out there can tell you a million ways to optimize this (or will encourage you to simply make this into a single alert, because why the hell would you need this?), but this should get you started.

           

          -HTH!