Putting the results of 2 custom pollers side-by-side in swql

daan-ce

Hello community

I've been breaking my head over something that looks very simple.

I have created two universal device pollers that query (1) the hostnames of all joined access points on a controller and (2) the serial numbers of each joined access point.

Orion puts the results of both pollers in the following table: Orion.NPM.CustomPollerStatus.

Now I want to create a report with SWQL containing 2 columns: Hostname and serial number for each access point.

I can easily query the table and get the values in a row:

SELECT pol1.RowID, pol1.status, pol2.status
FROM Orion.NPM.CustomPollerStatus pol1
RIGHT JOIN Orion.NPM.CustomPollerStatus pol2
ON pol1.RowID = pol2.RowID

The RowID is always matching for each hostname-serial pair. I have been working with self-joins but can't get the result I want.

Any help would be very welcome.

Thanks

dodo123

i dont have the same undps but this may help

Select distinct n.caption, Status1.status as Firstmetric, Status2.status as SecondMetric 
FROM 
Orion.nodes n 
JOIN orion.npm.CustomPollerAssignment A on n.nodeid = a.NodeID
Join Orion.npm.CustomPollerStatus S on a.CustomPollerAssignmentID = s.CustomPollerAssignmentID
join (SELECT Status, custompollerassignmentid 
FROM Orion.NPM.CustomPollerStatus
where custompollerassignmentid = 'c2cf3bc6-b81e-4e94-9af4-21e166fd2511' and RowID = 1) as Status1 on a.CustomPollerAssignmentID = Status1.CustomPollerAssignmentid
Join (SELECT Status, custompollerassignmentid
FROM Orion.NPM.CustomPollerStatus
where custompollerassignmentid = 'c2cf3bc6-b81e-4e94-9af4-21e166fd2511' and RowID = 2) as Status2 on a.CustomPollerAssignmentID = Status2.custompollerassignmentid