How to find a user logging in from 2 different countries


1 comment

  • Avatar
    Shane Phillips

    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

    Comment actions Permalink

Please sign in to leave a comment.