7 Replies Latest reply on Jul 21, 2016 6:53 PM by vikkyg86

    Warning about custom SQL alerts (reset trigger)

    RichardLetts

      This is something to be aware of, but I fall into this trap every time: if you make use of custom SQL alerts then unless it is a very simple trigger you MUST re-write the reset trigger.

       

      Negating SQL is a tricky tasks, and for anything with a JOIN or IN.. clause the alert manager will invariably not get the reset query correct.

      Here are a couple of examples:

       

      Trigger ConditionAuto-generated reset conditionworking reset condition

      INNER JOIN (SELECT nodeid,Count(*) CS

         FROM   syslog

         WHERE  acknowledged = 0

         AND messagetype = 'SFP-RX-HIGH'

         AND datetime > Dateadd(hour, -1, Getdate())

         GROUP  BY nodeid

         HAVING Count(*) > 5) SM

      ON SM.nodeid = nodes.nodeid

      INNER JOIN (

      SELECT nodeid, count(*) CS

      FROM   syslog WHERE NOT

      acknowledged = 0

             AND messagetype ='SFP-RX-HIGH'

             AND datetime > Dateadd(hour, -1, Getdate())

      group by NODEID

      HAVING count(*)>5

      ) SM

      ON SM.nodeid=nodes.nodeid

      WHERE  nodeid NOT

         IN (SELECT DISTINCT nodeid

             FROM   syslog

             WHERE  acknowledged = 0

             AND messagetype = 'SFP-RX-HIGH'

             AND datetime > Dateadd(hour, -1, Getdate()))

      WHERE  nodeid

         IN (SELECT DISTINCT nodeid

             FROM   syslog

             WHERE  acknowledged = 0

             AND messagetype = 'SFP-RX-HIGH'

            AND datetime > Dateadd(hour, -1, Getdate()))

      WHERE NOT

      nodeid NOT 

      IN (SELECT DISTINCT nodeid

             FROM   syslog WHERE NOT

      acknowledged = 0

             AND messagetype = 'SFP-RX-HIGH'

             AND datetime > Dateadd(hour, -1, Getdate()))

      WHERE  nodeid NOT

         IN (SELECT DISTINCT nodeid

             FROM   syslog

             WHERE  acknowledged = 0

             AND messagetype = 'SFP-RX-HIGH'

             AND datetime > Dateadd(hour, -1, Getdate()))

      I've not (ha!) reported this as a bug because I'm not entirely sure that generating the negative condition automatically is A Good Idea

       

      Anyway... something to be aware of when using custom SQL alerts and wondering why they never trigger.

        • Re: Warning about custom SQL alerts (reset trigger)
          michal.hrncirik

          Hi Richard, let me talk to DEV about this. thanks for very nice presentation of the problem.

          • Re: Warning about custom SQL alerts (reset trigger)
            ET

            Hi,

            this is unfortunately known issue. In case you use subqueries in your custom SQL trigger condition. Then automatically generated reset SQL query isn't always valid and accurate (it works fine for simple, solo queries). Solution here is to use also Custom SQL command for reset condition and don't rely on automatically generated.

            • Re: Warning about custom SQL alerts (reset trigger)
              alexslv

              Thank you. This logic has helped me to work out reset SQL statement for my complex advanced SQL alert with few joints.

              • Re: Warning about custom SQL alerts (reset trigger)
                alexslv

                Hi Richard, I have noticed that in RESET you didn't include GROUP BY nor HAVING statements. I am not SQL Guru and I have a query with similar structure. What I have found that it would work ok even if I leave these two in RESET condition. Are there any negative implications of leaving them there?

                  • Re: Warning about custom SQL alerts (reset trigger)
                    RichardLetts

                    it's okay if you leave the Group By.. in the reset condition; it will work but the meaning is slightly different, which may or may not be your intent, and you might be causing the database to do additional work.

                     

                    In my example the alert manager builds a set of nodes that have had more than 5 SFP-RX-HIGH alerts in the past hour; then it removes alerts from nodes that have not had ANY in the past hour. for the second query it only has to build a set of nodeids that have had SFP-RX-HIGH whereas if you leave the group by it has to maintain a running total of the count and then throw out any with a higher count.

                    For many sites with small installs (<1000 nodes) this probably doesn't make a difference, but when your syslog table is 13 million rows [1] and you have 10,000 nodes it does.

                     

                    Note, if your intent is for the alert to reset sooner, e.g. when there are less than three SFP-RX-HIGH per hour then you could write the reset condition like this:

                    INNER JOIN (SELECT nodeid,Count(*) CS

                       FROM   syslog

                       WHERE  acknowledged = 0

                       AND messagetype = 'SFP-RX-HIGH'

                       AND datetime > Dateadd(hour, -1, Getdate())

                       GROUP  BY nodeid

                       HAVING Count(*) < 3 ) SM

                    ON SM.nodeid = nodes.nodeid


                     

                    [1]caused by a runaway node logging millions of messages an hour for example...

                    1 of 1 people found this helpful
                  • Re: Warning about custom SQL alerts (reset trigger)
                    alexslv

                    I have found that the below template works for me in almost any case (obviously credits to RichardLetts for sharing the above):

                     

                    TRIGGER:

                    SELECT Table.ID as NetObjectID, Table.Name as Name

                    FROM Table

                    ---------------------------------------------------

                      WHERE

                      <YOUR SQL QUERY>

                     

                    RESET:

                    SELECT Table.ID as NetObjectID, Table.Name as Name

                    FROM Table

                    ---------------------------------------------------

                    WHERE

                    Table.ID NOT IN

                    (

                       SELECT DISTINCT Table.ID

                       FROM Table

                       WHERE

                       <YOUR SQL QUERY>

                    )

                    1 of 1 people found this helpful
                    • Re: Warning about custom SQL alerts (reset trigger)
                      vikkyg86

                      Hello Richard, may I please know where I can find the autogenerated reset conditions?