Finding hosts and services not in groups

[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

[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

Code: Select all

mysql -pnagiosxi nagios

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;

[ottow]

Thanks!

Could you please show how to find hosts and services with no Contact Groups defined too?

[scottwilkerson]

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;

[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)

[scottwilkerson]

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;

[ottow]

Excellent, this works as expected. Tank you very much!!

[scottwilkerson]

Excellent, this works as expected. Tank you very much!!

No problem

Locking thread