# Filter out client.address in WHERE **URL:** \ **Category:** Elasticsearch\ **Tags:** esql\ **Created:** [March 11, 2025, 5:08pm UTC](https://discuss.elastic.co/t/filter-out-client-address-in-where/375727 "2025-03-11T17:08:19Z")\ **Posts on this page:** 9\ **Page:** 1
**Author:** ![JSElasticDiscuss](https://avatars.discourse-cdn.com/v4/letter/j/3bc359/32.png) [@JSElasticDiscuss](https://discuss.elastic.co/u/JSElasticDiscuss)\ **Post date:** [March 11, 2025, 5:08pm UTC](https://discuss.elastic.co/t/filter-out-client-address-in-where/375727/1 "2025-03-11T17:08:19Z")
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. ---
**Author:** ![RainTown](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/raintown/32/140206_2.png) [@RainTown](https://discuss.elastic.co/u/RainTown)\ **Post date:** [March 11, 2025, 6:32pm UTC](https://discuss.elastic.co/t/filter-out-client-address-in-where/375727/2 "2025-03-11T18:32:15Z")
> [@JSElasticDiscuss](#): > > using the client.address field as a keyword 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. ---
**Author:** ![JSElasticDiscuss](https://avatars.discourse-cdn.com/v4/letter/j/3bc359/32.png) [@JSElasticDiscuss](https://discuss.elastic.co/u/JSElasticDiscuss)\ **Post date:** [March 11, 2025, 8:15pm UTC](https://discuss.elastic.co/t/filter-out-client-address-in-where/375727/3 "2025-03-11T20:15:21Z")
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. ---
**Author:** ![leandrojmp](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/leandrojmp/32/107231_2.png) [@leandrojmp](https://discuss.elastic.co/u/leandrojmp)\ **Post date:** [March 11, 2025, 8:28pm UTC](https://discuss.elastic.co/t/filter-out-client-address-in-where/375727/4 "2025-03-11T20:28:33Z")
Have you tried to use the `TO_IP` function like the example in the [documentation](https://www.elastic.co/guide/en/elasticsearch/reference/current/esql-functions-operators.html#esql-to_ip)? Something like this, I think: ```auto EVAL ip1 = TO_IP(client.address) ``` ---
**Author:** ![RainTown](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/raintown/32/140206_2.png) [@RainTown](https://discuss.elastic.co/u/RainTown)\ **Post date:** [March 11, 2025, 8:33pm UTC](https://discuss.elastic.co/t/filter-out-client-address-in-where/375727/5 "2025-03-11T20:33:19Z")
What version are you on? > **[ES|QL functions and operators | Elasticsearch Guide \[8.17\] | Elastic](https://www.elastic.co/guide/en/elasticsearch/reference/current/esql-functions-operators.html)** 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](https://us1.discourse-cdn.com/elastic/original/3X/a/0/a099a457fbc360a5a75e132f3299228279b4a54d.jpeg) ---
**Author:** ![JSElasticDiscuss](https://avatars.discourse-cdn.com/v4/letter/j/3bc359/32.png) [@JSElasticDiscuss](https://discuss.elastic.co/u/JSElasticDiscuss)\ **Post date:** [March 11, 2025, 8:41pm UTC](https://discuss.elastic.co/t/filter-out-client-address-in-where/375727/6 "2025-03-11T20:41:16Z")
I have not tried this one, but I will give it a shot -- Thank you! ---
**Author:** ![RainTown](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/raintown/32/140206_2.png) [@RainTown](https://discuss.elastic.co/u/RainTown)\ **Post date:** [March 11, 2025, 8:43pm UTC](https://discuss.elastic.co/t/filter-out-client-address-in-where/375727/7 "2025-03-11T20:43:19Z")
and there's also the helpful CIDR\_MATCH function (also in ES|QL) > **[ES|QL examples | Elasticsearch Guide \[8.17\] | Elastic](https://www.elastic.co/guide/en/elasticsearch/reference/current/esql-examples.html)** ---
**Author:** ![JSElasticDiscuss](https://avatars.discourse-cdn.com/v4/letter/j/3bc359/32.png) [@JSElasticDiscuss](https://discuss.elastic.co/u/JSElasticDiscuss)\ **Post date:** [March 11, 2025, 8:43pm UTC](https://discuss.elastic.co/t/filter-out-client-address-in-where/375727/8 "2025-03-11T20:43:56Z")
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. ---
**Author:** ![JSElasticDiscuss](https://avatars.discourse-cdn.com/v4/letter/j/3bc359/32.png) [@JSElasticDiscuss](https://discuss.elastic.co/u/JSElasticDiscuss)\ **Post date:** [March 13, 2025, 5:11pm UTC](https://discuss.elastic.co/t/filter-out-client-address-in-where/375727/9 "2025-03-13T17:11:02Z")
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.