Can you query the Nagios database for host "use template"

[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

[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

[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

[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

[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