Page 1 of 1
Find hosts not assigned to hostgroup
Posted: Mon Oct 07, 2024 5:39 am
by 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!!
Re: Find hosts not assigned to hostgroup
Posted: Mon Oct 07, 2024 9:13 am
by 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.
Re: Find hosts not assigned to hostgroup
Posted: Mon Oct 07, 2024 9:44 am
by 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?
Re: Find hosts not assigned to hostgroup
Posted: Mon Oct 07, 2024 4:12 pm
by 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
Re: Find hosts not assigned to hostgroup
Posted: Tue Oct 08, 2024 12:18 pm
by 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.