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
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
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.
@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 ...
But I'm having some troubles configuring the graph when I don't use as 'AS', seems something wrong with using "@timestamp".
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
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.
Apache, Apache Lucene, Apache Hadoop, Hadoop, HDFS and the yellow elephant
logo are trademarks of the
Apache Software Foundation
in the United States and/or other countries.