4 Replies Latest reply on Dec 28, 2012 12:22 PM by solarwooky

    SQL Report on multiple Universal Device Pollers

    robguent

      I have created UDP's to get slot hardware and serials from cisco routers from the entPhysicalTable mib. The values are in the status field of Orion.

      (1) How do I get to report more than one UDP in a SQL report? Everything that is within one row of a mib table should be logically reported within one row of an SQL report. With the current structure in Orion this seems not possible or do I miss something.

      The table UDP view on the website works at least and it would be great to get it into SQL reports like this as well.

      (2) in SQL I cannot use some filtering on the status UDP field. eg. IS NOT EMPTY would be quite feasible, as a lot of the slot will not be used; however it always reports all field when used also the empty ones. On the other hand filtering on entries that only start with "N" works like a charm. Any ideas?

        • Re: SQL Report on multiple Universal Device Pollers
          robguent

          To elaborate I attach an SQL report:

           

          
           SELECT  TOP 10000
              Nodes.MachineType AS Machine_Type,
              Nodes.Region AS Region,
              Nodes.NetworkName AS NetworkName,
              Nodes.SPID AS SPID,
              Nodes.Caption AS NodeName,
              Nodes.IOSVersion AS IOS_Version,
              CustomNodePollers_CustomPollers.UniqueName AS Poller_Name,
              CustomNodePollerStatistics_CustomPollerStatistics.Status AS Status,
              MAX(DATEADD(hh, DATEDIFF(hh, GETUTCDATE(), GETDATE()) , CustomNodePollerStatistics_CustomPollerStatistics.DateTime))
                  AS MAX_of_DateTime,
              Nodes.NodeID AS NodeID
          
          FROM
              ((Nodes 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
              (Nodes.Vendor = 'cisco') AND
              (CustomNodePollers_CustomPollers.UniqueName = 'Module') AND
              --Last 70 minutes
              --(datetime between dateadd(n,-70,getdate()) and getdate()) AND
              --
          --    (CustomNodePollerStatistics_CustomPollerStatistics."RowID" > '11') AND
          --    (CustomNodePollerStatistics_CustomPollerStatistics."RowID" < '22') AND
              (
                  (CustomNodePollerStatistics_CustomPollerStatistics.Status LIKE  'S%') OR
                  (CustomNodePollerStatistics_CustomPollerStatistics.Status LIKE  'N%') OR
                  (CustomNodePollerStatistics_CustomPollerStatistics.Status LIKE  'H%') OR
                  (CustomNodePollerStatistics_CustomPollerStatistics.Status LIKE  'W%') OR
                  (CustomNodePollerStatistics_CustomPollerStatistics.Status LIKE  'F%')
              )
          --    AND (CustomNodePollerStatistics_CustomPollerStatistics.Status IS NOT NULL)
          --    AND (CustomNodePollerAssignment_CustomPollerAssignment.InterfaceID = 0)
          
          GROUP BY
              Nodes.MachineType,
              Nodes.Region,
              Nodes.NetworkName ,
              Nodes.SPID ,
              Nodes.Caption ,
              Nodes.IOSVersion ,
              CustomNodePollers_CustomPollers.UniqueName ,
              CustomNodePollerStatistics_CustomPollerStatistics.Status ,
              Nodes.NodeID
          
          


          (1) Here I just show the latest poll on one Universal Device Poller (Module). If I don't filter on the poller name I get more rows per poller for all other UDP's. Those UDP's should be columns instead of rows.

           

          (2) I think that might be also the issue why I can't filter to take out empty Modules as in one of the Status-fields of some UDP something might be included?

            • Re: SQL Report on multiple Universal Device Pollers
              solarwooky

              I'd like to see an answer to this one as well. Its been one year with no answer. Hello Solarwinds!!!

                • Re: SQL Report on multiple Universal Device Pollers
                  JiriPsota

                  Hi,

                  with the following query you should get the results very similar to the table on web

                   

                  SELECT n.Caption

                        , ModuleLabel.Label

                        , SerialModuleSlotType.Status "SerialModuleSlotType"

                        , Module.Status "Module"

                    FROM Nodes n JOIN

                        (SELECT cpa.NodeID

                               , cpl.rowid

                               , cpl.Label

                           FROM CustomPollerLabels cpl JOIN CustomPollerAssignment cpa ON (cpl.CustomPollerAssignmentID = cpa.CustomPollerAssignmentID)

                                                       JOIN CustomPollers cp ON (cpa.PollerID = cp.PollerID) WHERE cp.UniqueName='Module') ModuleLabel

                                                         ON (n.NodeID = ModuleLabel.NodeID)

                                LEFT JOIN (SELECT cpa.NodeID

                                                , cps.rowid

                                                , cps.status

                                             FROM CustomPollerStatus cps JOIN CustomPollerAssignment cpa ON (cps.CustomPollerAssignmentID = cpa.CustomPollerAssignmentID)

                                                                         JOIN CustomPollers cp ON (cpa.PollerID = cp.PollerID) WHERE cp.UniqueName='SerialModuleSlotType') SerialModuleSlotType

                                                                           ON (n.NodeID=SerialModuleSlotType.NodeID AND ModuleLabel.RowID=SerialModuleSlotType.RowID)

                                LEFT JOIN (SELECT cpa.NodeID

                                                , cps.rowid

                                                , cps.status

                                             FROM CustomPollerStatus cps JOIN CustomPollerAssignment cpa ON (cps.CustomPollerAssignmentID=cpa.CustomPollerAssignmentID)

                                                                         JOIN CustomPollers cp ON (cpa.PollerID=cp.PollerID) WHERE cp.UniqueName='Module') Module

                                                                           ON (n.NodeID=Module.NodeID AND SerialModuleSlotType.RowID=Module.RowID)