I need tocalculate distances between points with GEOIP using latitude and longitude directly in a search with trigonometric functions, Can I use these functions in Splunk 6.1.5?
Example:
| eval cal_a = (sen_lati * sen_lati) + cos(GEO_1_LAT_NUM) * cos(GEO_2_LAT_NUM) * (sen_long* sen_long)
| eval cal_c = 2 * (atan2(sqrt(cal_a),sqrt(1-(cal_a))))
| eval distancia = round(cal_c * r_tierra)
Greetings!
Hi there,
fast forward into the future, we can do the great circle formula in Splunk now.
This example will provide the expected result:
| makeresults
| eval lat1=1, lon1=1, lat2=2, lon2=2
| eval rlat1 = pi()*lat1/180, rlat2=pi()*lat2/180, rlat = pi()*(lat2-lat1)/180, rlon= pi()*(lon2-lon1)/180
| eval a = sin(rlat/2) * sin(rlat/2) + cos(rlat1) * cos(rlat2) * sin(rlon/2) * sin(rlon/2)
| eval c = 2 * atan2(sqrt(a), sqrt(1-a))
| eval distance = 6371 * c
| table lat1 lon1 lat2 lon2 distance
distance
will be the distance in km
.
Hope this helps ...
cheers, MuS
Here's a handy macro I just created base on this answer. I used the multiplier for miles instead of km. Define as haversine(4), takes args lat1,lon1,lat2,lon2 and creates a "miles" field.
eval rlat1 = pi()*$lat1$/180, rlat2=pi()*$lat2$/180, rlat = pi()*($lat2$-$lat1$)/180, rlon = pi()*($lon2$-$lon1$)/180
| eval a = sin(rlat/2) * sin(rlat/2) + cos(rlat1) * cos(rlat2) * sin(rlon/2) * sin(rlon/2)
| eval c = 2 * atan2(sqrt(a), sqrt(1-a))
| eval miles = 3963 * c
Here it is included in the use case that brought me here.
sourcetype=oktaim2:log
| streamstats global=f window=2 current=t earliest(client.geographicalContext.geolocation.lon) AS lon1 latest(client.geographicalContext.geolocation.lon) AS lon2 earliest(client.geographicalContext.geolocation.lat) AS lat1 latest(client.geographicalContext.geolocation.lat) AS lat2 earliest(client.geographicalContext.city) AS src_city latest(client.geographicalContext.city) AS dest_city earliest(client.geographicalContext.state) AS src_state latest(client.geographicalContext.state) AS dest_state earliest(_time) AS departed_time latest(_time) AS arrived_time BY user
| where lat1!=lat2 AND lon1!=lon2
| `haversine(lat1,lon1,lat2,lon2)`
| eval hours=(arrived_time-departed_time)/60/60
| eval avg_mph=miles/hours
| where avg_mph>500 AND miles>100
| eval src_locale=src_city . ", " . src_state, dest_locale=dest_city . ", " . dest_state
| table _time lat1 lon1 lat2 lon2 src_locale dest_locale miles hours avg_mph user
| sort _time desc
Thanks a lot for this, it worked fine for me!
You can use following app to calculate distance between two points
https://splunkbase.splunk.com/app/936/#/documentation
For an alternate solution, see the answers from @Sideview from this post
https://answers.splunk.com/answers/90694/find-the-distance-between-two-or-more-geolocation-coordinat...