On 1/13/10 12:42 PM, Chip Old wrote:
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.
I could've sworn there was a way to do this, but now I can't find it and I have
a feeling I'm overlooking something really obvious.
We have InterMapper Server 5.2 on Fedora Linux, managed via IM Remote 5.2 on
Mac OS X.
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.)
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;
--
Bill Fisher
Dartware, LLC
____________________________________________________________________
List archives:
http://www.mail-archive.com/intermapper-talk%40list.dartware.com/
To unsubscribe: send email to: [email protected]
