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 ?

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.

Getting LAST record in aggr in ES|QL

Elastic Stack Elasticsearch

jfsardon (Jean Francois Sardon Arraz) January 9, 2025, 9:23am 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

Topic		Replies	Views
Query the last match by a field Elasticsearch	3	911	May 10, 2017
Get the last record for each day Kibana	5	3755	July 6, 2017
Get value from last arrived data and compare it with 0 Elasticsearch	1	353	July 6, 2017
How to query for "max" value? Elasticsearch	4	513	July 5, 2017
Elasticsearch query to pull the record based on max timestamp Logstash	6	706	March 16, 2018

Getting LAST record in aggr in ES|QL

Related topics