ESQL: function/operator to check if any value of a multivalue field matches

[alex-spies]

Description

There are use cases where we want to check if a multvalued field e.g. contains a particular value. Currently, the == operator does not support this as a multivalue for either of the operands results in a null (mentioned in the docs, too).

One workaround is to use MV_EXPAND, but this changes the number of rows and loses information about which values were once part of the same multvalue. This was, for instance, not adequate for (this SIEM case).

What we probably need is something like an any_equals function (and all_equals, most likely) - or some sort of ANY/ALL modifiers, e.g. WHERE ANY mv_field == "single_value". (The exact semantics of ANY/ALL are unclear in case we compose multiple operators/functions, though.)

[elasticsearchmachine]

Pinging @elastic/es-analytical-engine (Team:Analytics)

[Gelios7]

Hi @alex-spies, please tell me if there is any quality improvement on this problem, because it is very inconvenient to use very complex operations in ESQL such as "LOCATE"?

Thank you.

[alex-spies]

I'm sorry @Gelios7 , I'm not aware of any improvements that have been made to make applying a function to any/all elements of a multivalue so far; but we do recognize this is an important enhancement for use cases relying on multi-valued data.

Do I understand correctly that you'd like to check if any element of a multivalue contains a substring? And using MV_EXPAND + LOCATE is cumbersome, or doesn't solve your use case?

[Gelios7]

@alex-spies,
You have to use quite a few transformations to exclude the corresponding role from the filter that the user has.
here is a part of the working code that worked for me:

| EVAL user.target.role_name = MV_DEDUPE (user.target.role_name)
| EVAL rolename_concat = MV_CONCAT (user.target.role_name, ", ")
| EVAL is_rolename = LOCATE(rolename_concat, "Локальный администратор на выделенном ПК")
| EVAL is_rolename2 = LOCATE(rolename_concat, "Адміністратор серверу з ОС WIndows")
| EVAL is_rolename3 = LOCATE(rolename_concat, "Локальный администратор всех ПК в домене")
| EVAL is_rolename4 = LOCATE(rolename_concat, "Региональный администратор домена")
| EVAL is_rolename5 = LOCATE(rolename_concat, "Администратор")
| WHERE (user.target.name LIKE "pr1-" AND is_rolename == 0) OR ((user.target.name LIKE "pr2-" OR user.target.name LIKE "pr3-" OR user.target.name LIKE "pr4-") AND is_rolename2 == 0) OR (user.target.name LIKE "pr5-" AND is_rolename3 == 0 AND is_rolename4 == 0) OR (user.target.name LIKE "pr6-" AND is_rolename5 == 0) OR user.target.role_name is null

This construct is not very convenient to use in conjunction with a simple "Where" filter to exclude values from a multivalued field.

[Gelios7]

Also, our "tags" field has multi-valued values, because of this we cannot use it normally in ESQL in the "Where" filter, for example, there is an attribute that this log refers to a server or workstation, and this is sad.

I will say more that the functionality of the "Where" filter should not differ from KQL, Lucene, EQL because random multi-valued values in the fields may not correctly display the data for search.

[alex-spies]

Thank you @Gelios7 for explaining the workaround that's required in your use case. I agree, this is a poor UX for dealing with multi-values.

[mjmbischoff]

Given #133016 is merged we can clean up a little bit:

| EVAL user.target.role_name = MV_DEDUPE (user.target.role_name)
| EVAL rolename_concat = MV_CONCAT (user.target.role_name, ", ")
| EVAL is_rolename = CONTAINS(rolename_concat, "Локальный администратор на выделенном ПК")
| EVAL is_rolename2 = CONTAINS(rolename_concat, "Адміністратор серверу з ОС WIndows")
| EVAL is_rolename3 = CONTAINS(rolename_concat, "Локальный администратор всех ПК в домене")
| EVAL is_rolename4 = CONTAINS(rolename_concat, "Региональный администратор домена")
| EVAL is_rolename5 = CONTAINS(rolename_concat, "Администратор")
| WHERE (user.target.name LIKE "pr1-" AND is_rolename) OR ((user.target.name LIKE "pr2-" OR user.target.name LIKE "pr3-" OR user.target.name LIKE "pr4-") AND is_rolename2) OR (user.target.name LIKE "pr5-" AND is_rolename3 AND is_rolename4) OR (user.target.name LIKE "pr6-" AND is_rolename5 ) OR user.target.role_name is null

Other than ugly the problem is that the separator might be in any of the values and it's not exact match:

"foo,fazz" contains "foo" -> T
"bar,fazz" contains "foo" -> F
"foobar,notfoo" contains "foo" -> T

You can match with the separator to anchor start and end bounds, you do need to add a start and end separator for the whole string CONCAT(separator, mv_concat_ouput, separator):

"#foo#fazz#" contains "#foo#" -> T
"#bar#fazz#" contains "#foo#" -> F
"#foobar#notfoo#" contains "#foo#" -> F

Which is all very user unfriendly.

---

But I'm not sure which syntax is ideal for the usecase:

MV_ANY(mv_field, value) function:

| WHERE (user.target.name LIKE "pr1-" AND MV_ANY(user.target.role_name, "Local admin")) 
OR (
   (user.target.name LIKE "pr2-" OR user.target.name LIKE "pr3-" OR user.target.name LIKE "pr4-") 
   AND MV_ANY(user.target.role_name, "Domain admin")
) 
OR user.target.role_name is null

Fits the whole MV functions approach, though multivalue is very native to ES, still feels weird having special cases for it.

A FLATTEN modifier which which emit the multivalue field as inline values or ANY, ALL modifiers to modify the way the left or right hand is interpreted can easily lead to unforeseen permutations.

An boolean operator which behaves differently from the standard is already part of spec: CIEQ : =~ (case insensitive equals) Though here we can take the same approach as the CAST operator :: where we can add the operator as a shorthand. If we do that later, we can avoid bike-shedding over syntax right now (=={, ={, =* etc.)

Revising how we handle multi value and adjusting IN is an entirely different can of worms. Given we already have a set of MV_ functions we would have to migrate that anyways, so might as wel side step that right now as well.

[mjmbischoff]

Created a draft pull request: #133099 went with MV_CONTAINS_ALL over ANY because I wanted to allow for multi values on both sides.

[Gelios7]

The most normal way is to implement the normal operation of filters and functions with multifields in ESQL as it works in KQL. Then the query syntax will be simple and you will not have to build three-story queries.