Cannot query ip address fields using elastic SQL

Elastic Stack Kibana
#1

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

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:

{
  "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?

#2

Is this error not saying the IP is not whitelisted?

#3

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

#4

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

#5

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

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

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:

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.

#7

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)

#8

Thank you very much. That worked.

#9

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