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? Did you miss .conf21 Virtual?

### Good news! The event's keynotes and many of its breakout sessions are now available online, and still totally FREE! Catch Up Now >>

Get Updates on the Splunk Community!