Canvas Group By Month with Chronological order

[willemdh]

Hello,

I'm trying for some time to create a graph in canvas of unique users per month, ordered chronologically (oldest month of last year first). Whatever ESSQL query I'm trying, it seems to always order alphabetically.

SELECT COUNT(DISTINCT user.name) AS UniqueUsers,
DATETIME_FORMAT("@timestamp", 'MMM') AS Month
FROM "nagios" 
WHERE "@timestamp" > now() - interval 1 years AND event.dataset LIKE 'nagios.audit' AND MATCH(message,'Logged in') AND Month IS NOT NULL
GROUP BY Month

Result:

It was suggested to me to ask in the Kibana forum. Please check Elasticsearch SQL ORDER BY Month for more info on what I've been trying.

Anyone got an idea if this is even possible and if so how?

Full expression:

filters

| essql

query="SELECT COUNT(DISTINCT user.name) AS UniqueUsers,

DATETIME_FORMAT(\"@timestamp\", 'MMM') AS Month

FROM \"nagios\"

WHERE \"@timestamp\" > now() - interval 1 years AND event.dataset LIKE 'nagios.audit' AND MATCH(message,'Logged in') AND Month IS NOT NULL

GROUP BY Month"

| pointseries x="Month" y="UniqueUsers"

| plot defaultStyle={seriesStyle bars=0.75} legend="ne"

| render

Thanks!

Willem

[lukeelmers]

What version of the stack are you running? There's a known regression with sorting in the plot function starting in 7.9.

If you are running 7.9 or higher, you can follow along with the issue here: https://github.com/elastic/kibana/issues/62770

[willemdh]

@lukeelmers Thank you for this information, we are running 7.9.2. This issue seems related. I'll follow progression there.