[Q]: Is there a 'LIKE' function in ES|QL?

Elastic Search
1

So I have som data in filebeat where the following query will return some data:

FROM filebeat-*
| WHERE event.dataset == "cert-info.log"
| KEEP tags
| LIMIT 2000

this results in something like this:

Certificate
[Private key, issuer_missing, subject_missing]
[Private key, issuer_missing]

What I would like to do is only get the rows where tags contain the phrase Private key. I have tried adding the following WHERE clauses one by one but even though I dont get any errors they all result in no hits at all:

WHERE tags LIKE "*Private key*"
WHERE tags LIKE "%Private key%"
EVAL t = LOCATE(tags, "Private key") | WHERE t > 0

Is it possible to succeed in what I'm trying to do or does ES|QL simply not support such a relatively basic feature?

Thanks

2

I do not know whether ES|QL supports this or not, so will need to let someone else respond to that.

If you were to translate this type of query into Elasticsearch query clauses, which is what I believe ES/QL does behind the scenes, it would result in a wildcard query with leading and trailing wildcards. This is as far as I know by far the most inefficient query you can run in Elasticsearch and it performs and scales very badly with increasing data volumes. It is generally recommended to avoid this at all cost, at least if it has to be run against a field mapped as keyword and not wildcard If this type of query is not supported by ES|QL I would expect this to be the reason for it.

3

@cjessing

Perhaps review the docs

LIKE

RLIKE

Actually it is mostly a new execution framework and specifically does not translate to DSL.:).

But the actual issue is tags is an array so it needs to be expanded first

You can use MV_EXPAND

FROM logs-* 
| MV_EXPAND tags
| where tags == "Private key" 
| LIMIT 10
1 Like
4

Even if it does not directly translate to DSL I do not see how it could execute this type of logic in a much more efficient way against Lucene. If that was possible, would the wildcard query clause not have been improved as well? Has there been a major breakthrough in efficiency?

1 Like
5

MV_EXPAND!!! That was it! You're a life saver. Thanks a million!

1 Like
6

Actually... to elaborate a bit... what if I want ONE hit if two conditions are met?

I would like 3 seperate counts for

tags == "Private key" (and nothing else)
tags == [Private key, issuer_missing] (no more, no less)
tags == [Private key, issuer_missing, subject_missing] (no more, no less)

I realize I have to do seperate queries but thats okay.

7

I think the following query will be a good place to start...

FROM filebeat-*
| EVAL t = MV_CONCAT(tags, ", ")
| WHERE event.dataset == "cert-info.log"
| WHERE (LOCATE(t, "Private key") > 0 )
| KEEP t
| LIMIT 100