1 Reply Latest reply on May 25, 2018 10:56 AM by antigrammatik

    Custom Node Poller - Custom SQL Alert not firing

    jvaughan@connectria.com

      Greetings all!

       

      I am trying to set up a Custom SQL Alert for a Custom Node Poller with no luck.  When I modify the query in SSMS to guarantee one returned record, I get nothing in SolarWinds.  I'll attach the real trigger condition and the modified condition that guarantees at least one returned record.

       

      Original query:

      [GIVEN SELECT]

      SELECT CustomPollerAssignmentView.AssignmentName, CustomPollerAssignmentView.CustomPollerAssignmentID FROM CustomPollerAssignmentView

      [CUSTOM]

      LEFT OUTER JOIN (

           SELECT NodeID, COUNT(*) AS 'ProcCount'

           FROM AssetInventory_ProcessorView

           GROUP BY NodeID

           ) AS procs ON CustomPollerAssignmentView.NodeID = procs.NodeID

      WHERE CustomPollerAssignmentView.CustomPollerName LIKE 'LoadAverage1min'

           AND CONVERT(decimal,CustomPollerAssignmentView.CurrentValue) < procs.ProcCount

       

      Modified to guarantee at least one returned record:

      [GIVEN SELECT]

      SELECT CustomPollerAssignmentView.AssignmentName, CustomPollerAssignmentView.CustomPollerAssignmentID FROM CustomPollerAssignmentView

      [CUSTOM]

      LEFT OUTER JOIN (

           SELECT NodeID, COUNT(*) AS 'ProcCount'

           FROM AssetInventory_ProcessorView

           GROUP BY NodeID

           ) AS procs ON CustomPollerAssignmentView.NodeID = procs.NodeID

      WHERE CustomPollerAssignmentView.CustomPollerName LIKE 'LoadAverage1min'

       

      It's strange because if I just use WHERE CustomPollerAssignmentView.CustomPollerName LIKE 'LoadAverage1min', no alerts are triggered.  Is this method of alerting not working as expected, or am I misconfiguring it?

       

      Thanks in advance!

       

      Jack Vaughan, Jr.

      Systems Monitoring Engineer

      ___________________________

      CONNECTRIA HOSTING

      jvaughan@connectria.com

      ___________________________

      NO JERKS ALLOWED®
      www.connectria.com
      Connectria Blog
      Recommend us on Linkedin
      Follow us on Twitter

      nojerksallowed.com

        • Re: Custom Node Poller - Custom SQL Alert not firing
          antigrammatik

          I had the same problem but was able to get it resolved with help from support. I was setting up what looks like the same kind of thing as you as I was creating a load average alert on a custom node poller. I'll throw out a couple assumptions that I think apply to you that applied to me. You have set up a Load Average custom poll in the Universal Device Poller, the SNMP Get Type under Advanced Options in the Universal Device Poller is GET TABLE, and you are using the web interface to create the alert.

           

          Based on what you have listed in your select statement you have Custom Node Poller selected on the Trigger Condition page under Set up your SQL condition. You need to change this to Custom Node Table Poller and update your JOIN and WHERE statements accordingly.

           

          Even though the query works when you run it through SSMS, the application appears to care that it is a GET TABLE type and wants you to use the Custom Node Table Poller condition instead of Custom Node Poller. I took a bit of a different approach on my trigger but I have included my SQL below for reference if it is helpful.

           

          Interestingly, I have access to an older instance of Orion and it did not care that I selected Custom Node Poller. So if you have an alert that used to work but doesn't after upgrade, that may be the culprit.

           

          SQL Statement:

          [Given Select]

          SELECT CustomPollerStatusTable.FullLabel, CustomPollerStatusTable.CustomPollerAssignmentID, CustomPollerStatusTable.CompressedRowID FROM CustomPollerStatusTable

           

          [Custom]

          INNER JOIN Nodes ON CustomPollerStatusTable.NodeID = Nodes.NodeID

          INNER JOIN CustomPollers ON CustomPollerStatusTable.PollerID = CustomPollers.PollerID

          INNER JOIN NodesCustomProperties ON Nodes.NodeID = NodesCustomProperties.NodeID

          WHERE

          (

          CustomPollers.UniqueName = 'LoadAvg'

          AND CustomPollerStatusTable.NumValue >= NodesCustomProperties.Threshold_LinuxLoadAvg15

          AND CustomPollerStatusTable.RowID = 3

          )