3 Replies Latest reply on Sep 18, 2013 4:06 AM by simoneidem

    Trap to Event List using Custom SQL Alert

    simoneidem

      Hi

       

      Trap details.PNG

      The image above shows the trap i need to get into the Event list on Orion NPM.

      "spSensorValue = 1" should be the Trigger Condition, and "spSensorValue = 0" is the Reset Condition.

      I have tried to make a custom sql alert, but can't get it right.

       

      Can someone help me on this one?

       

      Thanks,

      Simon

       

      UPDATE:

       

      Case is closed, this is the solution:

       

      Trigger Condition:

      INNER JOIN traps a

                     ON a.nodeid = nodes.nodeid

      INNER JOIN trapvarbinds b

                     ON a.trapid = b.trapid

                        AND b.oidname = 'spSensorValue'

      WHERE  a.traptype = 'HHMSAGENT-MIB:spUnknownStatus.301'

             AND a.datetime >= Dateadd(second, -15, Getdate())

             AND b.oidvalue = '1'

       

      Reset Condition:

      INNER JOIN traps a

                     ON a.nodeid = nodes.nodeid

      INNER JOIN trapvarbinds b

                     ON a.trapid = b.trapid

                        AND b.oidname = 'spSensorValue'

      WHERE  a.traptype = 'HHMSAGENT-MIB:spUnknownStatus.301'

             AND a.datetime >= Dateadd(second, -15, Getdate())

             AND b.oidvalue = '0'

       

      Big thanks to RichardLetts.

        • Re: Trap to Event List using Custom SQL Alert
          RichardLetts

          when developing these I start with a database query that returns the set of nodes that match the criteria:

          SELECT nodeid,

                 traptype,

                 oidvalue

          FROM   traps a

                 INNER JOIN trapvarbinds b

                         ON a.trapid = b.trapid

                            AND b.oidname = 'spSensorValue'

          WHERE  a.acknowledged = 0

                 AND traptype = 'HHMSAGENT-MIB:spUnknownStatus.301'

          ORDER BY a.datetime

           

          This is much faster at developing and testing that trying to use the alert manager to do this. Then convert this into the bit I paste into the 'alert' ; I like to put a time-bound on traps and allow then to be 'acknowledged'

           

                 INNER JOIN traps a

                         ON a.nodeid = nodes.nodeid

                 INNER JOIN trapvarbinds b

                         ON a.trapid = b.trapid

                            AND b.oidname = 'spSensorValue'

          WHERE  a.acknowledged = 0

                 AND a.traptype = 'HHMSAGENT-MIB:spUnknownStatus.301'

                 AND a.datetime >= Dateadd(hour, -4, Getdate())

                 AND b.oidvalue = '1'

           

           

                 INNER JOIN traps a

                         ON a.nodeid = nodes.nodeid

                 INNER JOIN trapvarbinds b

                         ON a.trapid = b.trapid

                            AND b.oidname = 'spSensorValue'

          WHERE  a.traptype = 'HHMSAGENT-MIB:spUnknownStatus.301'

           

                 AND a.datetime >= Dateadd(hour, -4, Getdate())

                 AND b.oidvalue = '0'


          Hopefully that is enough to get you started in the right direction

           

          [SOAPBOX]

               I have prefer putting the restrictions into the join criteria because they get evaluated how most people expect them to do when you have an outer join.

               I like putting the table restrictions in the join criteria when it makes sense, because if you ever need an outer join they work more like you expect them to

               Note: the Orion trap receiver incorrectly decoded the trap type. if that ever gets fixed you may have to update your clause.

          [/SOAPBOX]

            • Re: Trap to Event List using Custom SQL Alert
              RichardLetts

              You are going to need some more complex SQL, because you have to check that the last trap received trap for the oidvalue:

               

                     INNER JOIN traps a

                             ON a.nodeid = nodes.nodeid

                                AND a.traptype = 'HHMSAGENT-MIB:spUnknownStatus.301'

                     INNER JOIN trapvarbinds b

                             ON a.trapid = b.trapid

                                AND b.oidname = 'spSensorValue'

              WHERE  a.acknowledged = 0

                     AND a.datetime >= Dateadd(hour, -4, Getdate())

                     AND a.datetime = (SELECT Max(datetime)

                                       FROM   traps t

                                       WHERE  t.nodeid = a.nodeid

                                              AND T.traptype =

                                                  'HHMSAGENT-MIB:spUnknownStatus.301')

                     AND b.oidvalue = '1'



              1 of 1 people found this helpful
              • Re: Trap to Event List using Custom SQL Alert
                simoneidem

                Thanks alot, Richard!

                I did some customization of your examples, and now it works perfect for our use.

                 

                Trigger Condition:

                INNER JOIN traps a

                               ON a.nodeid = nodes.nodeid

                INNER JOIN trapvarbinds b

                               ON a.trapid = b.trapid

                                  AND b.oidname = 'spSensorValue'

                WHERE  a.traptype = 'HHMSAGENT-MIB:spUnknownStatus.301'

                       AND a.datetime >= Dateadd(second, -15, Getdate())

                       AND b.oidvalue = '1'

                 

                Reset Condition:

                INNER JOIN traps a

                               ON a.nodeid = nodes.nodeid

                INNER JOIN trapvarbinds b

                               ON a.trapid = b.trapid

                                  AND b.oidname = 'spSensorValue'

                WHERE  a.traptype = 'HHMSAGENT-MIB:spUnknownStatus.301'

                       AND a.datetime >= Dateadd(second, -15, Getdate())

                       AND b.oidvalue = '0'