SWQL query UnDP with custom property on Node

blkchampion

I'm trying to write a swql query that combines 4 universal Device pollers with a custom property on a node. 

One of the UnDP's is collated to a custom property that is attached on a Node.  I want to state again the node has the custom property but one of the UnDP's has the same value as the custom property.  

I need to know how to write a query that has the values of all 4 UnDP's and that its joined by the values from the custom property and the one of the UnDP.  

Seashore

Hi, not sure I understand exactly what you are after but this is and example of a script I use alot when I want to display several UnDP's in one table. It's not that easy to join several UnDPs together as you see but this one works for me. 

One one row I have added "AND S2.Status=N.CustomProperties.MyCP" and this is what i THINK is what you are after.

SELECT 
    N.Caption as [NodeName]
    ,ISNULL(L.Label,'-') AS [Module]
    ,S1.Status AS [Temp Status]
    ,S2.Status + ' °C' AS [Temp value]


    ,N.DetailsUrl as [_LinkFor_NodeName]
FROM Orion.Nodes AS N
    INNER JOIN Orion.NPM.CustomPollerAssignment AS A ON N.Nodeid=A.NodeID
    INNER JOIN Orion.NPM.CustomPollerStatus AS S1 ON A.CustomPollerAssignmentID=S1.CustomPollerAssignmentID AND A.CustomPollerName='tempSensorStatus'
-- Get Label
    INNER join Orion.NPM.CustomPollerLabels AS L on A.CustomPollerAssignmentID=L.CustomPollerAssignmentID AND S1.RowID=L.RowID 
    LEFT OUTER JOIN ORION.NPM.CustomPollerAssignment AS A2 ON N.Nodeid=A2.NodeID AND A2.CustomPollerName='tempSensorCurrentValue'
    LEFT OUTER JOIN Orion.NPM.CustomPollerStatus AS S2 ON A2.CustomPollerAssignmentID=S2.CustomPollerAssignmentID AND S2.RowID=L.RowID AND S2.Status=N.CustomProperties.MyCP
--    LEFT OUTER JOIN ORION.NPM.CustomPollerAssignment AS A3 ON N.Nodeid=A3.NodeID AND A3.CustomPollerName='psStatus'
--    LEFT OUTER JOIN Orion.NPM.CustomPollerStatus AS S3 ON A3.CustomPollerAssignmentID=S3.CustomPollerAssignmentID AND S3.RowID=L.RowID
--    LEFT OUTER JOIN ORION.NPM.CustomPollerAssignment AS A4 ON N.Nodeid=A4.NodeID AND A4.CustomPollerName='psActiveJobStatus'
--    LEFT OUTER JOIN Orion.NPM.CustomPollerStatus AS S4 ON A4.CustomPollerAssignmentID=S4.CustomPollerAssignmentID AND S4.RowID=L.RowID
--WHERE 
--  (L.Label LIKE '%${SEARCH_STRING}%' OR S1.Status LIKE '%${SEARCH_STRING}%')
ORDER BY S1.RawStatus desc,N.caption,L.Label--, S1.Status

Hope it helps.