Posted Thu, 01 Jan 2015 06:00:00 GMT by Portal Admin

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;

You must be signed in to post in this forum.