SQL query for Jasper Reports

My users want to generate a report “all hosts monitored by Icinga” for each customer. So I got JasperReports working perfectly with Icinga Reports but now I’m being told all of the reports have too much data in them. Long story short, I need to create a custom report that generates a simple list of every host in a particular zone.
select alias from icinga_hosts; finds all the hosts and
select alias from icinga_hostgroups; finds all the hostgroups (zones) but I’m having trouble with writing a join query of these two. Or perhaps I’m using the wrong fields.

Or perhaps I should use hostgroup_id instead of alias for the hostgroup . . .

Hosts are linked to hostgroups via the hostgroup_members table in order to provide the m:n relation.
In addition to that, none of the tables above actually holds the object name, that’s again to be joined against the objects table. Once for the host, then later for the hostgroup.

MariaDB [icinga]> 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';
+-----------------------------+----------------+
| host_name                   | hostgroup_name |
+-----------------------------+----------------+
| 3998-host                   | linux-servers  |
| 4832-host                   | linux-servers  |
| 5039-host                   | linux-servers  |
| action                      | linux-servers  |
| bla                         | linux-servers  |
| crit-os-version             | linux-servers  |
| dummy-host                  | linux-servers  |
| flapping                    | linux-servers  |
| foo                         | linux-servers  |
| host-without-service        | linux-servers  |
| icinga2-client1.localdomain | linux-servers  |
| icinga2-client2.localdomain | linux-servers  |
| if-host                     | linux-servers  |
| mbmif.int.netways.de        | linux-servers  |
| ok-os-version               | linux-servers  |
| ttl-host                    | linux-servers  |
| warn-os-version             | linux-servers  |
| webserver-12                | linux-servers  |
| xmas                        | linux-servers  |
| 5444-host                   | linux-servers  |
| many-test-0                 | linux-servers  |
| many-test-1                 | linux-servers  |
| many-test-2                 | linux-servers  |
| many-test-3                 | linux-servers  |
| many-test-4                 | linux-servers  |
| many-test-5                 | linux-servers  |
| many-test-6                 | linux-servers  |
| many-test-7                 | linux-servers  |
| many-test-8                 | linux-servers  |
| many-test-9                 | linux-servers  |
| perm-host                   | linux-servers  |
+-----------------------------+----------------+
31 rows in set (0.00 sec)

For better readability and to follow my thinking (I wrote that query entirely from brain):

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

Thanks, michi. That would have taken me all year to figure out.

1 Like

I figured out how to add the IP address. I changed the first line to read,

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

Now I need to figure out how to not have the hostgroup_name printed as part of the result.