SQL query for hosts and custom variables

Icinga 2
1

Hello,

i am willing to query all hosts joined with a specific custom variable (“guest_fullname”).

But the join does not work since instance_id are not equal on tables icinga_hosts and icinga_customvariables.

Could you help me out on this query?
Is there a actual database schema available?
The only one I could find is this one: https://fossies.org/linux/misc/old/icinga-core-1.14.2.tar.gz/icinga-core-1.14.2/html/docs/images/db_model_ht.png

SELECT icinga_hosts.instance_id,address,display_name,varvalue FROM icinga_hosts JOIN icinga_customvariables IC ON IC.instance_id = icinga_hosts.instance_id WHERE varname LIKE 'guest_fullname'
2 
select * from icinga_customvariables left outer join icinga_objects using(object_id) where varname like "guest_fullname" and name2 is NULL;

but my instance_ids do match, but you do not join by object_id

3

SELECT icinga_hosts.instance_id,address,display_name,varvalue FROM icinga_hosts JOIN icinga_customvariables IC ON IC.object_id = icinga_hosts.host_object_id and IC.instance_id = icinga_hosts.instance_id WHERE varname LIKE 'guest_fullname'

1 Like
4

I am thinking you are using icingaweb2, too?
But did you try to use this sql query with “addColumns” in the URL?
You may remove unnecessary columns from the query that you get out of the URL :slight_smile:

https://icinga-server/icingaweb2/monitoring/list/hosts?addColumns=_host_guest_fullname&format=sql

or with filter on guest_fullname

https://icinga-server/icingaweb2/monitoring/list/hosts?_host_citycode=asdf&addColumns=_host_guest_fullname&modifyFilter=1&format=sql

2 Likes