Filter out client.address in WHERE

Elastic Stack Elasticsearch
1

I am trying to write an ES|QL query that filters out client.address based with a WHERE clause, using wildcards to get rid of internal IP ranges. We are currently using the client.address field as a keyword, and trying to form the query I have used:
WHERE client.address != "X.X.*"
WHERE client.address NOT IN ("X.X.*")
WHERE client.address NOT IN ("X.X.X.X/X")
WHERE client.address != "X.X.X.X/X"

None of the options seem to be working to reduce the internal IP address findings. From the documentation, keywords allow wildcard, so it should be working, but we also included the CIDR, even though it technically is not an IPADDR field.

2

therein lies your main issue. Its a keyword field, and a keyword is a keyword, not an IP address.

Look at using RLIKE and regexes.

3

Hi @RainTown -- That would be great because I already have a query that works with a regex, but I am trying to do this in ES|QL, which is completely different from the Query DSL language. There is no RLIKE or regex commands I can use, unless you are aware of something that I have not found in documentation available.

4

Have you tried to use the TO_IP function like the example in the documentation?

Something like this, I think:

EVAL ip1 = TO_IP(client.address)
5

What version are you on?

I should really have checked before replying, let me do that now ... I'm on 8.17.2

FROM test| WHERE field2 RLIKE "[t-z]*" | KEEP field2,field1

works for my test index

The TO_IP suggestion in meantime is even better.

Screenshot 2025-03-11 at 21.32.09
Screenshot 2025-03-11 at 21.32.09807×633 59.8 KB

6

I have not tried this one, but I will give it a shot -- Thank you!

7

and there's also the helpful CIDR_MATCH function (also in ES|QL)

8

I didn't mention it either, sorry about that -- We're currently on 8.16 and after checking out what I was sent, I found I was on the totally incorrect version of documentation.

I appreciate the responses here, maybe my face was just too close to the screen to see the options available. I will take a look at RLIKE, CIDR_MATCH, and the EVAL and see if I can get a working query.

2 Likes
9

Thanks for the suggestion @RainTown -- I ended up using WHERE NOT CIDR_MATCH(client.ip, "X.X.X.X/X") and that seems to have worked to filter out the results. Curiously enough, I used the WHERE NOT on IPv6 and it filtered it to only include IPv6, I had to change it to WHERE CIDR_MATCH(client.ip, "::/48") and that worked for me.

1 Like