SQL select to print all host names in Icinga2 hostgroup

Question

select alias from icinga_hosts; prints all the host names in every hostgroup.

select alias from icinga_hostgroups; prints all the hostgroups.

I can't see how to "select select alias from icinga_hosts where icinga_hostgroups is "customer0";

In other words, 'print all the hostnames in hostgroup customer0'.

Do I need a join of some sort? This is MariaDB 5.5. Thanks for any advice.

MariaDB [icinga]> show fields in icinga_hostgroups;
+---------------------+---------------------+------+-----+---------+----------------+
| Field               | Type                | Null | Key | Default | Extra          |
+---------------------+---------------------+------+-----+---------+----------------+
| hostgroup_id        | bigint(20) unsigned | NO   | PRI | NULL    | auto_increment |
| instance_id         | bigint(20) unsigned | YES  | MUL | 0       |                |
| config_type         | smallint(6)         | YES  |     | 0       |                |
| hostgroup_object_id | bigint(20) unsigned | YES  |     | 0       |                |
| alias               | varchar(255)        | YES  |     |         |                |
| notes               | text                | YES  |     | NULL    |                |
| notes_url           | text                | YES  |     | NULL    |                |
| action_url          | text                | YES  |     | NULL    |                |
| config_hash         | varchar(64)         | YES  |     | NULL    |                |
+---------------------+---------------------+------+-----+---------+----------------+
9 rows in set (0.00 sec)

show fields in icinga_hosts;
+-----------------------------------+---------------------+------+-----+---------+----------------+
| Field                             | Type                | Null | Key | Default | Extra          |
+-----------------------------------+---------------------+------+-----+---------+----------------+
| host_id                           | bigint(20) unsigned | NO   | PRI | NULL    | auto_increment |
| instance_id                       | bigint(20) unsigned | YES  | MUL | 0       |                |
| config_type                       | smallint(6)         | YES  |     | 0       |                |
| host_object_id                    | bigint(20) unsigned | YES  | MUL | 0       |                |
| alias                             | varchar(255)        | YES  |     |         |                |
| display_name                      | varchar(255)        | YES  |     |         |                |
| address                           | varchar(128)        | YES  |     |         |                |

Yes, you can use a `join` of some sort. What have you tried? — Gordon Linoff, Feb 16 '18 at 03:25
icinga_hosts has 60 fields. I'm only interested in the `alias` field. — mr.zog, Feb 16 '18 at 15:32
select * from icinga_hostgroups where alias = 'shellcorp'; does find the shellcorp record. That's as far as I got last night. — mr.zog, Feb 16 '18 at 15:43
From the partial list of fields in `icinga_hosts` that you've pasted it's not obvious how `icinga_hosts` relates to `icinga_hostgroups`. Obviously, there should be a `hostgroup_id` somewhere in `icinga_hosts`. That's what you will be joining on, or using in a subquery if you so prefer. — elenst, Feb 17 '18 at 22:50
elenst, there is no hostgroup_id in icinga_hosts. hostgroup_id is in icinga_hostgroups only. — mr.zog, Feb 18 '18 at 00:13

cflinspach · Answer 1 · 2018-02-20T16:25:13.827

0

You need the icinga_hostgroup_members table like:

SELECT groups.alias AS 'Group',
  hosts.alias AS Host
FROM icinga_hosts AS hosts 
JOIN icinga_hostgroup_members AS group_members     
  ON hosts.host_object_id = group_members.host_object_id 
JOIN icinga_hostgroups AS groups     
  ON group_members.hostgroup_id = groups.hostgroup_id 
WHERE groups.alias = 'customer0';

edited Feb 20 '18 at 16:25

answered Feb 18 '18 at 22:10

cflinspach

290
1
4
16

I updated it, it should work now. the column header just needed single quotes. – cflinspach Feb 20 '18 at 16:20

score 0 · Accepted Answer · answered Feb 19 '18 at 20:03

select oh.name1 as host_name, ohg.name1 as hostgroup_name

from icinga_hosts h

join icinga_objects oh on h.host_object_id=oh.object_id

join icinga_hostgroup_members hgm on hgm.host_object_id=h.host_object_id

join icinga_hostgroups hg on hg.hostgroup_id=hgm.hostgroup_id

join icinga_objects ohg on hg.hostgroup_object_id=ohg.object_id

where ohg.name1='linux-servers';

SQL select to print all host names in Icinga2 hostgroup

2 Answers2