Geographically improbable Access Using iplocation


Hi everybody,

I was reading and I am trying to get a better and more simple query to find geographically improbable access.

My environment is not as full as these, I have atm 3 variables

subject=ID of the user
_time=timestamp (doh)
IP=IP address from where they have logged on
from IP I can obtain the fields "lat" and "lon"

Then with some simple string magic I am looking at the following

index=main eventtype="loginevents" subject=*     | fields ip subject _time | iplocation ip  | eval lat=tostring(lat), lon=tostring(lon) | eval latlon=lat.", ".lon | stats count by ip latlon 

My issue is that this results just give me basic statistic data, what i want is to compare the 2 last logins and see how far those 2 locations are, so it would be adding the previous login's lat and lon in different fields, any idea to apply this?

0 Karma


You can use below query based on Haversine_formula

| dedup user_id, clientip 
| eval time1=_time
| map maxsearches=99 search="search [BASE SEARCH]
    | eval clientip1=$clientip$, time1=$time1$, time2=_time
    | search user_id=$user_id$ clientip!=clientip1
    | dedup user_id, clientip 
    | rename clientip as clientip2"
| where clientip1!=clientip2 
| iplocation clientip1 
| eval lat1=lat, lon1=lon, city1=City, country1=Country 
| iplocation clientip2 
| eval lat2=lat, lon2=lon , city2=City, country2=Country 
| 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 
| eval timestamp1=strftime(time1, "%y-%m-%d %H:%M:%S"), timestamp2=strftime(time2, "%y-%m-%d %H:%M:%S")
| table user_id, timestamp1, clientip1, city1, country1, timestamp2,clientip2, city2, country2, distance
| rename distance as "distance in KM"

Sample output:
alt text

0 Karma


Can you upvote and accept the answer if it's helped you? Thanks.

0 Karma
Get Updates on the Splunk Community!

Improve Your Security Posture

Watch NowImprove Your Security PostureCustomers are at the center of everything we do at Splunk and security ...

Maximize the Value from Microsoft Defender with Splunk

 Watch NowJoin Splunk and Sens Consulting for this Security Edition Tech TalkWho should attend:  Security ...

This Week's Community Digest - Splunk Community Happenings [6.27.22]

Get the latest news and updates from the Splunk Community here! News From Splunk Answers ✍️ Splunk Answers is ...