How to get only latests events from an events set ?

[julienlance]

Hello Splunkers !

We need your help, as we didn't found any answers solving our issue 😞
We will be so grateful 🙂

We have severals events coming from the same sourcetype, identifing source and VPN tunnels dest.
These events are sent by packets with the same "_time" value. Due to the network, the delay betwen those packets is random (could be few seconds or one hour).

Here is an instance :
2019-09-06 18:08:35 ServernameA dst-ip:10.10.10.10 tunnel-state:up
2019-09-06 18:08:35 ServernameA dst-ip:10.10.2.2 tunnel-state:up
2019-09-06 18:08:35 ServernameA dst-ip:10.10.2.3 tunnel-state:down
2019-09-06 18:08:35 ServernameA dst-ip:10.10.2.4 tunnel-state:up
2019-09-06 18:08:31 ServernameA dst-ip:10.10.10.10 tunnel-state:up
2019-09-06 18:08:31 ServernameA dst-ip:10.10.2.2 tunnel-state:up
2019-09-06 18:08:31 ServernameA dst-ip:10.10.2.3 tunnel-state:down
2019-09-06 18:08:31 ServernameA dst-ip:10.10.2.4 tunnel-state:up
2019-09-06 18:04:31 ServernameA dst-ip:10.10.10.10 tunnel-state:up
2019-09-06 18:04:31 ServernameA dst-ip:10.10.2.2 tunnel-state:up
2019-09-06 18:04:31 ServernameA dst-ip:10.10.2.3 tunnel-state:down
2019-09-06 18:04:31 ServernameA dst-ip:10.10.2.4 tunnel-state:up

We want to show in a table only the last events (here, events sent at 18:08:35).
First, we tried with the relative time-picker for 15mins or 60 mins. But as the delaying time betwen events is random, it's not working, both events are presented.

In a second time, we tried with the "last" command, as the following example :
index="vpn-state" sourcetype="routers:json" hostname=ServernameA earliest =-900s
| stats last(_time) as last_time
| table _time,hostname,dst-ip,tunnel-state
| where _time ==last_time

But that doesn't work too, probably a syntax error or because "stats" results can't be evaluated.

Any clues for helping us ?
Many thanks !

[richgalloway · Accepted solution]

stats results can indeed be evaluated. The problem in your example query is stats only returns one field, last_time, so _time, hostname, etc. are not available to table. That can be resolved by using eventstats in place of stats.

[julienlance]

Many thanks !