CODEWITHSUNDEEP
sqlelasticsearchdata-visualizationkibana
josephkibe
josephkibe

Reputation: 1343

How do I plot a timeseries histogram using Elastic SQL in Kibana with Canvas?

We have our monitoring data in Elasticsearch, visualized via Kibana, and I'm trying to put together some charts using the Canvas feature in Kibana. For the life of me, I can't figure out how to get the time series histogram to work.

Per the documentation, I'm trying to use this query to pull the data out for the plot:

SELECT HISTOGRAM("@timestamp", INTERVAL 1 MONTH) as t, count(*) as c FROM "nginx*"

And I get the error, when I try to use the "Preview" button:

[essql] > Unexpected error from Elasticsearch: [verification_exception] Found 1 problem(s) line 1:8: [HISTOGRAM("@timestamp", INTERVAL 1 MONTH)] needs to be part of the grouping

Someone in our office suggested that I add GROUP BY t, but that didn't do anything, and indeed returned the same error. What am I doing wrong here? Is there some other better way to get data out so I can set up a time series plot?

Upvotes: 1

Views: 1266

Answers (1)

oandersonm
oandersonm

Reputation: 174

Placing the histogram expression under GROUP BY will solve this:

SELECT COUNT(*) AS c FROM "nginx*" GROUP BY HISTOGRAM("@timestamp", INTERVAL 1 MONTH)

Upvotes: 1

Related Questions