How to test ip range query

[TomonoriSoejima]

this test is to compare the range query against IP field and keyword field and see if the resutlt is same or different.

expected outcome is results from keyword field is wrong.

1. get the sample data

Go to http://localhost:5601/app/home#/tutorial_directory/sampleData and download Sample web logs

2. create a mapping with clientip field as type keyword.

sample mapping

``` PUT new { "mappings" : { "properties" : { "@timestamp" : { "type" : "alias", "path" : "timestamp" }, "agent" : { "type" : "text", "fields" : { "keyword" : { "type" : "keyword", "ignore_above" : 256 } } }, "bytes" : { "type" : "long" }, "clientip" : { "type" : "keyword" }, "event" : { "properties" : { "dataset" : { "type" : "keyword" } } }, "extension" : { "type" : "text", "fields" : { "keyword" : { "type" : "keyword", "ignore_above" : 256 } } }, "geo" : { "properties" : { "coordinates" : { "type" : "geo_point" }, "dest" : { "type" : "keyword" }, "src" : { "type" : "keyword" }, "srcdest" : { "type" : "keyword" } } }, "host" : { "type" : "text", "fields" : { "keyword" : { "type" : "keyword", "ignore_above" : 256 } } }, "index" : { "type" : "text", "fields" : { "keyword" : { "type" : "keyword", "ignore_above" : 256 } } }, "ip" : { "type" : "ip" }, "machine" : { "properties" : { "os" : { "type" : "text", "fields" : { "keyword" : { "type" : "keyword", "ignore_above" : 256 } } }, "ram" : { "type" : "long" } } }, "memory" : { "type" : "double" }, "message" : { "type" : "text", "fields" : { "keyword" : { "type" : "keyword", "ignore_above" : 256 } } }, "phpmemory" : { "type" : "long" }, "referer" : { "type" : "keyword" }, "request" : { "type" : "text", "fields" : { "keyword" : { "type" : "keyword", "ignore_above" : 256 } } }, "response" : { "type" : "text", "fields" : { "keyword" : { "type" : "keyword", "ignore_above" : 256 } } }, "tags" : { "type" : "text", "fields" : { "keyword" : { "type" : "keyword", "ignore_above" : 256 } } }, "timestamp" : { "type" : "date" }, "url" : { "type" : "text", "fields" : { "keyword" : { "type" : "keyword", "ignore_above" : 256 } } }, "utc_time" : { "type" : "date" } } } } ``` 　

3. reindex document from kibana_sample_data_logs to new index.

POST _reindex
{
  "source": {
    "index": "kibana_sample_data_logs"
  },
  "dest": {
    "index": "new"
  }
}

4. do the search in both indices.

GET kibana_sample_data_logs/_search
{
  "query": {
    "bool": {
      "filter": [
        {
          "range": {
            "clientip": {
              "gte": "130.49.143.213",
              "lte": "130.246.123.197"
            }
          }
        }
      ]
    }
  },
  "from": 0,
  "size": 0,
  "_source": [
    "clientip"
  ]
}

GET new/_search
{
  "query": {
    "bool": {
      "filter": [
        {
          "range": {
            "clientip": {
              "gte": "130.49.143.213",
              "lte": "130.246.123.197"
            }
          }
        }
      ]
    }
  },
  "from": 0,
  "size": 0,
  "_source": [
    "clientip"
  ]
}

5. See the result and the latter for new index returns no hits.

# GET kibana_sample_data_logs/_search
{
  "took" : 0,
  "timed_out" : false,
  "_shards" : {
    "total" : 1,
    "successful" : 1,
    "skipped" : 0,
    "failed" : 0
  },
  "hits" : {
    "total" : {
      "value" : 96,
      "relation" : "eq"
    },
    "max_score" : null,
    "hits" : [ ]
  }
}

# GET new/_search
{
  "took" : 0,
  "timed_out" : false,
  "_shards" : {
    "total" : 1,
    "successful" : 1,
    "skipped" : 0,
    "failed" : 0
  },
  "hits" : {
    "total" : {
      "value" : 0,
      "relation" : "eq"
    },
    "max_score" : null,
    "hits" : [ ]
  }
}

[TomonoriSoejima]

Now I am trying to do the same in _sql endpoint and here goes the result.

POST _sql
{
  "query":"select count(clientip) from  kibana_sample_data_logs where clientip IN ('130.49.143.213','130.246.123.197')"
}

• response

{
  "error" : {
    "root_cause" : [
      {
        "type" : "verification_exception",
        "reason" : "Found 1 problem\nline 1:60: 1st argument of [clientip IN ('130.49.143.213','130.246.123.197')] must be [ip], found value ['130.49.143.213'] type [keyword]"
      }
    ],
    "type" : "verification_exception",
    "reason" : "Found 1 problem\nline 1:60: 1st argument of [clientip IN ('130.49.143.213','130.246.123.197')] must be [ip], found value ['130.49.143.213'] type [keyword]"
  },
  "status" : 400
}

[TomonoriSoejima]

This works !!

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

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

POST _sql
{
  "query":"select clientip from kibana_sample_data_logs where clientip BETWEEN '129.40.10.1' AND '130.49.143.213'"
}

POST _sql
{
  "query":"select clientip from new where clientip BETWEEN '129.40.10.1' AND '130.49.143.213'"
}