topic Re: How to edit my search to check for a valid order of events against a lookup? in Splunk Search

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

swe — Tue, 31 May 2016 20:44:45 GMT

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

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

sundareshr — Wed, 01 Jun 2016 03:46:21 GMT

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")

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

swe — Wed, 01 Jun 2016 12:41:34 GMT

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 *