Page 1 of 1
Finding hosts and services not in groups
Posted: Tue Aug 11, 2020 2:26 am
by ottow
Hi,
What is the recommended way to locate hosts and services that have _no_ groups (host, contact, service) assigned?
Is there perhaps some report for this (I can't find one), or maybe this needs to be done via SQL query?
Thanks,
Otto
Re: Finding hosts and services not in groups
Posted: Tue Aug 11, 2020 3:46 pm
by scottwilkerson
We don't have a report for that, you could either program something through the API or, here is a few sample queries that I think will get you close
Services not in service groups
Code: Select all
SELECT o.name1 as host_name, o.name2 AS service_description
FROM nagios_objects o
WHERE o.object_id NOT IN (SELECT service_object_id from nagios_servicegroup_members)
AND is_active=1
AND objecttype_id=2;
Hosts not in host groups
Code: Select all
SELECT o.name1 as host_name
FROM nagios_objects o
WHERE o.object_id NOT IN (SELECT host_object_id from nagios_hostgroup_members)
AND is_active=1
AND objecttype_id=1;
Re: Finding hosts and services not in groups
Posted: Wed Aug 12, 2020 2:55 am
by ottow
Thanks!
Could you please show how to find hosts and services with no Contact Groups defined too?
Re: Finding hosts and services not in groups
Posted: Wed Aug 12, 2020 6:59 am
by scottwilkerson
ottow wrote:Thanks!
Could you please show how to find hosts and services with no Contact Groups defined too?
Sure
Code: Select all
SELECT o.name1 as contact
FROM nagios_objects o
WHERE o.object_id NOT IN (SELECT contact_object_id from nagios_contactgroup_members)
AND is_active=1
AND objecttype_id=10;
Re: Finding hosts and services not in groups
Posted: Wed Aug 12, 2020 7:17 am
by ottow
I am sorry but I just tested the above and it does not show the hosts or services I have 0 contact groups defined for.
MariaDB [nagios]> SELECT o.name1 as contact FROM nagios_objects o WHERE o.object_id NOT IN (SELECT contact_object_id from nagios_contactgroup_members) AND is_active=1 AND objecttype_id=10;
Empty set (0.01 sec)
Re: Finding hosts and services not in groups
Posted: Wed Aug 12, 2020 7:57 am
by scottwilkerson
ottow wrote:I am sorry but I just tested the above and it does not show the hosts or services I have 0 contact groups defined for.
MariaDB [nagios]> SELECT o.name1 as contact FROM nagios_objects o WHERE o.object_id NOT IN (SELECT contact_object_id from nagios_contactgroup_members) AND is_active=1 AND objecttype_id=10;
Empty set (0.01 sec)
Sorry I misread your question the above was for contacts not in a contact group... For the hosts and services with no Contact Groups defined you would use a different database to grab from the CCM.
use nagiosql;
Hosts with no contact groups
Code: Select all
SELECT host_name
FROM tbl_host, tbl_lnkHostToContactgroup
WHERE tbl_host.id NOT IN (SELECT tbl_lnkHostToContactgroup.idMaster from tbl_lnkHostToContactgroup)
GROUP BY host_name;
Services with no contact groups
Code: Select all
SELECT tbl_host.host_name, service_description
FROM tbl_host, tbl_service, tbl_lnkServiceToContactgroup, tbl_lnkServiceToHost
WHERE tbl_service.id NOT IN (SELECT tbl_lnkServiceToContactgroup.idMaster from tbl_lnkServiceToContactgroup)
AND tbl_service.id = tbl_lnkServiceToHost.idMaster
AND tbl_host.id = tbl_lnkServiceToHost.idSlave
GROUP BY tbl_host.host_name, service_description;
Re: Finding hosts and services not in groups
Posted: Wed Aug 12, 2020 8:00 am
by ottow
Excellent, this works as expected. Tank you very much!!
Re: Finding hosts and services not in groups
Posted: Wed Aug 12, 2020 8:02 am
by scottwilkerson
ottow wrote:Excellent, this works as expected. Tank you very much!!
No problem
Locking thread