How to check which templates are used by each/all hosts

[greavette]

Hello,

We have Zabbix 5 LTS installed monitoring around 50 hosts. I'd like to know if there is a command or way to list or report all my current hosts and the templates each host has assigned.

Thank you.

[vladimir_lv]

The same query shows a linked template. of course, a little bit is uncomfortable that the parent template is shown in the host column. Add %% to '{#'; to avoid of macros

SELECT s.hostid,h1.name as host,s.name as template from hosts as h1 LEFT JOIN (select ht.hostid,hs.name from hosts_templates as ht inner join hosts as hs on ht.templateid=hs.hostid) as s on s.hostid=h1.hostid where s.name is not Null and h1.name not like '%{#%';

[vladimir_lv]

I understand that somewhere in the world exists much better way, but this one work as well

SELECT s.hostid,h1.name as host,s.name as template from hosts as h1 LEFT JOIN (select ht.hostid,hs.name from hosts_templates as ht inner join hosts as hs on ht.templateid=hs.hostid) as s on s.hostid=h1.hostid where s.name is not Null and h1.name not like '{#';

[greavette]

Hello Vladmir, really appreciate your reply...thanks!

I've run your select statement and this does appear to give me what I need! I've noticed that some templates are linked. is there something that can be added to this select statement you provided that will also show templates used and any linked templates as well?

Thank you.

[vladimir_lv]

The same query shows a linked template. of course, a little bit is uncomfortable that the parent template is shown in the host column. Add %% to '{#'; to avoid of macros

SELECT s.hostid,h1.name as host,s.name as template from hosts as h1 LEFT JOIN (select ht.hostid,hs.name from hosts_templates as ht inner join hosts as hs on ht.templateid=hs.hostid) as s on s.hostid=h1.hostid where s.name is not Null and h1.name not like '%{#%';

[greavette]

The same query shows a linked template. of course, a little bit is uncomfortable that the parent template is shown in the host column. Add %% to '{#'; to avoid of macros

SELECT s.hostid,h1.name as host,s.name as template from hosts as h1 LEFT JOIN (select ht.hostid,hs.name from hosts_templates as ht inner join hosts as hs on ht.templateid=hs.hostid) as s on s.hostid=h1.hostid where s.name is not Null and h1.name not like '%{#%';

Thanks very much Vladmir for your help!