I have an index for purchases. In each document there is a customer.name and cashier.name field. I want to group the documents using ESQL based on customer.name and return the most recent value of cashier.name. This will let me see the most recent cashier for each user at a given time.
FROM purchases-*
| WHERE event.outcome == "success"
| SORT @timestamp DESC
| <Here is where I need a Stats function to get the most recent cashier>
| LIMIT 10
The following example does not work because while it gets the most recent timestamp, the VALUES aggregates the cashier.name values from all matching documents grouped by customer.name.
FROM purchases-*
| WHERE event.outcome == "success"
| SORT @timestamp DESC
| STATS
latest_timestamp = MAX(@timestamp),
most_recent_cashier = VALUES(cashier.name) by customer.name
| LIMIT 10
Do you have any idea how to accomplish this or if it is possible with ESQL in its current state?
I feel like if it is possible, the solution would have to use the MAX(@timestamp) in some form, but I can't figure out how to use the result of this function to only return the most recent event per customer.
If you have any insight or experience solving something like this, I would really appreciate any help.