Splunk Search

How to edit my search to check for a valid order of events against a lookup?

swe
Path Finder

Hi there,

i have a series of events wich contain time and location information and want to check if they are in a valid (actually defined in a lookup) order.

The search

index="touchtrack" Event.EventName="touched" host="touchtrack_file" |  lookup dbttloc.csv sensor AS Event.SensorNumber OUTPUT name longitude latitude | transaction maxspan=30s mvlist=t | head 1 | Table Time name | eval Time=mvdedup(Time) | eval name=mvdedup(name)

has this result:

Time                         name
2016-05-31 20:32:42.851001   München
2016-05-31 20:32:43.956112   Berlin
2016-05-31 20:32:45.233240   Frankfurt
2016-05-31 20:32:47.176434   Hamburg

I want to compare the oldest event (here München) against the next one (here Berlin) and so on against the order in a lookup.

From      To
München   Hamburg
Hamburg   Berlin

and mark the transaction as valid (or not).

In the given example, the transaction is not valid because München - Berlin is not in the lookup

Any ideas are welcome as I do not have any left... 🙂

thanks
swe

0 Karma

sundareshr
Legend

GIve this a try

index="touchtrack" Event.EventName="touched" host="touchtrack_file" |  lookup dbttloc.csv sensor AS Event.SensorNumber OUTPUT name longitude latitude | transaction maxspan=30s mvlist=t | head 1 | Table Time name | eval Time=mvdedup(Time) | eval name=mvdedup(name) | eval eventstart=mvindex(name, 0) | eval eventend=mvindex(name, 1) | lookup stationlist.csv From AS eventstart OUTPUT To | eval valid=if(eventend=To, "Yes", "No")

swe
Path Finder

thanks sundareshr,

this lead me to a different approach, without having to loop within the multivalue:

  • streamstats current=f global=f window=1 last(Nach) as Von *
0 Karma
Get Updates on the Splunk Community!

2024 Splunk Career Impact Survey | Earn a $20 gift card for participating!

Hear ye, hear ye! The time has come again for Splunk's annual Career Impact Survey!  We need your help by ...

Optimize Cloud Monitoring

  TECH TALKS Optimize Cloud Monitoring Tuesday, August 13, 2024  |  11:00AM–12:00PM PST   Register to ...

What's New in Splunk Cloud Platform 9.2.2403?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.2.2403! Analysts can ...