Security

## Distance between two or more Geolocations (Lat, Lon,)

Explorer

I'm have a search that pulls in user login info with lat and lon. I'm trying to calculate the distance between two cordinates for the same user name. If there isn't a match on username, I want it to move to the next match and then output the distance between the two with the login time.

Labels (3)

• ### other

Tags (2)
1 Solution
Path Finder

| eventstats dc(src) as src_count by user
| search src_count>1
| sort 0 + _time
| iplocation src
| where isnotnull(lat) AND isnotnull(lon)
| streamstats window=2 global=false earliest(lat) as prev_lat, earliest(lon) as prev_lon, earliest(_time) as prev_time, earliest(src) as prev_src, earliest(City) as prev_city, earliest(Country) as prev_country, earliest(app) as prev_app by user
| where (src != prev_src)
| eval lat1_r=((lat * 3.14159265358) / 180), lat2_r=((prev_lat * 3.14159265358) / 180), delta=(((prev_lon - lon) * 3.14159265358) / 180), distance=(3959 * acos(((sin(lat1_r) * sin(lat2_r)) + ((cos(lat1_r) * cos(lat2_r)) * cos(delta))))), distance=round(distance,2)
| fields - lat1_r, lat2_r, long1_r, long2_r, delta
| eval time_diff=if((('_time' - prev_time) == 0),1,('_time' - prev_time)), speed=round(((distance * 3600) / time_diff),2)
| eval prev_time=strftime(prev_time,"%Y-%m-%d %H:%M:%S")
| table user, src, _time, City, Country, app, prev_src, prev_time, prev_city, prev_country, prev_app, distance, speed

Hope that will help.

Thanks, Gene

Path Finder

| eventstats dc(src) as src_count by user
| search src_count>1
| sort 0 + _time
| iplocation src
| where isnotnull(lat) AND isnotnull(lon)
| streamstats window=2 global=false earliest(lat) as prev_lat, earliest(lon) as prev_lon, earliest(_time) as prev_time, earliest(src) as prev_src, earliest(City) as prev_city, earliest(Country) as prev_country, earliest(app) as prev_app by user
| where (src != prev_src)
| eval lat1_r=((lat * 3.14159265358) / 180), lat2_r=((prev_lat * 3.14159265358) / 180), delta=(((prev_lon - lon) * 3.14159265358) / 180), distance=(3959 * acos(((sin(lat1_r) * sin(lat2_r)) + ((cos(lat1_r) * cos(lat2_r)) * cos(delta))))), distance=round(distance,2)
| fields - lat1_r, lat2_r, long1_r, long2_r, delta
| eval time_diff=if((('_time' - prev_time) == 0),1,('_time' - prev_time)), speed=round(((distance * 3600) / time_diff),2)
| eval prev_time=strftime(prev_time,"%Y-%m-%d %H:%M:%S")
| table user, src, _time, City, Country, app, prev_src, prev_time, prev_city, prev_country, prev_app, distance, speed

Hope that will help.

Thanks, Gene

Explorer

This looks great. Just two questions...what is speed and is the distance in Kilometers or Miles and speed MPH or KPH?

Path Finder

Speed is e.g. when user connected from London and next time from China - in this field you can see with what speed user was traveling. This can be very suspicious in case user in 5 minutes did so. 🙂 distance is in miles but you can recalculate for your needs.

Explorer

Thank you!! I should have clarified. is the speed in MPH? Also is there a way to add time between the two logins as a column?

Get Updates on the Splunk Community!

#### Enterprise Security Content Update (ESCU) | New Releases

In the last month, the Splunk Threat Research Team (STRT) has had 2 releases of new security content via the ...

#### Announcing the 1st Round Champion’s Tribute Winners of the Great Resilience Quest

We are happy to announce the 20 lucky questers who are selected to be the first round of Champion's Tribute ...

#### We’ve Got Education Validation!

Are you feeling it? All the career-boosting benefits of up-skilling with Splunk? It’s not just a feeling, it's ...