Elasticsearch SQL ORDER BY Month

Elastic Stack Elasticsearch
3

Hello @bogdan.pintea,

Thanks a lot for your answer. In the meantime we are +- using something similar to what you suggest:

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

The result is:

image
image751×243 8.05 KB

But as you can see the graph start with month 1 (january). As it's november currently, the first bucket in the graph should be december last year. I think we should somehow evolve to a group by / sort based on a combination of year and month. But not sure how to accomplish that.

Grtz

Willem