How to Query Zabbix to Show Host-Template Relationships?

Query to Display Host-Template Relationships in Zabbix

This query helps retrieve data from Zabbix tables to show which hosts utilize specific templates. The challenge lies in the fact that both hosts and templates are stored in the same table, intermixed with IDs like 11813 for hosts and 11815 for templates.

To address this, we introduce the hosts_templates table, which establishes the connection between hosts and templates through its three columns: host_template ID, hostid, and templateid.

The hosts table includes crucial fields like hostid and name. Although the hosts table possesses a templateid column, it is not used.

In the hosts_templates table, we can determine which hosts use which templates. However, the challenge arises when we need to translate IDs into corresponding names.

Previous Attempts

The following initial queries aimed to provide partial solutions but encountered duplication issues:

select name, name
  from hosts_templates
 inner join hosts on hosts_templates.hostid = hosts.hostid;

select name, name
  from hosts_templates
 inner join hosts on hosts_templates.templateid = hosts.hostid;

Solution

The solution requires two joins, each with different table aliases:

SELECT h1.name as host_name, h2.name AS template_name
FROM hosts_template AS t
JOIN hosts AS h1 ON t.hostid = h1.hostid
JOIN hosts AS h2 ON t.hosttemplateid = h2.hostid

In this query, the hosts_template table is aliased as t, while the hosts table is aliased as h1 and h2 to differentiate between host names and template names.

The above is the detailed content of How to Query Zabbix to Show Host-Template Relationships?. For more information, please follow other related articles on the PHP Chinese website!