Version 2

    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.

     

    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]

     

    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'



     

    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'

     

    This document was generated from the following discussion: Trap to Event List using Custom SQL Alert