4 Replies Latest reply on Feb 26, 2014 6:14 PM by j sachs

    Universal Device Poller Report

    j sachs

      I am trying to create a report to show me the top 10 nodes with T1 errored seconds.   For example, the node below only has errored seconds on one T1, but this report is showing errors on all of them.  If I do not limit the report to the top 10 then I also get errored seconds that show less than 150 even though I have specified only to show greater than.  It would also be nice to show the interface with issues only once in the report instead of at different time intervals.

       

      OID 1.3.6.1.2.1.10.18.9.1.2

       

      ErroredSecondsResults.png

       

      SELECT NodeName,

      IP_Address,

      Interface_Name,

      Poller_Name,

      CAST(Status AS varchar) AS Status,

      DateTime FROM (

       

      SELECT TOP 10

      Nodes.Caption AS NodeName,

      Nodes.IP_Address AS IP_Address,

      Interfaces.InterfaceName AS Interface_Name,

      CustomNodePollers_CustomPollers.UniqueName AS Poller_Name,

      CAST(CustomNodePollerStatistics_CustomPollerStatistics.Status AS real) AS Status,

      DATEADD(hh, DATEDIFF(hh, GETUTCDATE(), GETDATE()) , CustomNodePollerStatistics_CustomPollerStatistics.DateTime) AS DateTime

      FROM

      (((Nodes INNER JOIN Interfaces ON (Nodes.NodeID = Interfaces.NodeID))  INNER JOIN CustomPollerAssignment CustomNodePollerAssignment_CustomPollerAssignment ON (Nodes.NodeID = CustomNodePollerAssignment_CustomPollerAssignment.NodeID))  INNER JOIN CustomPollerStatistics CustomNodePollerStatistics_CustomPollerStatistics ON (CustomNodePollerAssignment_CustomPollerAssignment.CustomPollerAssignmentID = CustomNodePollerStatistics_CustomPollerStatistics.CustomPollerAssignmentID))  INNER JOIN CustomPollers CustomNodePollers_CustomPollers ON (CustomNodePollerAssignment_CustomPollerAssignment.CustomPollerID = CustomNodePollers_CustomPollers.CustomPollerID)

       

      WHERE

      ( DATEADD(hh, DATEDIFF(hh, GETUTCDATE(), GETDATE()) , DateTime) BETWEEN 41693 AND 41693.375 )

      AND

      (

        (CustomNodePollers_CustomPollers.UniqueName = 'ErrSec') AND

        (Interfaces.InterfaceName LIKE '%T1%') AND

        (CustomNodePollerStatistics_CustomPollerStatistics.Status >= '150')

      )

      AND

      (

      (CustomNodePollerAssignment_CustomPollerAssignment.InterfaceID = 0)

      )

      AND isnumeric(CustomNodePollerStatistics_CustomPollerStatistics.Status) = 1

       

      ORDER BY 5 DESC

       

      ) A UNION ALL

      SELECT NodeName,

      IP_Address,

      Interface_Name,

      Poller_Name,

      CAST(Status AS varchar) AS Status,

      DateTime FROM (

       

      SELECT TOP 10

      Nodes.Caption AS NodeName,

      Nodes.IP_Address AS IP_Address,

      Interfaces.InterfaceName AS Interface_Name,

      CustomNodePollers_CustomPollers.UniqueName AS Poller_Name,

      CustomNodePollerStatistics_CustomPollerStatistics.Status AS Status,

      DATEADD(hh, DATEDIFF(hh, GETUTCDATE(), GETDATE()) , CustomNodePollerStatistics_CustomPollerStatistics.DateTime) AS DateTime

      FROM

      (((Nodes INNER JOIN Interfaces ON (Nodes.NodeID = Interfaces.NodeID))  INNER JOIN CustomPollerAssignment CustomNodePollerAssignment_CustomPollerAssignment ON (Nodes.NodeID = CustomNodePollerAssignment_CustomPollerAssignment.NodeID))  INNER JOIN CustomPollerStatistics CustomNodePollerStatistics_CustomPollerStatistics ON (CustomNodePollerAssignment_CustomPollerAssignment.CustomPollerAssignmentID = CustomNodePollerStatistics_CustomPollerStatistics.CustomPollerAssignmentID))  INNER JOIN CustomPollers CustomNodePollers_CustomPollers ON (CustomNodePollerAssignment_CustomPollerAssignment.CustomPollerID = CustomNodePollers_CustomPollers.CustomPollerID)

       

      WHERE

      ( DATEADD(hh, DATEDIFF(hh, GETUTCDATE(), GETDATE()) , DateTime) BETWEEN 41693 AND 41693.375 )

      AND

      (

        (CustomNodePollers_CustomPollers.UniqueName = 'ErrSec') AND

        (Interfaces.InterfaceName LIKE '%T1%') AND

        (CustomNodePollerStatistics_CustomPollerStatistics.Status >= '150')

      )

      AND

      (

      (CustomNodePollerAssignment_CustomPollerAssignment.InterfaceID = 0)

      )

       

      AND isnumeric(CustomNodePollerStatistics_CustomPollerStatistics.Status) != 1

       

      ORDER BY 5 DESC

       

      ) B