Q. I need to provide a list of the devices monitored by InterMapper and the notifiers attached to them. It can be either a full device list showing the attached notifiers, or lists for each notifier showing what devices they are attached to. Is there a way to do this?
A. One way to do this is to use IMDatabase. Your SQL query will join the "device" and "notifier" tables via "notifierrule".
This query generates a report of device name with notifier count and comma-separated list of notifiers. Devices without any attached notifiers appear in the name column also. (The query is complicated by making a comma-separated list of notifiers.)
Code: |
SELECT d.name, x.cnt, x.notifiers FROM device d LEFT OUTER JOIN (SELECT server_id, map_id, device_id, COUNT(o.*) AS cnt, ARRAY_TO_STRING( ARRAY ( SELECT n.name FROM notifier n, notifierrule i WHERE i.server_id = o.server_id AND i.map_id = o.map_id AND i.device_id = o.device_id AND n.server_id = i.server_id AND n.notifier_id = i.notifier_id ORDER BY n.name ), ', ') AS notifiers FROM notifierrule o GROUP BY o.server_id, o.map_id, o.device_id ) x ON d.server_id = x.server_id AND d.map_id = x.map_id AND d.device_id = x.device_id WHERE d.delete_time = 'infinity' ORDER BY d.name; |
|
|