From OpenNMS

My employers make extensive use of asset categories, in particular pollercategory and notifycategory to customise poller packages and notification paths for various devices. The downside to this is that each node needs to be categorised via the webUI and OpenNMS restarted before any new nodes will get polled. We got around this by running a small chunk of sql daily to identify “uncategorised nodes” thus:

SELECT t1.nodeid as "nodeid", t0.nodelabel AS "node", t1.displaycategory,
t1.pollercategory, t1.notifycategory,t1.thresholdcategory
FROM node t0 LEFT OUTER JOIN assets t1
ON (t0.nodeid = t1.nodeid)
WHERE (t1.pollercategory IS NULL
OR t1.notifycategory IS NULL
OR t1.displaycategory IS NULL)
ORDER BY t1.nodeid ASC;

You’ll notice that this does not check the threholdcategory. Adding this would be simple though.

This whole scheme started to become a real pain for some nodes. In particular, VMware. virtual machines. We have an entire network of nodes that are virtual machines. These are often transient, running for a few days or weeks only. Continually having to categorise these nodes via the webUI became a pain. To avoid this we added some display categories, notices and poller packages based on the node’s IP address, instead of asset categories. This all worked fine apart from the fact that these transient nodes were appearing on our “uncategorised nodes” report. The report was only intended to indicate nodes that required asset data updates before they would be polled, and therefore should not contain these nodes. This is where iplike came in handy. We modified our sql thus:

SELECT t1.nodeid as "nodeid", t0.nodelabel AS "node", t1.displaycategory,
t1.pollercategory, t1.notifycategory, t1.thresholdcategory
FROM node t0 LEFT OUTER JOIN assets t1
ON (t0.nodeid = t1.nodeid)
WHERE (t1.pollercategory IS NULL
OR t1.notifycategory IS NULL
OR t1.displaycategory IS NULL)
AND t1.nodeid NOT IN (SELECT nodeid from ipinterface WHERE iplike(ipinterface.ipaddr,'192.168.1.*'))
ORDER BY t1.nodeid ASC;

You can see the use of iplike to exclude any node with an IP address in the 192.186.1.* range, which happens to contain all the VMware virtual machines. This is probably not the most efficient piece of SQL ever (I’m no DBA), but does the trick. Now our report only shows nodes that will not be displayed or polled or not cause notices to be generated.