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 | | | |
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';