Filtering services not working as expected

[sysadminsha]

Describe the bug

Hi

I came across this issue while filtering services by multiple conditions.

This is the query which I am using:
host.vars.application[*]~SAS*&host.vars.application[*]!=Oracle Database

While I get 60 hits for hosts, I retrieve an empty result for services using the same filter.

Now when I take the SQL queries which are generated by Icinga-DB and compare them to each other, there is a difference in the services' WHERE-clause. The service query has additional LEFT JOINs. I can finally get the correct result when I replace those with RIGHT-/INNER JOINs.

This is the simplified query which is generated by Icinga-DB:

And here the same query changed by me:

To Reproduce

1. Goto Overview -> Services
2. Apply a filter with at least 1 "AND"-condition which contains host variables

Expected behavior

To see the services behind those hosts which are not filtered out.

Screenshots

See above

Your Environment

Include as many relevant details about the environment you experienced the problem in.

• Icinga DB Web version (System - About): 1.1.1
• Icinga Web 2 version (System - About): 2.12.1
• Web browser: Chrome/Edge
• Icinga 2 version (icinga2 --version): r2.14.0-1
• Icinga DB version (icingadb --version): v1.1.1
• PHP version used (php --version): 7.4.33
• Server operating system and version: RHEL8

[nilmerg]

Hi,

seems to work fine for me without any changes:

object Host "with-fruits" {
    check_command = "dummy"
    check_interval = 5m
    retry_interval = 1m
    max_check_attempts = 5

    vars.dummy_state = 0
    vars.fruits = ["apple", "banana", "orange", "papaya"]
}

object Host "also-with-fruits" {
    check_command = "dummy"
    check_interval = 5m
    retry_interval = 1m
    max_check_attempts = 10

    vars.dummy_state = 0
    vars.fruits = ["banana", "apple", "orange"]
}

Please verify that the hosts you get after adjusting the query, really meet the requirements.

[nilmerg]

Eh, sorry, misread at first. But still, the service of that host is found:

[carraroj]

/ref/NC/806241

[sysadminsha]

Hi,

ok. I random picked 3 services from the result and I can verify that the adjusted query indeed meet the requirements.

And then I looked up the corresponding hosts

Finally I checked that the host objects match the filter criteria

[mj84]

I'm also seeing some strange behavior while trying to restrict access to hosts and services.

I'm running a freshly installed Icinga2 2.14.2, Icingaweb2 2.12.1, IcingaDB 1.1.1 and IcingaDB-Web 1.1.1 on Debian Bookworm with PHP 8.2.7.

We are also using Arrays in the Host objects which contain strings and based upon the presence of some strings, hosts (and their services) should be filtered out for some users, for example:

vars.tags = [
  "invisible" // used to hide objects for all non-admins
]

Querying hosts for those who do NOT contain a certain tag works fine:

When using the exact same query for services, I receive zero results:

The IcingaDB query in both cases is expanded to these URL parameters: ?host.vars.tags%5B%2A%5D!=invisible

[mj84]

On a side note:
I have experimented a bit with the queries and it seems to me, that the negating query for
host.vars.tags[*]=something (meaning: vars.tags contains "something")
should not be host.vars.tags[*]!=something but rather !(host.vars.tags[*]=something).

Also, I am seeing different behavior depending on the tag value I filter for.
The tag value "invisible" is problematic, but "invi_sible" is not, at least in my case using PostgreSQL.

Currently it seems to me that I receive the desired behaviour by using the form !(host.vars.tags[*]=some_value) and making sure to include an underscore in the value.

Sorry for spamming this issue, the findings mentioned previously in this post were just plain wrong.

[nilmerg]

@mj84 This is an already acknowledged bug: #865

@sysadminsha After some struggling and debugging, I've reproduced it. The catch was, that I needed a host that has the custom variable you don't want (Oracle Database) but without any services attached to it. This is directly related with #974 as it's caused by the same bug. The good news is, it's easy to fix, just not easy to find 😅