# Cannot query ip address fields using elastic SQL

**URL:** <https://discuss.elastic.co/t/cannot-query-ip-address-fields-using-elastic-sql/249038>\
**Category:** Kibana\
**Created:** [September 17, 2020, 8:37pm UTC](https://discuss.elastic.co/t/cannot-query-ip-address-fields-using-elastic-sql/249038 "2020-09-17T20:37:21Z")\
**Posts on this page:** 9\
**Page:** 1

<div class="post-metadata">

**Author:** ![fporrata](https://avatars.discourse-cdn.com/v4/letter/f/eb8c5e/32.png) [@fporrata](https://discuss.elastic.co/u/fporrata)\
**Post date:** [September 17, 2020, 8:37pm UTC](https://discuss.elastic.co/t/cannot-query-ip-address-fields-using-elastic-sql/249038/1 "2020-09-17T20:37:21Z")

</div>

I am using the Kibana console to run a SQL query with an ip field as criteria. Here is the query:

```auto
POST /_sql?format=json 
 {
  "query": """SELECT "@timestamp", "destination.ip" FROM "dummyindex" WHERE "destination.ip" IN ('127.01.01.01', '128.01.01.01') and "@timestamp" > '2020-09-16' """
}

```

This is the error:

```auto
{
  "error" : {
    "root_cause" : [
      {
        "type" : "verification_exception",
        "reason" : "Found 1 problem\nline 1:63: 1st argument of [\"destination.ip\" IN ('127.01.01.01', '128.01.01.01')] must be [ip], found value ['127.01.01.01'] type [keyword]"
      }
    ],
    "type" : "verification_exception",
    "reason" : "Found 1 problem\nline 1:63: 1st argument of [\"destination.ip\" IN ('127.01.01.01', '128.01.01.01')] must be [ip], found value ['127.01.01.01'] type [keyword]"
  },
  "status" : 400
}

```

Is there a special syntax to query ip fields using SQL?

---

<div class="post-metadata">

**Author:** ![highlandspring](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/highlandspring/32/75793_2.png) [@highlandspring](https://discuss.elastic.co/u/highlandspring)\
**Post date:** [September 17, 2020, 8:51pm UTC](https://discuss.elastic.co/t/cannot-query-ip-address-fields-using-elastic-sql/249038/2 "2020-09-17T20:51:54Z")

</div>

Is this error not saying the IP is not whitelisted?

---

<div class="post-metadata">

**Author:** ![fporrata](https://avatars.discourse-cdn.com/v4/letter/f/eb8c5e/32.png) [@fporrata](https://discuss.elastic.co/u/fporrata)\
**Post date:** [September 17, 2020, 9:03pm UTC](https://discuss.elastic.co/t/cannot-query-ip-address-fields-using-elastic-sql/249038/3 "2020-09-17T21:03:20Z")

</div>

No, it is looking at the field as a keyword instead of an ip data type and the query does not run

---

<div class="post-metadata">

**Author:** ![TomonoriSoejima](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/tomonorisoejima/32/33182_2.png) [@TomonoriSoejima](https://discuss.elastic.co/u/TomonoriSoejima)\
**Post date:** [September 24, 2020, 2:00am UTC](https://discuss.elastic.co/t/cannot-query-ip-address-fields-using-elastic-sql/249038/4 "2020-09-24T02:00:11Z")

</div>

Yeah looks that way and I just ran into the same problem.

---

<div class="post-metadata">

**Author:** ![TomonoriSoejima](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/tomonorisoejima/32/33182_2.png) [@TomonoriSoejima](https://discuss.elastic.co/u/TomonoriSoejima)\
**Post date:** [September 24, 2020, 2:14am UTC](https://discuss.elastic.co/t/cannot-query-ip-address-fields-using-elastic-sql/249038/5 "2020-09-24T02:14:46Z")

</div>

I borrowed the idea from [https://github.com/elastic/elasticsearch/pull/34758/files/7b43b24fa1dc3c6e9e2f843281cd6c9aa76b8cf9#diff-f9d17e274b4800d6b8f0fa433d1bce17R83](https://github.com/elastic/elasticsearch/pull/34758/files/7b43b24fa1dc3c6e9e2f843281cd6c9aa76b8cf9#diff-f9d17e274b4800d6b8f0fa433d1bce17R83) and it is now working for me in this form.

```auto
POST _sql
{
  "query":"select count(clientip) from kibana_sample_data_logs where clientip BETWEEN '129.40.10.1' AND '130.49.143.213'"
}

```

---

<div class="post-metadata">

**Author:** ![fporrata](https://avatars.discourse-cdn.com/v4/letter/f/eb8c5e/32.png) [@fporrata](https://discuss.elastic.co/u/fporrata)\
**Post date:** [September 24, 2020, 2:33am UTC](https://discuss.elastic.co/t/cannot-query-ip-address-fields-using-elastic-sql/249038/6 "2020-09-24T02:33:02Z")

</div>

Thank you for your answer. THe issue seems to be with the IN clause. The BETWEEN clause works fine. If you try your query like this:

```auto
select count(clientip) from kibana_sample_data_logs where clientip IN ( '129.40.10.1' , '130.49.143.213')

```

I bet you are going to get the same error. This seems to be a bug in elastic's implementation of SQL using ip addresses.

---

<div class="post-metadata">

**Author:** ![bogdan.pintea](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/bogdan.pintea/32/45740_2.png) [@bogdan.pintea](https://discuss.elastic.co/u/bogdan.pintea)\
**Post date:** [October 5, 2020, 11:56am UTC](https://discuss.elastic.co/t/cannot-query-ip-address-fields-using-elastic-sql/249038/7 "2020-10-05T11:56:45Z")

</div>

This might be improved, to have the values in the `IN` set converted to the type of the attribute, but you could also achieve that already by converting them explicitly: ...`where clientip IN ( '129.40.10.1'::IP , '130.49.143.213'::ip)`

---

<div class="post-metadata">

**Author:** ![fporrata](https://avatars.discourse-cdn.com/v4/letter/f/eb8c5e/32.png) [@fporrata](https://discuss.elastic.co/u/fporrata)\
**Post date:** [October 5, 2020, 2:10pm UTC](https://discuss.elastic.co/t/cannot-query-ip-address-fields-using-elastic-sql/249038/8 "2020-10-05T14:10:55Z")

</div>

Thank you very much. That worked.

---

<div class="post-metadata">

**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\
**Post date:** [November 2, 2020, 2:11pm UTC](https://discuss.elastic.co/t/cannot-query-ip-address-fields-using-elastic-sql/249038/9 "2020-11-02T14:11:06Z")

</div>

This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.