Getting LAST record in aggr in ES|QL

Elastic Stack Elasticsearch
1

Is there an simple way to get the last value of a field (@timestamp sorted) for each customer, hostname, etc.. For example :
select logs-myindex-xxx | SORT (@timestamp) | STATS LAST (maxsize) BY customer, hostname...

but where is no LAST or First stats function...

any idea ?

2

This one got me for a bit as well. There is the TOP function which should be able to achieve this. Set limit to 1 and change order to act as a first/last function.

1 Like
3

Great ! It's not a very easy syntax, but it's works. Thanks Ben !

1 Like
4

Can you maybe share the ES\QL query you ended up with, so thread actually contains the solution, rather than (good) clues that lead to a solution.

5

Sure ! Here's the request :
FROM logs-connectivity-qyyp | SORT @timestamp DESC | STATS lastState = TOP (stateStatus,1,"asc"), transitionTime=MAX(transitionTime) BY Client, hostname | WHERE lastState LIKE "Failed"

1 Like
6

and I hope ES|QL will be enable in Canvas soon ....

1 Like