1 Reply Latest reply on Jul 11, 2013 11:19 AM by RichardLetts

    Custom SQL Alert not resetting

    ryan.rockwell@ontargetjobs.com

      I've created a custom alert to send e-mails and NetPerfMon entry when my F5 Pool is in down status.  The trigger condition SQL is:

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

      From Nodes

      Where Nodes.NodeID in (Select F5_Pools.NodeID From F5_Pools Where F5_PoolsAvailabilityStatus = 3 AND F5_PoolsEnabledState = 1 AND F5_PoolsName Like '/Common/Prod%')

       

      The alert works fine it's the reset that doesn't work. I've tried reset conditions: Reset when trigger conditions are no longer true and

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

      From Nodes

      Where NOT Nodes.NodeID in (Select F5_Pools.NodeID From F5_Pools Where NOT F5_PoolsAvailabilityStatus = 3 AND F5_PoolsEnabledState = 1 AND F5_PoolsName Like '/Common/Prod%').

       

      I'm wondering if:

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

      From Nodes

      Where Nodes.NodeID in (Select F5_Pools.NodeID From F5_Pools Where F5_PoolsAvailabilityStatus = 1 AND F5_PoolsEnabledState = 1 AND F5_PoolsName Like '/Common/Prod%')

       

      would be a better option?

        • Re: Custom SQL Alert not resetting
          RichardLetts

          you have to be careful with SQL if you have a NULL set

           

          if (Select F5_Pools.NodeID From F5_Pools Where NOT F5_PoolsAvailabilityStatus = 3 AND F5_PoolsEnabledState = 1 AND F5_PoolsName Like '/Common/Prod%') is NULL

          then (I think)  IN .... AND NOT IN ... is also NULL

           

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

          From Nodes

          Inner Join F5_Pools

          on Nodes.NodeID  =  F5_Pools.NodeID

          Where F5_PoolsAvailabilityStatus = 3 AND F5_PoolsEnabledState = 1 AND F5_PoolsName Like '/Common/Prod%'

           

          will probably give you a better result (and the negative reset condition will be better)