Differences in values between Dev Tools SQL and Visualization

Elastic Stack Kibana
1

Hi all,

I am new to elasticsearch/kibana but am having issues matching out the figures between two seemingly similar queries in the Kibana's Dev Tools SQL function and Kibana's visualization tool. The figures are extremely similar but not the same as I would expect. Kibana's visualization tools returns more counts for every FIELD bucket.

This is not unique to the query below but every query I have attempted.

Also, using pythons elasticsearch_dsl package, I was able to match Kibana's SQL Dev Tools figures.

I am using Kibana V7.9.2.

Example queries:

In SQL Dev Tools:
POST _sql?format=txt { "query": "SELECT <FIELD>, COUNT(*) FROM <INDEX> WHERE DATETIME_FORMAT(\"@timestamp\", 'yyyy,MM,dd') = DATETIME_FORMAT(CAST('2021-05-10'AS DATE),'yyyy,MM,dd') GROUP BY <FIELD>" }

Kibana Visualization:
Start Date: May 10, 2021 @ 00:00:00.000
End Date: May 10, 2021 @ 23:59:59.999
Metric: Count
Buckets:
Split Rows
Aggregation: Terms
Field: FIELD
Size: 99999

2

reason is SQL query in kibana dev tool is putting out date as is.
Kibana visualization is thinking your data is UTC and converting back to your browser time

This is problem with timezone. UTC to local time and vice-versa

3

Thank you so much @elasticforme, that makes complete sense. Is there a way to make these two queries return the same results in Kibana?

4

yes you have to create another field in ELK which will take date with timezone and then ELK will not covert it.
here is example

mutate{
         add_field => { "loged_date_timezone" => "%{loged_date}" }
   }
   ## this will be conerted to UTC by ELK
date {
      match => ["loged_date", "dd-MMM-yy HH:mm:ss", "ISO8601"]
      target => "loged_date"
   }

   ## lets set to UTC for all data, that way data will stay as it is in database (eventhought it is in CST time), and we can use this for sql query
   date { match => ["loged_date_timezone", "dd-MMM-yy HH:mm:ss", "ISO8601"]
        timezone => "Etc/UTC"
        target => "loged_date_timezone"
  }

now your index will have two date.
you create two index pattern one with loged_date
another one with loged_date_timezone

on SQL use index pattern created with loged_date_timezone and date field loged_date_timezone
on kibana viz use another one.

5

This is incredibly helpful @elasticforme. Thank you so much for the help

6

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