Find hosts not assigned to hostgroup

[AngeloMileto]

Is there a way to list all hosts that have no assignment to any hostgroup? I've seen a few posts about using sql queries but when I try to get to the db I keep getting permission denied errors. I inherited this system so if there should have been a db user/password at setup, I don't know it.

I'm thinking I could write a bash script to capture all of the hosts and then do a relatively simple looking to see if they exist in at least one hostgroup but I'm also thinking that this could grow to a recurring thing to list all of the hostgroups with their associated hosts for review.

Thanks!!

[lgute]

Hi @AngeloMileto, thanks for reaching out.

You should be able to find the passwords, including mysqlpass, etc., in /usr/local/nagiosxi/var/xi-sys.cfg or /usr/local/nagiosxi/etc/xi-sys.cfg.

[AngeloMileto]

I had found that and it looks like it's still the default but still got permission denied. Maybe I can't do that as the nagios user?

[cnorell]

AngeloMileto,

I would try the users "nagiosadmin" and "nagiosxi". I'll dig around a bit to see if there are other users you may have to use; I have always used one of the aforementioned.

Best Regards,

Cory Norell

[lgute]

Hi @AngeloMileto,

The value mysqlpass is for use with mysql so you can query and manage the databases, if you need to. I think the query you are looking for would be something like this.

Code: Select all

mysql --user=root --password=<mysqlpass> --execute="select id as missing_host_id from nagiosql.tbl_host where id not in (select distinct idSlave as id from nagiosql.tbl_lnkHostgroupToHost as link, nagiosql.tbl_hostgroup as hostgroup where link.idMaster = hostgroup.id);"

I would recommend creating a read-only sql user, if you want to run queries like this frequently.