Security

Geographically improbable Access Using iplocation

JRamirezEnosys
Explorer

Hi everybody,

I was reading https://answers.splunk.com/answers/560188/logic-behind-geographically-improbable-access-dete.html 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

jawaharas
Motivator

You can use below query based on Haversine_formula

[BASE SEARCH]
| 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

jawaharas
Motivator

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

0 Karma
Career Survey
First 500 qualified respondents will receive a $20 gift card! Tell us about your professional Splunk journey.

Can’t make it to .conf25? Join us online!

Get Updates on the Splunk Community!

Take Action Automatically on Splunk Alerts with Red Hat Ansible Automation Platform

 Are you ready to revolutionize your IT operations? As digital transformation accelerates, the demand for ...

Calling All Security Pros: Ready to Race Through Boston?

Hey Splunkers, .conf25 is heading to Boston and we’re kicking things off with something bold, competitive, and ...

Beyond Detection: How Splunk and Cisco Integrated Security Platforms Transform ...

Financial services organizations face an impossible equation: maintain 99.9% uptime for mission-critical ...