Elasticsearch SQL ORDER BY Month

Elastic Stack Elasticsearch
1

Hello,

I'm almost there getting my vertical bar to work in Canvas based on a SQL query, but I'm having a hard time ordering the monthly buckets chronologically. My SQL query:

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

Result:

image
image1186×175 3.25 KB

So how can I get the months ordered by @timestamp?

Grtz

Willem

2

Instead/besides MONTH_NAME you could GROUP BY MONTH.

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

4

(Trying to prevent auto close) So ordering buckets chronologically with essql is possible or not?

5

@willemdh, maybe something like this would work for you?

SELECT COUNT(DISTINCT user.name), DATETIME_FORMAT("@timestamp", 'MMM')
FROM ...
WHERE ...
GROUP BY DATETIME_FORMAT("@timestamp", 'yyyy-LL'), 2
ORDER BY ...
6

Thanks @bogdan.pintea for your answer.

The data preview seems perfect:

image

But I'm having some troubles configuring the graph when I don't use as 'AS', seems something wrong with using "@timestamp".

image

So tried with 'AS':

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')
GROUP BY DATETIME_FORMAT("@timestamp", 'yyyy-LL'), 2

Which produces:

image
image851×269 10.7 KB

But this is also ordered alphabetically..

So it seems to result in the same issue using "GROUP BY Month, MonthNumber".

Willem

7

I see.
This seems to be a Canvas-specific behaviour. My Canvas kung-fu is not that strong unfortunately, but while trying to replicate your experience I've noticed that a null X-axis value will trigger the plot to reorder the X values. I don't see a null value in your list, but if you think there is - and just not rendered - you might want to try filtering it out: add a AND Month IS NOT NULL to your WHERE clause.

You could try to play with the expression in the </> Expression editor and potentially add a | head count=<some number> (or | tail ..) after | essql function to try find out if there is a value triggering the reorder.
Even better, maybe ask in the Kibana forum.

You could also try to generate a name for the months which is immutable to reordering, but I feel there should be an easier way to get what you want.

8

Created Canvas Group By Month with Chronological order

Sorry for the many questions and ty very much for the insights @bogdan.pintea