SQL Report on multiple Universal Device Pollers

Question

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?

solarwooky · Answer

I'm getting a blank report.

JiriPsota · Answer

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)

solarwooky · Answer

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