Geo Impossible Logins: Detecting Credential Theft in Splunk

Earlier this year I attended the Educause Security Professional Conference in St. Louis. I went to a session at which Nick Hannon from Swarthmore College explained how Splunk could combine MaxMind GeoIP data with authentication logs to detect credential theft by looking for geo impossible logins. I couldn’t find an exact tutorial online, so this is my execution of his idea. I based much of the syntax on another Splunk report I found here.

This post contains affiliate links, which means I may receive a small commission, at no extra cost to you, if you make a purchase using these links.

First we will need to get CAS sending its authentication logs to Splunk, see this post for details.

Next we can use the MaxMind database included in the Google Maps app for Splunk. Get that here: http://apps.splunk.com/app/368/. This will give us access to the “geoip” command which provides (among other things) the longitude and latitude of an IP address.

We will also need the Haversine app for Splunk which is available here: http://apps.splunk.com/app/936/. The Haversine algorithm is used to determine the shortest distance between two points on a sphere. This app takes a source latitude and longitude and a destination latitude and longitude and gives a distance in miles.

By noting the change in IP address for successful logins for each user and by calculating the distance in between we can find how quickly the user “travelled” by dividing the distance by the time delta. Anyone who has traveled faster than 450 MPH should be considered suspicious. Here is how the query developed.

Starting at the most basic, I selected every TICKET_GRANTING_TICKET_CREATED, grabbed the GeoIP of the client IP, and sorted by time.

index=authentication TICKET_GRANTING_TICKET_CREATED 
     client_ip=* |sort - _time |geoip client_ip

Next, I renamed the some of the GeoIP output for ease of use, and I filtered out anything with null coordinates.

index=authentication TICKET_GRANTING_TICKET_CREATED client_ip=* 
    |sort - _time |geoip client_ip 
    |rename client_ip_latitude as "lat"
    |rename client_ip_longitude as "long" 
    |rename client_ip_country_code as "country"
    |rename client_ip_region_name as "state"
    |where isnotnull(lat)|rename _time as time

Then, I ran the output through statstream by username to catch IP changes per user.

index=authentication TICKET_GRANTING_TICKET_CREATED client_ip=* 
    |sort - _time |geoip client_ip 
    |rename client_ip_latitude as "lat"
    |rename client_ip_longitude as "long" 
    |rename client_ip_country_code as "country"
    |rename client_ip_region_name as "state"
    |where isnotnull(lat)|rename _time as time
    |streamstats current=f global=f window=1 first(lat) as next_lat
     first(long) as next_long first(time) as next_time first(client_ip)
     as next_ip first(country) as next_country first(state) as
     next_state by username

After that, I ran the before and after coordinates through Haversine to find the distance between the two IP addresses in miles.

index=authentication TICKET_GRANTING_TICKET_CREATED client_ip=* 
    |sort - _time |geoip client_ip 
    |rename client_ip_latitude as "lat"
    |rename client_ip_longitude as "long" 
    |rename client_ip_country_code as "country"
    |rename client_ip_region_name as "state"
    |where isnotnull(lat)|rename _time as time
    |streamstats current=f global=f window=1 first(lat) as next_lat
     first(long) as next_long first(time) as next_time first(client_ip)
     as next_ip first(country) as next_country first(state) as
     next_state by username
    |strcat lat "," long pointA 
    |haversine originField=pointA units=mi inputFieldLat=next_lat 
     inputFieldLon=next_long outputField=distance_miles 
    |strcat next_lat "," next_long  pointB

Then, I calculated the time delta in hours.

index=authentication TICKET_GRANTING_TICKET_CREATED client_ip=* 
    |sort - _time |geoip client_ip 
    |rename client_ip_latitude as "lat"
    |rename client_ip_longitude as "long" 
    |rename client_ip_country_code as "country"
    |rename client_ip_region_name as "state"
    |where isnotnull(lat)|rename _time as time
    |streamstats current=f global=f window=1 first(lat) as next_lat
     first(long) as next_long first(time) as next_time first(client_ip)
     as next_ip first(country) as next_country first(state) as
     next_state by username
    |strcat lat "," long pointA 
    |haversine originField=pointA units=mi inputFieldLat=next_lat 
     inputFieldLon=next_long outputField=distance_miles 
    |strcat next_lat "," next_long pointB
    |eval time_dif=(((next_time - time)/60)/60)

Finally, I filtered out distances less than 1200 miles since they was causing a lot of false positives due to the Maxmind database not being entirely accurate, then determined speed (distance over time), filtered anything slower than 450 miles per hour, and then formatted the output.

index=authentication TICKET_GRANTING_TICKET_CREATED client_ip=* 
    |sort - _time |geoip client_ip 
    |rename client_ip_latitude as "lat"
    |rename client_ip_longitude as "long" 
    |rename client_ip_country_code as "country"
    |rename client_ip_region_name as "state"
    |where isnotnull(lat)|rename _time as time
    |streamstats current=f global=f window=1 first(lat) as next_lat
     first(long) as next_long first(time) as next_time first(client_ip)
     as next_ip first(country) as next_country first(state) as
     next_state by username
    |strcat lat "," long pointA 
    |haversine originField=pointA units=mi inputFieldLat=next_lat 
     inputFieldLon=next_long outputField=distance_miles 
    |strcat next_lat "," next_long pointB
    |eval time_dif=(((next_time - time)/60)/60)
    |where distance_miles>1200|eval speed=ceil(distance_miles/time_dif)
    |table client_ip state country next_ip next_state next_country 
     distance_miles time_dif username speed 
    |where speed>450

As a result I got a report of anyone travelling faster than 450 miles per hour that looked like this:

This led me to consider some reasons why people may end up on this report.

• A compromised account
• Bad GeoIP data in the database
• Use of a VPN, proxy, tor, etc.
• Credential sharing

Still I feel it provides worth while information about user logins on CAS. Especially when combined with a search like this to see user’s login history:

index=authentication TICKET_GRANTING_TICKET_CREATED username=$user$ 
     client_ip=* |geoip client_ip |table _time username client_ip* 
    |sort - _time

Geo impossible logins are a hard signal to work with. There are so many factors creating false positives, from VPNs and outdated data, to shared accounts. But this is a good start to bolstering your account takeover detection game.

If you want to level up your Splunk skills take a look at this course on Udemy: Splunk: Zero to Power User by Hailie Shaw.