Page 1 of 1
Can you query the Nagios database for host "use template"
Posted: Mon Jan 31, 2022 3:28 pm
by onegative
G' Day Support,
I am sniffing through the nagios database to try and determine what host template(s) is/are assigned to the host.
I'm not finding it...can anyone point me to which table has the host template(s) assigned to the host record?
How is the database re-creating the host record?
Many thanks,
Danny
Re: Can you query the Nagios database for host "use template
Posted: Tue Feb 01, 2022 2:45 pm
by pbroste
Hello
@onegative
Thanks for following up, these will show you links between hosts and hosttemplate ids found in the nagiosql database. The ids can then be linked to ids found in tbl_host and tbl_hosttemplate.
echo "select * from tbl_lnkHosttemplateToHost;" | mysql -uroot -pnagiosxi -Dnagiosql
echo "select * from tbl_lnkHostToHosttemplate;" | mysql -uroot -pnagiosxi -Dnagiosql
Option to read from the Core Configuration found in '/usr/local/nagios/etc/....'
Thanks,
Perry
Re: Can you query the Nagios database for host "use template
Posted: Tue Feb 01, 2022 4:58 pm
by onegative
Happy Tuesday Perry,
So that helped...I was focused on the nagios database and of course missing the idea it was spread across databases.
So I was able to pull exactly what I needed.
Here is my solution in case anyone else wants to know quickly...especially when you have thousands of hosts.
Code: Select all
select
th.host_name AS host,
th.address AS ipAddr,
h2t.template_name AS templateName
FROM
nagiosql.tbl_host AS th
JOIN nagiosql.tbl_lnkHostToHosttemplate h2ht ON th.id=h2ht.idMaster
JOIN nagiosql.tbl_hosttemplate h2t ON h2ht.idSlave=h2t.id
;
Thanks for the help,
Danny
Re: Can you query the Nagios database for host "use template
Posted: Wed Feb 02, 2022 12:21 pm
by onegative
Further extension of the script.
Now I can run a report where it returns the hostname, ipAddr, hostTemplate(s) and Host Group(s).
I have a duplicate hostname column...but I will correct that latter.
Just in case anyone is interested...
Code: Select all
SELECT *
FROM ( SELECT
no.name1 AS hostgroup_name,
nh.display_name AS host
from nagios_objects AS no
JOIN nagios_hostgroups nhg ON no.object_id=nhg.hostgroup_object_id
JOIN nagios_hostgroup_members nhm ON nhg.hostgroup_id=nhm.hostgroup_id
JOIN nagios_hosts nh ON nhm.host_object_id=nh.host_object_id
)
AS A
JOIN ( select
h2t.template_name AS templateName,
th.host_name as hostname
FROM
nagiosql.tbl_host AS th
JOIN nagiosql.tbl_lnkHostToHosttemplate h2ht ON th.id=h2ht.idMaster
JOIN nagiosql.tbl_hosttemplate h2t ON h2ht.idSlave=h2t.id
)
AS B
ON A.host=B.hostname
Re: Can you query the Nagios database for host "use template
Posted: Wed Feb 02, 2022 2:00 pm
by pbroste
Hello
@onegative; thanks for sharing the details with everyone. This will definitely help others.
I will go ahead and lock this on up.
Thanks,
Perry