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
Double Terms aggregation with top_hits sub-aggregation not working
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

8

I am also in search of a way to get the most recent value, as opposed to the smaller or largest one.

9

Have you tried using MAX()?

Something like this:

FROM index
| STATS last_timestamp = MAX(event.ingested)

I use this on some queries to get the ingestion lag for some data streams:

FROM index
| STATS last_timestamp = MAX(event.ingested)
| EVAL lag = DATE_DIFF("minute", last_timestamp, NOW())
| LIMIT 1
1 Like
10

MAX works to get me the latest timestamp, but I need to extract the value of another field from the most recent document.

I was able to actually get the latest value by using CONCAT to combine the value I am looking for with the timestamp.

| EVAL timestamp_str = TO_STRING(@timestamp)
// Create a string that looks like combines timestamp and size
| EVAL combo = CONCAT(timestamp_str, " | ", TO_STRING(`file.size(mb)`))
| STATS 
    latest_combo = MAX(combo)
  BY file.path, user.name, computer.name
// Split the string back apart to get the size
| EVAL 
    latest_timestamp = TO_DATETIME(SUBSTRING(latest_combo, 1, 24)),
    latest_size = TO_DOUBLE(SUBSTRING(latest_combo, 28, 100))

I don’t love this workaround, but it works.

11

Look at the TOP function, as enhanced in 9.3.0+

Sadly the outputField is a bit limited, allowing a keyword field as outputField would be a massive enhancement in many use cases IMHO, but it might be useful in your case?

I asked about this here but sadly got no answer.