Security

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

Bryon_bowman
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 (2)
Tags (2)
0 Karma
1 Solution

Gene
Path Finder

Your search
| 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

View solution in original post

Gene
Path Finder

Your search
| 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

Bryon_bowman
Explorer

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

0 Karma

Gene
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.

Bryon_bowman
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? 

0 Karma
Get Updates on the Splunk Community!

Fueling your curiosity with new Splunk ILT and eLearning courses

At Splunk Education, we’re driven by curiosity—both ours and yours! That’s why we’re committed to delivering ...

Splunk AI Assistant for SPL 1.1.0 | Now Personalized to Your Environment for Greater ...

Splunk AI Assistant for SPL has transformed how users interact with Splunk, making it easier than ever to ...

Unleash Unified Security and Observability with Splunk Cloud Platform

     Now Available on Microsoft AzureOn Demand Now Step boldly into the AI revolution with enhanced security ...