3 Replies Latest reply on May 16, 2013 3:39 PM by solarwooky

    Custom Poller with Custom Property Report

    solarwooky

      undp
      Alright all of you SQL Report Gurus, I have a query I want to put together.

       

      I have created a Universal Device Poller for ifInErrors OID 1.3.6.1.2.1.2.2.1.14 and use the counter value with Time Frame in Seconds.

      This produces a rate of increase for interface errors per second.

      ifInErrors UnDP.png

       

      I have an alert based on this UnDP, if any ifInErrors are > 0 then send an alert. I also have a custom interface property for "Critical Interfaces". How do I create a report/alert which will constrain the results of the IfInErrors > 0 by the Critical Interfaces Custom Interface Property?

       

      Here is the query I use to pull the ifInErrors > 0:

      SELECT
      Nodes.NodeID AS NodeID,
      Nodes.Caption AS NodeName,
      Nodes.VendorIcon AS Vendor_Icon,
      Nodes.IP_Address AS IP_Address,
      Nodes.MachineType AS Machine_Type,
      CustomPollerLabels.Label,
      CustomPollerStatus.Status

      FROM
      Nodes, CustomPollerStatus, CustomPollerLabels, CustomPollerAssignment, CustomPollers

      WHERE
      Nodes.NodeID = CustomPollerAssignment.NodeID
      AND CustomPollerAssignment.CustomPollerAssignmentID = CustomPollerLabels.CustomPollerAssignmentID
      AND CustomPollerAssignment.CustomPollerAssignmentID = CustomPollerStatus.CustomPollerAssignmentID
      AND CustomPollerStatus.RowID = CustomPollerlabels.RowID AND CustomPollerAssignment.CustomPollerID = CustomPollers.CustomPollerID
      AND CustomPollers.UniqueName = 'ifInErrors' AND CustomPollerStatus.Status >0

      ORDER BY
      Nodes.Caption,
      Nodes.NodeID,
      Nodes.VendorIcon,
      Nodes.IP_Address,
      Nodes.MachineType,
      CustomPollerLabels.Label,
      CustomPollerStatus.Status

       

      I ultimately need to constrain these results to only interfaces withe the custom property "Critical_Interface".

       

      PLEASE HELP

        • Re: Custom Poller with Custom Property Report
          solarwooky


          Ok so I made a change and I streamed down the list but I have a bunch of duplicates now:

           

          SELECT
          Nodes.NodeID AS NodeID,
          Nodes.Caption AS NodeName,
          Nodes.VendorIcon AS Vendor_Icon,
          Nodes.IP_Address AS IP_Address,
          Nodes.MachineType AS Machine_Type,
          CustomPollerLabels.Label,
          Interfaces.Critical_Interface,
          CustomPollerStatus.Status

          FROM
          Nodes, CustomPollerStatus, CustomPollerLabels, CustomPollerAssignment, CustomPollers, Interfaces

          WHERE
          Nodes.NodeID = CustomPollerAssignment.NodeID
          AND CustomPollerAssignment.CustomPollerAssignmentID = CustomPollerLabels.CustomPollerAssignmentID
          AND CustomPollerAssignment.CustomPollerAssignmentID = CustomPollerStatus.CustomPollerAssignmentID
          AND CustomPollerStatus.RowID = CustomPollerlabels.RowID AND CustomPollerAssignment.CustomPollerID = CustomPollers.CustomPollerID AND Interfaces.InterfaceName = CustomPollerLabels.Label
          AND CustomPollers.UniqueName = 'ifInErrors' AND CustomPollerStatus.Status >0 AND Interfaces.Critical_Interface =1

          ORDER BY
          Nodes.Caption,
          Nodes.NodeID,
          Nodes.VendorIcon,
          Nodes.IP_Address,
          Nodes.MachineType,
          CustomPollerLabels.Label,
          CustomPollerStatus.Status

            • Re: Custom Poller with Custom Property Report
              solarwooky

              With a simple DISTINCT I eliminated duplicates. This is what I finally got:

               

              SELECT DISTINCT
              Nodes.NodeID AS NodeID,
              Nodes.Caption AS NodeName,
              Nodes.VendorIcon AS Vendor_Icon,
              Nodes.IP_Address AS IP_Address,
              Nodes.MachineType AS Machine_Type,
              CustomPollerLabels.Label,
              Interfaces.Critical_Interface,
              CustomPollerStatus.Status

              FROM
              Nodes, CustomPollerStatus, CustomPollerLabels, CustomPollerAssignment, CustomPollers, Interfaces

              WHERE
              Nodes.NodeID = CustomPollerAssignment.NodeID
              AND CustomPollerAssignment.CustomPollerAssignmentID = CustomPollerLabels.CustomPollerAssignmentID
              AND CustomPollerAssignment.CustomPollerAssignmentID = CustomPollerStatus.CustomPollerAssignmentID
              AND CustomPollerStatus.RowID = CustomPollerlabels.RowID AND CustomPollerAssignment.CustomPollerID = CustomPollers.CustomPollerID AND Interfaces.InterfaceName = CustomPollerLabels.Label
              AND CustomPollers.UniqueName = 'ifInErrors' AND CustomPollerStatus.Status >0 AND Interfaces.Critical_Interface =1

              ORDER BY
              Nodes.Caption,
              Nodes.NodeID,
              Nodes.VendorIcon,
              Nodes.IP_Address,
              Nodes.MachineType,
              CustomPollerLabels.Label,
              CustomPollerStatus.Status