Hi guys,
Icinga2 version: r2.13.1-1
Operating System: Debian 9
I search for an MySQL statement to figure out, which host or service object has not be configured to use a given “Contact” and “Contactgroup”
E.g.:
I have a contact “testcontact” and Contactgroup “testcontactgroup” and have attached it to may all host and services. Now I want to figure out, to which hosts/services it is not attached.
Any help is appreciated.
Kind regards
Peer-Mario
That’s what Icinga Web 2 issues to fetch notifications, their contacts and hosts/services. Though, you have to adjust it to your needs first:
SELECT n.id,
n.host_display_name,
n.host_name,
n.notification_contact_name,
n.notification_output,
n.notification_state,
n.notification_timestamp,
n.service_description,
n.service_display_name
FROM ((SELECT hn.notification_id AS id,
h.display_name AS host_display_name,
ho.name1 AS
host_name,
co.name1 AS notification_contact_name,
hn.output AS
notification_output,
hn.state AS notification_state,
UNIX_TIMESTAMP(hn.start_time) AS notification_timestamp,
NULL AS
service_description,
NULL AS service_display_name
FROM icinga_notifications
AS hn
INNER JOIN icinga_objects AS ho ON ho.object_id = hn.object_id AND
ho.is_active = 1 AND ho.objecttype_id = 1
INNER JOIN icinga_hosts AS h ON h.host_object_id = ho.object_id
LEFT JOIN icinga_contactnotifications AS cn ON cn.notification_id =
hn.notification_id
LEFT JOIN icinga_objects AS co ON co.object_id = cn.contact_object_id AND
co.is_active = 1 AND co.objecttype_id = 10
GROUP BY hn.notification_id,
co.object_id,
ho.object_id,
h.host_id)
UNION ALL
(SELECT sn.notification_id AS id,
h.display_name AS
host_display_name,
so.name1 AS host_name,
co.name1 AS
notification_contact_name,
sn.output AS notification_output,
sn.state AS
notification_state,
UNIX_TIMESTAMP(sn.start_time) AS
notification_timestamp,
so.name2 AS service_description,
s.display_name AS
service_display_name
FROM icinga_notifications AS sn
INNER JOIN icinga_objects AS so ON so.object_id = sn.object_id AND
so.is_active = 1 AND so.objecttype_id = 2
INNER JOIN icinga_services AS s ON s.service_object_id = so.object_id
INNER JOIN icinga_hosts AS h ON h.host_object_id = s.host_object_id
LEFT JOIN icinga_contactnotifications AS cn ON cn.notification_id =
sn.notification_id
LEFT JOIN icinga_objects AS co ON co.object_id = cn.contact_object_id AND
co.is_active = 1 AND co.objecttype_id = 10
GROUP BY sn.notification_id,
co.object_id,
so.object_id,
h.host_id,
s.service_id)) AS n
ORDER BY n.notification_timestamp DESC;
Thanks Johannes,
for your answer. The issue here is to understand this statement. I have only basis knowledge in SQL but it will may help, If I show this statment to an SQL expert.
Kind regards
Peer-Mario