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.

1 Like
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
7

Hi Jean,

I’m wondering if this works correctly for your use case. Top function in this case returns the “smallest” value of “stateStatus” per bucket, not the last one. SORT command before STATS has no effect on the result.

Is it the final version of your query?

I have similar use-case and still looking for a solution.

Regards

Alexander