How to extract information from two rows in a search result

[MemoreX42]

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

[schose · Accepted solution]

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

[jeffland]

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.