How to check if a node has all relevant custom pollers applied to it using SQWL

Hello fellow Community Guru's.

I am trying to find a way to report if a node does not have all relevant custom pollers applied.

I have tested this code which works.

The Node has a number of custom pollers attached and I would like to know if any are not being applied. I tried to expand the above code to include a test for the other custom pollers but it fails when more than one custom poller is listed. For example either of the next two variations provide nothing back. However they do not provide any error making me think the code itself is correct.

SELECT n.Caption, n.IP_Address, n.Vendor
FROM Orion.Nodes n
JOIN Orion.Vendors v ON n.Vendor = v.Name
WHERE n.NodeID NOT IN
( SELECT NodeID FROM Orion.NPM.CustomPollerAssignment WHERE CustomPollerName = 'Cellular_ID' OR CustomPollerName = 'hrSystemUptime' OR CustomPollerName = 'Internal_Temperature' OR CustomPollerName = 'modemGsmNetworkTechnology' OR CustomPollerName = 'Network_Provider' OR CustomPollerName = 'Signal_Strength' OR CustomPollerName = 'SIM_Card_Number' OR CustomPollerName = 'VA_BGP_STATE' OR CustomPollerName = 'vaInvSoftwareVersion' )
AND n.Vendor = 'Virtual Access Ltd'

SELECT n.Caption, n.IP_Address, n.Vendor
FROM Orion.Nodes n
JOIN Orion.Vendors v ON n.Vendor = v.Name
WHERE n.NodeID NOT IN
( SELECT NodeID FROM Orion.NPM.CustomPollerAssignment WHERE CustomPollerName IN ('Cellular_ID', 'hrSystemUptime', 'Internal_Temperature', 'modemGsmNetworkTechnology', 'Network_Provider', 'Signal_Strength', 'SIM_Card_Number', 'VA_BGP_STATE', 'vaInvSoftwareVersion') )
AND n.Vendor = 'Virtual Access Ltd'

There are hundreds of nodes with these custom pollers applied. The aim of the report is to list any nodes with vendor 'Virtual Access Ltd' and missing expected custom pollers being applied to them.

It is a little like having an array of custom pollers to check against. If any are not applied then flag this in the report.

Does anyone know how to do this?

Thank you.

Find more posts tagged with

Accepted answers

paulsmith310

Hi All,

For anyone looking to solve this in the future I have some code that works.

There are 9 custom pollers and I want to report on any routers that do not have all 9 applied to them.

