topic How to extract information from two rows in a search result in Splunk Search

How to extract information from two rows in a search result

MemoreX42 — Mon, 20 Apr 2015 07:57:30 GMT

Hi experts,

I am trying to find a way of extracting information out of a search result and combining this information with another row. It's pretty hard to explain, so let me show you what I mean:

Search: index=idx LogType="Locations"

Result:

_time | Latitude |Longitude
13:27:00 | 52.111 | 17.111
13:30:00 | 52.222 | 17.222
13:33:00 | 52.333 | 17.333
13:36:00 | 52.444 | 17.444

This is the basic search I got. Now I would like to calculate the distance between each of the result rows. I found a plugin called haversine (https://splunkbase.splunk.com/app/936/#/overview) which calculates the distance between two geo locations, so that is not really the problem, but what I'm really struggling with is to combine two rows of this search.

Ultimately, in the above example it should calculate three distances, dist(52.444, 17.444 -> 52.333, 17.333), dist(52.333, 17.333 -> 52.222, 17.222) and dist(52.222, 17.222 -> 52.111, 17.111).

So the final result could look like this:

_time | Distance
13:36:00 | 5000
13:33:00 | 4800
13:30:00 | 4600

Does anyone have an idea how something like this would be possible?

Thanks a lot,
Christian

Re: How to extract information from two rows in a search result

jeffland — Mon, 20 Apr 2015 08:36:54 GMT

The way to go when you want to compare two (or more) rows is streamstats. I would imagine that you could set window=2 and use last() to get the value from the previous row (don't forget to set current=false for this to work), then input those two into haversine.

Re: How to extract information from two rows in a search result

schose — Mon, 20 Apr 2015 09:37:15 GMT

Hi,

used your example data i used

index=temp sourcetype=distance | 
streamstats current=f last(Longitude) as lastLongitude last(Latitude) as lastLatitude |
eval lastlocation= lastLatitude  + " , " + lastLongitude | 
eval currlocation = Latitude  + " , " + Longitude |
search lastlocation=* AND currlocation=* |
haversine originField=lastlocation currlocation outputField=distance | 
table _time lastlocation currlocation distance

output is:

_time lastlocation currlocation distance
2015-04-19 13:33:00 52.444 , 17.444 52.333 , 17.333 14.459705438259151
2015-04-19 13:30:00 52.333 , 17.333 52.222 , 17.222 14.469574885347384
2015-04-19 13:27:00 52.222 , 17.222 52.111 , 17.111 14.47944752893998

Cheers,

Andreas