1 comment

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

| srcdevice_ip as src_ip
// Create table listing successive logins by user
| count BY login_time, user, src_ip
| where isValidIPv4(src_ip)
| ipv4ToNumber(src_ip) AS src_ip_decimal
| backshift src_ip_decimal BY user
| 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