topic Re: Distance between two or more Geolocations (Lat, Lon,) in Security

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

Bryon_bowman — Mon, 05 Jul 2021 15:19:50 GMT

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.

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

Gene — Tue, 06 Jul 2021 09:21:31 GMT

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

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

Bryon_bowman — Tue, 06 Jul 2021 15:24:02 GMT

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

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

Gene — Wed, 07 Jul 2021 06:19:33 GMT

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.

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

Bryon_bowman — Wed, 07 Jul 2021 17:15:34 GMT

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?