Icinga
Filtering on servicegroup/hostgroup variable not working
Describe the bug
I have added a variable to the hostgroup/servicegroup in order to be able to filter only the hostgroups/servicegroups based on this variable.
To Reproduce
Add a variable to multiple hostgroups/servicegroups and filter on it.
- Example on one hostgroup
object HostGroup "hostgroup_linux" {
display_name = "Hostgroup - LINUX"
vars.var_hostgroup_linux="1" -> This variable for filtering the hostgroup
assign where host.vars.host_linux == "1"
}
- Filter on the hostgroup /icingaweb2/icingadb/hostgroups?hostgroup.vars.var_hostgroup_linux=1
Expected behavior
I only expect to see the hostgroups/servicegroups with the hostgroup/servicegroup-variable.
SQL code
See here the sql code :
SELECT hostgroup.hostgroup_id AS id, hostgroup.hostgroup_display_name AS display_name, (SUM(CASE WHEN host_state = 1 AND (host_handled = 'y' OR host_reachable = 'n') THEN 1 ELSE 0 END)) AS hosts_down_handled, (SUM(CASE WHEN host_state = 1 AND host_handled = 'n' AND host_reachable = 'y' THEN 1 ELSE 0 END)) AS hosts_down_unhandled, (SUM(CASE WHEN host_state = 99 THEN 1 ELSE 0 END)) AS hosts_pending, (SUM(CASE WHEN host_id IS NOT NULL THEN 1 ELSE 0 END)) AS hosts_total, (SUM(CASE WHEN host_state = 0 THEN 1 ELSE 0 END)) AS hosts_up, (MAX(host_severity)) AS hosts_severity, hostgroup.hostgroup_name AS name, (SUM(CASE WHEN service_state = 2 AND (service_handled = 'y' OR service_reachable = 'n') THEN 1 ELSE 0 END)) AS services_critical_handled, (SUM(CASE WHEN service_state = 2 AND service_handled = 'n' AND service_reachable = 'y' THEN 1 ELSE 0 END)) AS services_critical_unhandled, (SUM(CASE WHEN service_state = 0 THEN 1 ELSE 0 END)) AS services_ok, (SUM(CASE WHEN service_state = 99 THEN 1 ELSE 0 END)) AS services_pending, (SUM(CASE WHEN service_id IS NOT NULL THEN 1 ELSE 0 END)) AS services_total, (SUM(CASE WHEN service_state = 3 AND (service_handled = 'y' OR service_reachable = 'n') THEN 1 ELSE 0 END)) AS services_unknown_handled, (SUM(CASE WHEN service_state = 3 AND service_handled = 'n' AND service_reachable = 'y' THEN 1 ELSE 0 END)) AS services_unknown_unhandled, (SUM(CASE WHEN service_state = 1 AND (service_handled = 'y' OR service_reachable = 'n') THEN 1 ELSE 0 END)) AS services_warning_handled, (SUM(CASE WHEN service_state = 1 AND service_handled = 'n' AND service_reachable = 'y' THEN 1 ELSE 0 END)) AS services_warning_unhandled FROM ((SELECT host_state.is_handled AS host_handled, host.id AS host_id, host_state.is_reachable AS host_reachable, host_state.severity AS host_severity, host_state.soft_state AS host_state, host_hostgroup.display_name AS hostgroup_display_name, host_hostgroup.id AS hostgroup_id, host_hostgroup.name AS hostgroup_name, (NULL) AS service_handled, (NULL) AS service_id, (NULL) AS service_reachable, (NULL) AS service_state FROM host INNER JOIN hostgroup_member host_hostgroup_member ON host_hostgroup_member.host_id = host.id INNER JOIN hostgroup host_hostgroup ON host_hostgroup.id = host_hostgroup_member.hostgroup_id LEFT JOIN host_state ON host_state.host_id = host.id WHERE host.id IN ((SELECT sub_customvar_flat_hostgroup_host.id AS sub_customvar_flat_hostgroup_host_id FROM customvar_flat sub_customvar_flat INNER JOIN hostgroup_customvar sub_customvar_flat_hostgroup_customvar ON sub_customvar_flat_hostgroup_customvar.customvar_id = sub_customvar_flat.customvar_id INNER JOIN hostgroup sub_customvar_flat_hostgroup ON sub_customvar_flat_hostgroup.id = sub_customvar_flat_hostgroup_customvar.hostgroup_id INNER JOIN hostgroup_member sub_customvar_flat_hostgroup_hostgroup_member ON sub_customvar_flat_hostgroup_hostgroup_member.hostgroup_id = sub_customvar_flat_hostgroup.id INNER JOIN host sub_customvar_flat_hostgroup_host ON sub_customvar_flat_hostgroup_host.id = sub_customvar_flat_hostgroup_hostgroup_member.host_id WHERE (sub_customvar_flat.flatname = 'var_hostgroup_linux') AND (sub_customvar_flat.flatvalue = '1')))) UNION ALL (SELECT (NULL) AS host_handled, (NULL) AS host_id, (NULL) AS host_reachable, (0) AS host_severity, (NULL) AS host_state, service_hostgroup.display_name AS hostgroup_display_name, service_hostgroup.id AS hostgroup_id, service_hostgroup.name AS hostgroup_name, service_state.is_handled AS service_handled, service.id AS service_id, service_state.is_reachable AS service_reachable, service_state.soft_state AS service_state FROM service INNER JOIN hostgroup_member service_hostgroup_member ON service_hostgroup_member.host_id = service.host_id INNER JOIN hostgroup service_hostgroup ON service_hostgroup.id = service_hostgroup_member.hostgroup_id LEFT JOIN service_state ON service_state.service_id = service.id WHERE service.id IN ((SELECT sub_customvar_flat_hostgroup_service.id AS sub_customvar_flat_hostgroup_service_id FROM customvar_flat sub_customvar_flat INNER JOIN hostgroup_customvar sub_customvar_flat_hostgroup_customvar ON sub_customvar_flat_hostgroup_customvar.customvar_id = sub_customvar_flat.customvar_id INNER JOIN hostgroup sub_customvar_flat_hostgroup ON sub_customvar_flat_hostgroup.id = sub_customvar_flat_hostgroup_customvar.hostgroup_id INNER JOIN hostgroup_member sub_customvar_flat_hostgroup_hostgroup_member ON sub_customvar_flat_hostgroup_hostgroup_member.hostgroup_id = sub_customvar_flat_hostgroup.id INNER JOIN service sub_customvar_flat_hostgroup_service ON sub_customvar_flat_hostgroup_service.host_id = sub_customvar_flat_hostgroup_hostgroup_member.host_id WHERE (sub_customvar_flat.flatname = 'var_hostgroup_linux') AND (sub_customvar_flat.flatvalue = '1')))) UNION ALL (SELECT (NULL) AS host_handled, (NULL) AS host_id, (NULL) AS host_reachable, (0) AS host_severity, (NULL) AS host_state, hostgroup.display_name AS hostgroup_display_name, hostgroup.id AS hostgroup_id, hostgroup.name AS hostgroup_name, (NULL) AS service_handled, (NULL) AS service_id, (NULL) AS service_reachable, (NULL) AS service_state FROM hostgroup WHERE hostgroup.id IN ((SELECT sub_customvar_flat_hostgroup.id AS sub_customvar_flat_hostgroup_id FROM customvar_flat sub_customvar_flat INNER JOIN hostgroup_customvar sub_customvar_flat_hostgroup_customvar ON sub_customvar_flat_hostgroup_customvar.customvar_id = sub_customvar_flat.customvar_id INNER JOIN hostgroup sub_customvar_flat_hostgroup ON sub_customvar_flat_hostgroup.id = sub_customvar_flat_hostgroup_customvar.hostgroup_id WHERE (sub_customvar_flat.flatname = 'var_hostgroup_linux') AND (sub_customvar_flat.flatvalue = '1'))))) hostgroup GROUP BY hostgroup.hostgroup_id, hostgroup.hostgroup_display_name, hostgroup.hostgroup_name ORDER BY display_name LIMIT 25
SQL investigation
When I parse the sql code I see that it correctly does the filtering of the host, service and hostgroup for the hostgroup-variable. But a host can be in several hostgroups and so it shows ALL the hostgroups at the end.
What it doesn't do for me is at the end filtering only the hostgroups/servicegroups that have the hostgroup/servicegroup-variable.
Your Environment
See here the versions i running -> latest snapshot on 2023-07-29 06:17 for centos 7
icingadb-redis-devel-7.0.5-0.20230728.1804.el7.icinga.x86_64 icinga2-bin-2.14.0+8.ge3cca711e-1689372210.el7.x86_64 icinga2-2.14.0+8.ge3cca711e-1689372210.el7.x86_64 vim-icinga2-2.14.0+8.ge3cca711e-1689372210.el7.x86_64 icinga2-common-2.14.0+8.ge3cca711e-1689372210.el7.x86_64 icinga2-ido-pgsql-2.14.0+8.ge3cca711e-1689372210.el7.x86_64
icingadb-1.1.0.4.g4ed4db3-0.20230120.2003.el7.icinga.x86_64 icingadb-redis-7.0.5-0.20230728.1804.el7.icinga.x86_64 icingadb-redis-debuginfo-7.0.5-0.20230728.1804.el7.icinga.x86_64
icingadb-web-1.0.2+142.gcd5f74b-1690585492.el7.noarch
icingaweb2-common-2.11.4+124.g4ccebb78b-1690581852.el7.noarch icingaweb2-vendor-Parsedown-2.11.4+124.g4ccebb78b-1690581852.el7.noarch icingaweb2-vendor-lessphp-2.11.4+124.g4ccebb78b-1690581852.el7.noarch icinga-cube-1.3.1+0.gf259240-1690585483.el7.noarch icinga-php-thirdparty-0.11.0+14.g675dacd-1690578207.el7.noarch icingaweb2-vendor-JShrink-2.11.4+124.g4ccebb78b-1690581852.el7.noarch icingacli-2.11.4+124.g4ccebb78b-1690581852.el7.noarch icinga-php-common-1.0.0165655404400000000-0.20220630.0154.el7.icinga.noarch icingaweb2-vendor-zf1-2.11.4+124.g4ccebb78b-1690581852.el7.noarch icingaweb2-vendor-HTMLPurifier-2.11.4+124.g4ccebb78b-1690581852.el7.noarch icingaweb2-2.11.4+124.g4ccebb78b-1690581852.el7.noarch icinga-l10n-1.2.0+125.g94eb306-1690578225.el7.noarch icingaweb2-vendor-dompdf-2.11.4+124.g4ccebb78b-1690581852.el7.noarch icinga-cube-web-1.3.1+0.gf259240-1690585483.el7.noarch icinga-php-library-0.12.0+3.g84cc277-1690578243.el7.noarch php-icinga-2.11.4+124.g4ccebb78b-1690581852.el7.noarch
regards, Geert
I think this where should be added to show only the hostgroup for which the hostgroup-variable was foreseen.
)
hostgroup
where
hostgroup.hostgroup_id in
(
select
sub_customvar_flat_hostgroup.id as sub_customvar_flat_hostgroup_id
from
customvar_flat sub_customvar_flat
inner join hostgroup_customvar sub_customvar_flat_hostgroup_customvar on
sub_customvar_flat_hostgroup_customvar.customvar_id = sub_customvar_flat.customvar_id
inner join hostgroup sub_customvar_flat_hostgroup on
sub_customvar_flat_hostgroup.id = sub_customvar_flat_hostgroup_customvar.hostgroup_id
where
(sub_customvar_flat.flatname = 'var_hostgroup_linux')
and (sub_customvar_flat.flatvalue = '1')
)
group by
hostgroup.hostgroup_id,
hostgroup.hostgroup_display_name,
hostgroup.hostgroup_name
```
I am currently testing your solution as you provided in case 978 (GitHub version 7e6d1bd). This solution works very well for me, including at the level of index searches in the database.
I just have a question regarding this case. Your filtering works well at the level of host/service/hostgroup, but for a hostgroup variable, the same filter should also be applied to the end result.
While both are custom variable fitlers, this isn't affected by the fix for hosts and services. Thus this is not supposed to work due to that.
Hello,
The next one planned is a major release.
For this case, modifications to the icinga-php-library might also be necessary.
Could you consider including this issue in the 1.2.0 release as well?
This could significantly reduce the number of host/service groups for us.
Regards, Geert