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