How to get only latests events from an events set ?

julienlance — Wed, 30 Sep 2020 02:06:16 GMT

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 !

Re: How to get only latests events from an events set ?

richgalloway — Tue, 10 Sep 2019 13:09:24 GMT

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.

Re: How to get only latests events from an events set ?

julienlance — Tue, 10 Sep 2019 13:55:25 GMT

Many thanks !

topic Re: How to get only latests events from an events set ? in Splunk Search

How to get only latests events from an events set ?

Re: How to get only latests events from an events set ?

Re: How to get only latests events from an events set ?