Kibana Visualization time range filter gave incorrect result

Greetings!
I have a issue with visualization result. Seems the kibana did not give me correct value.

I am using ELK 6.2.1. Also updated a few days ago to 6.2.3. Still the issue exists.

What I am trying to do:
I'd like to get daily activate number of IoT devices if the device sent data to server.

My query in kibana console give a number of 5 on 2016-11-19. Below is the query I am using:

my index pattern:
logstash-elt_op_dev_log-2016-11-10
logstash-elt_op_dev_log-2016-11-11
......

Console query:

GET /logstash-elt_op_dev_log-*/_search

  {
    "_source": ["json.model.dev_id", "json.dev.active_days", "json.dev.dev_created_date"],
    "size": 1,
    "query": {
      "bool" : {
          "must": [
             {"term": {
                  "json.dev.active_days": 1
              }},
              {"range" : {
                "@timestamp" : {
                    "gte": "2016-11-19T00:00:00", 
                    "lte": "2016-11-19T23:59:59",
                    "format": "strict_date_optional_time"
                }
              }}
            ]
        }
    },
    "aggs" : {
                   "distinct_terminals" : {
                      "cardinality" : {
                      "field" : "json.model.dev_id"
                }
              }
    }
  }

The result is

{
.......
      "hits": [{
        "_index": "logstash-elt_op_dev_log-2016.11.19",
        "_type": "doc",
        "_id": "O_dev_log_3078664",
        "_score": 2,
        "_source": {
          "json": {
            "model": {
              "dev_id": 15067
            },
            "dev": {
              "dev_created_date": "2016-11-17T22:34:50.000Z",
              "active_days": 1
            }
          }
        }
      }
  ...
  ]
},
"aggregations": {
    "distinct_terminals": {
      "value": 5
    }
  }

But when I trying to do the same in Visualization. On 2016-11-19. The number is 12 .

Below is the visualization setup:
Time range 2016-11-19 00:00:00 to 2016-11-19 23:59:59
metrics :
Unique Count: json.model.dev_id
buckets :
Date Histogram: @timestamp
Interval: 1Day

visualization Filter:
json.dev.activate_days is 1 (number)

24%20am

I double check the dev_id returned. The visualization gave 12 devices' id. but 7 of them were activated on 11-18 when their activated_days are 1. They also actived on 11-19. But on 11-19, the 7 devices' activated days were 2 (alive for 2 days)
From what I saw, it seems kibana aggregate both 11-18 and 11-19. and use 'or' to filter the result so the following device counted incorrectly:
e.g
2016-11-18 dev_id: 14677 activated_days 1
2016-11-18 dev_id: 15064 activated_days 0
2016-11-19 dev_id: 14677 activated_days 2
2016-11-19 dev_id: 15064 activated_days 1
Visualization will give me both 14677, 15064 if I were to query how may device is activated for 1 day on 11/19. That is equal to following console setup:

        {"range" : {
          "@timestamp" : {
              "gte": "2016-11-18T00:00:00",   # 1 more day
              "lte": "2016-11-19T23:59:59",
              "format": "strict_date_optional_time"
          }
        }}

But only 15064 is the correct device id.
From what I saw. console gave correct result. But Visualization gave more values than I expected.
I tried to tune the threshold to 1000, not working
I also tried to change the time-range e.g to 2016-11-18 12:00:00 ~ 2016-11-19 12:00:00 also not working.

Is there additional setting/filter I need to add in visualization?

Thanks!
Ray

My hypothesis: it's a timezone issue.

2 questions:

  • What's the value of dateFormat:tz in Kibana's Advanced Settings?
  • If it's "browser", what timezone is your local machine in? If it's not UTC, Kibana is doing timezone conversions for you, because all dates in ES are stored as UTC.

You should be able to confirm this by looking at the request Kibana is generating. Just pop open the spy tab on the visualization (the small arrow button in the bottom left hand corner) and click the request tab.

Mat Bargar
Thanks for your feedback. I checked my ELK setup. You are right that the TZ setup was my local time instead of UTC. After I changed to UTC. Kibana and console seems to gave me same result.
Thanks for your help!

--Ray

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