How to find a user logging in from 2 different countries
I'm struggling to find a way to detect users from logging in from 2 different countries within a timeframe. I have the userID which is the users email address they are using to log in, and I have the clientIP, which I'm using geolookup to find the country they are logging in from, but I'm not sure how to compare my results to see if they are logging in from 2 different locations.
The query in currently using is:
_collector="Office365"
| json field=_raw "UserId", "ClientIP", "Operation"
| where Operation="UserLoggedIn"
| lookup country_name from geo://location on ip = ClientIP
| Count as eventCount by UserID, ClientIP, country_name
Any help would be much appreciated. Thanks
-
I received this from a user on reddit and it's interesting. It should do what you're looking for with some tweaks
//Log Source
| user_username as user
| srcdevice_ip as src_ip
// Create table listing successive logins by user
| _messagetime as login_time
| count BY login_time, user, src_ip
| sort BY user, +login_time
| where isValidIPv4(src_ip)
| ipv4ToNumber(src_ip) AS src_ip_decimal
| backshift src_ip_decimal BY user
| backshift login_time AS previous_login
| where !(isNull(_backshift)) // This filters on users with only a single login or the latest event per user & avoids 'null' error messages
// Convert the decimal back into an IP address
| decToHex(toLong(_backshift)) as src_ip_hex
| parse regex field=src_ip_hex "^(?<o1>[0-9A-Z]{2})(?<o2>[0-9A-Z]{2})(?<o3>[0-9A-Z]{2})(?<o4>[0-9A-Z]{2})"
| concat(hexToDec(o1), ".", hexToDec(o2), ".", hexToDec(o3), ".", hexToDec(o4)) as previous_src_ip
// A geo-lookup for each IP address
| lookup latitude AS lat1, longitude AS long1, country_name AS country_name1, City as City1, State as State1 FROM geo://location ON ip=src_ip
| lookup latitude AS lat2, longitude AS long2, country_name AS country_name2, City as City2, State as State2 FROM geo://location ON ip=previous_src_ip
| where !(isNull(lat1) OR isNull(lat2))
// Calculate the distance between a user's successive logins using the haversine formula
| haversine(lat1, long1, lat2, long2) AS distance_kms
// Calculate the speed a user would have to travel at in order to have done this
| (login_time - previous_login)/3600000 AS login_time_delta_hrs
| where !(login_time_delta_hrs=0)
| distance_kms/login_time_delta_hrs AS apparent_velocity_kph
| where apparent_velocity_kph > 0 // Filter out logins from the same IP
// Specify the impossible speed here (km/hr)
| 500 AS impossible_speed
| where apparent_velocity_kph > impossible_speed
| lookup cn, department, title, manager from shared/ad_users on sam_account_name=user
// Clean it up for presentation
| concat(src_ip,", ",previous_src_ip) AS ip_addresses
| if(country_name1 <> country_name2,concat(country_name1,", ",country_name2),country_name1) AS countries
| fields user, cn, department,title, manager, ip_addresses, countries, City1, State1, City2, State2, distance_kms, login_time_delta_hrs, apparent_velocity_kph
| sort BY apparent_velocity_kph
// EoQ
Please sign in to leave a comment.
Comments
1 comment