I have 2 devices that dynamically create OID's based on if the sensors are connected or not. In the custom query below, some devices do not show up in the list because there is no CustomPollerID because there may not be an OID associated. The first device displays everything correctly because all sensors are connected and all OID are there. The second device doesn't even show up in the query because there might be one or two missing OID's. Is there a way to void or null those entries that have no CustomPollerID assigned?
SELECT
n.Caption AS [Node]
,n.DetailsUrl AS [_LinkFor_Node]
,'/Orion/images/StatusIcons/Small-' + n.StatusIcon AS [_IconFor_Node]
,t1.Status AS [Sensor 0]
,t2.Status/100 AS [Temp 0]
,t3.Status AS [Sensor 1]
,t4.Status/100 AS [Temp 1]
,t5.Status/100 AS [Humid 1]
,t6.Status AS [Sensor 2]
,t7.Status/100 AS [Temp 2]
,t8.Status/100 AS [Humid 2]
,t9.Status AS [Sensor 3]
,t10.Status/100 AS [Temp 3]
,t11.Status/100 AS [Humid 3]
FROM Orion.Nodes n
JOIN
(
SELECT
cps.NodeID
,cps.RowID
,cps.CustomPollerID
,cps.AssignmentName
,cps.Status
FROM Orion.NPM.CustomPollerStatusOnNode cps
WHERE cps.CustomPollerID = '968c727b-263a-4671-8154-52f2ed524cc1' --int_name
) AS t1 ON n.NodeID = t1.NodeID
JOIN
(
SELECT
cps.NodeID
,cps.RowID
,cps.CustomPollerID
,cps.AssignmentName
,cps.Status
FROM Orion.NPM.CustomPollerStatusOnNode cps
WHERE cps.CustomPollerID = 'fbcf5f17-d1b0-4387-82b3-42991224a01d' --int_temp
) AS t2 ON n.NodeID = t2.NodeID AND t1.RowID = t2.RowID
JOIN
(
SELECT
cps.NodeID
,cps.RowID
,cps.CustomPollerID
,cps.AssignmentName
,cps.Status
FROM Orion.NPM.CustomPollerStatusOnNode cps
WHERE cps.CustomPollerID = '5fb0f2ad-0f66-42ef-acbc-7f43f57bc3bf' --sensor1_name
) AS t3 ON n.NodeID = t3.NodeID AND t1.RowID = t3.RowID
JOIN
(
SELECT
cps.NodeID
,cps.RowID
,cps.CustomPollerID
,cps.AssignmentName
,cps.Status
FROM Orion.NPM.CustomPollerStatusOnNode cps
WHERE cps.CustomPollerID = '1b3acb5d-9440-4cde-aaea-aeacb66290b8' --sensor1_temp
) AS t4 ON n.NodeID = t4.NodeID AND t1.RowID = t4.RowID
JOIN
(
SELECT
cps.NodeID
,cps.RowID
,cps.CustomPollerID
,cps.AssignmentName
,cps.Status
FROM Orion.NPM.CustomPollerStatusOnNode cps
WHERE cps.CustomPollerID = 'b98657ad-d3fd-456f-8c3c-a3db41fd6e87' --sensor1_humidity
) AS t5 ON n.NodeID = t5.NodeID AND t1.RowID = t5.RowID
JOIN
(
SELECT
cps.NodeID
,cps.RowID
,cps.CustomPollerID
,cps.AssignmentName
,cps.Status
FROM Orion.NPM.CustomPollerStatusOnNode cps
WHERE cps.CustomPollerID = 'af11c5c7-9be3-4514-ae14-34ee92eaeba1' --sensor2_name
) AS t6 ON n.NodeID = t6.NodeID AND t1.RowID = t6.RowID
JOIN
(
SELECT
cps.NodeID
,cps.RowID
,cps.CustomPollerID
,cps.AssignmentName
,cps.Status
FROM Orion.NPM.CustomPollerStatusOnNode cps
WHERE cps.CustomPollerID = 'bc9791aa-98f4-4e8d-9a0f-ab43273c910c' --sensor2_temp
) AS t7 ON n.NodeID = t7.NodeID AND t1.RowID = t7.RowID
JOIN
(
SELECT
cps.NodeID
,cps.RowID
,cps.CustomPollerID
,cps.AssignmentName
,cps.Status
FROM Orion.NPM.CustomPollerStatusOnNode cps
WHERE cps.CustomPollerID = '35c0a91d-7cd8-4c7a-8844-68012abb38c8' --sensor2_humidity
) AS t8 ON n.NodeID = t8.NodeID AND t1.RowID = t8.RowID
JOIN
(
SELECT
cps.NodeID
,cps.RowID
,cps.CustomPollerID
,cps.AssignmentName
,cps.Status
FROM Orion.NPM.CustomPollerStatusOnNode cps
WHERE cps.CustomPollerID = 'c7a1a735-1795-4731-9070-e2f29f14707d' --sensor3_name
) AS t9 ON n.NodeID = t9.NodeID AND t1.RowID = t9.RowID
JOIN
(
SELECT
cps.NodeID
,cps.RowID
,cps.CustomPollerID
,cps.AssignmentName
,cps.Status
FROM Orion.NPM.CustomPollerStatusOnNode cps
WHERE cps.CustomPollerID = '828ec464-c58d-4681-af08-8c1d43f27144' --sensor3_temp
) AS t10 ON n.NodeID = t10.NodeID AND t1.RowID = t10.RowID
JOIN
(
SELECT
cps.NodeID
,cps.RowID
,cps.CustomPollerID
,cps.AssignmentName
,cps.Status
FROM Orion.NPM.CustomPollerStatusOnNode cps
WHERE cps.CustomPollerID = '6797a00e-2054-4e90-9328-52fcff8a78c4' --sensor3_humidity
) AS t11 ON n.NodeID = t11.NodeID AND t1.RowID = t11.RowID
ORDER BY n.Caption