SELECT n.Caption,n.IP_Address,n.Vendor
,CASE WHEN chk.NodeID IS NOT NULL THEN 1 ELSE 0 END AS cellular_check
,CASE WHEN chk1.NodeID IS NOT NULL THEN 1 ELSE 0 END AS hrSystemUptime_check
,CASE WHEN chk2.NodeID IS NOT NULL THEN 1 ELSE 0 END AS internal_temp_check
,CASE WHEN chk3.NodeID IS NOT NULL THEN 1 ELSE 0 END AS modemGsmNetworkTechnology_check
,CASE WHEN chk4.NodeID IS NOT NULL THEN 1 ELSE 0 END AS Network_Provider_check
,CASE WHEN chk5.NodeID IS NOT NULL THEN 1 ELSE 0 END AS Signal_Strength_check
,CASE WHEN chk6.NodeID IS NOT NULL THEN 1 ELSE 0 END AS SIM_Card_Number_check
,CASE WHEN chk7.NodeID IS NOT NULL THEN 1 ELSE 0 END AS VA_BGP_STATE_check
,CASE WHEN chk8.NodeID IS NOT NULL THEN 1 ELSE 0 END AS vaInvSoftwareVersion_check
FROM Orion.Nodes n
LEFT OUTER JOIN Orion.NPM.CustomPollerAssignment chk
ON chk.NodeID = n.NodeID
AND chk.CustomPollerName = 'Cellular_ID'
LEFT OUTER JOIN Orion.NPM.CustomPollerAssignment chk1
ON chk1.NodeID = n.NodeID
AND chk1.CustomPollerName = 'hrSystemUptime'
LEFT OUTER JOIN Orion.NPM.CustomPollerAssignment chk2
ON chk2.NodeID = n.NodeID
AND chk2.CustomPollerName = 'Internal_Temperature'
LEFT OUTER JOIN Orion.NPM.CustomPollerAssignment chk3
ON chk3.NodeID = n.NodeID
AND chk3.CustomPollerName = 'modemGsmNetworkTechnology'
LEFT OUTER JOIN Orion.NPM.CustomPollerAssignment chk4
ON chk4.NodeID = n.NodeID
AND chk4.CustomPollerName = 'Network_Provider'
LEFT OUTER JOIN Orion.NPM.CustomPollerAssignment chk5
ON chk5.NodeID = n.NodeID
AND chk5.CustomPollerName = 'Signal_Strength'
LEFT OUTER JOIN Orion.NPM.CustomPollerAssignment chk6
ON chk6.NodeID = n.NodeID
AND chk6.CustomPollerName = 'SIM_Card_Number'
LEFT OUTER JOIN Orion.NPM.CustomPollerAssignment chk7
ON chk7.NodeID = n.NodeID
AND chk7.CustomPollerName = 'VA_BGP_STATE'
LEFT OUTER JOIN Orion.NPM.CustomPollerAssignment chk8
ON chk8.NodeID = n.NodeID
AND chk8.CustomPollerName = 'vaInvSoftwareVersion'
WHERE n.Vendor = 'Virtual Access Ltd'
AND CASE WHEN chk.NodeID IS NOT NULL THEN 1 ELSE 0 END +
CASE WHEN chk1.NodeID IS NOT NULL THEN 1 ELSE 0 END +
CASE WHEN chk2.NodeID IS NOT NULL THEN 1 ELSE 0 END +
CASE WHEN chk3.NodeID IS NOT NULL THEN 1 ELSE 0 END +
CASE WHEN chk4.NodeID IS NOT NULL THEN 1 ELSE 0 END +
CASE WHEN chk5.NodeID IS NOT NULL THEN 1 ELSE 0 END +
CASE WHEN chk6.NodeID IS NOT NULL THEN 1 ELSE 0 END +
CASE WHEN chk7.NodeID IS NOT NULL THEN 1 ELSE 0 END +
CASE WHEN chk8.NodeID IS NOT NULL THEN 1 ELSE 0 END < 9

In the report I updated the column headings to make it smaller and removed a few more custom pollers from other routers to get the end result.

Each custom poller is in the report with a 1 denoting it has been applied and a 0 showing where it has accidentally been left unchecked. So the SIM and Software checks are clearly missing on these routers.

The steps in the report part as below for further help getting the final result.

Change the display name

I edited the column widths to fit it all in. Hopefully this template will be useful for others to adjust to fit to their organisation requirements.

All comments

paulsmith310

I found that the JOIN command is surplus to requirements.

I still cannot find a way to use the OR logic where multiple custom pollers need to be checked rather than just one in the example. Any help much appreciated.

paulsmith310

Hi All,

For anyone looking to solve this in the future I have some code that works.

There are 9 custom pollers and I want to report on any routers that do not have all 9 applied to them.

In the report I updated the column headings to make it smaller and removed a few more custom pollers from other routers to get the end result.

The steps in the report part as below for further help getting the final result.

Change the display name

I edited the column widths to fit it all in. Hopefully this template will be useful for others to adjust to fit to their organisation requirements.

villerblender323

To check if a node is missing any required custom pollers in SWQL without diving into complex queries, think of it like verifying if your Mary Brown’s Chicken meal includes all expected items—if even one is missing, it’s incomplete. Similarly, you need to compare each node against a full list of expected pollers and flag any node that doesn’t have all of them. The key is not checking for just one missing poller, but whether any from the full list are missing. A summary report or grouped count approach can help you spot those incomplete nodes easily.