Skip to main content
Sumo Logic

Lab 5 - Detecting a landspeed violation

Learn how analyze AWS data to detect when there has been unauthorized root account usage, monitor security groups, and logins from two different IP addresses.

In today's society, the user community is mobile and can login from anywhere around the world. Due to this, detecting Landspeed violations is a critical factor. To protect the security your corporate environment early awareness of credentials that have been leaked or phished is vital. 

A landspeed violation is when a user logs in from one location and then logs in again from a different location within too short of a time period to be practical.  For example, a user logs in from the Australia and then just 3 hours later they log in from Brazil, we can detect this using GEO Lookup in an advanced query. 

Screen Shot 2020-08-05 at 6.36.30 PM.png

In this lab you will merge various capabilities that you have previously learned with additional advanced query practices. You will see how code can be designed to flow from one step to the next providing the necessary conversions, applying the haversine distance formula, and tests for proper execution. You will use the operators  backshift, GEO Lookup, ipv4ToNumber, and several mathematical operators - pow, floor, min.

Lab Activity

Flowchart the steps

  1. This landspeed query provides a good example of a best practice approach for longer queries. It can be helpful to represent the approach with the flowchart below. You can organize it into steps and take it one section at a time. Start a new Log Search Query for a 24 hour period and copy the comments on the right into the query builder. We are using a Last 24 hours for our data because we want to catch any potential actors over a substantial amount of time. The shorter the time the less data and we might miss important logins/access.

Query Flow Chart Query Comments
Screen Shot 2020-08-05 at 7.34.48 PM.png


// Select our _sourceCategory and parse the user and their ip address

/* Filter out where a valid public IP address and create the start of when 
a user logs in at their IP address and then look across all the times that that user has logged in from the same IP Address */

// 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

// A geo-lookup for each IP address filter on only legitimate values returned

// Calculate the distance between a user's successive logins using the haversine formula 

// Calculate the speed a user would have to travel at in order to have traveled that distance 

// Filter out logins from the same IP 

// Specify the impossible speed here (km/hr) 

// Format for presentation 

Scope, parse, and validate ip address login

  1. First you will use source category to point to the incoming log for AWS CloudTrail. Then you will use JSON to parse the user identity and their ip address.  You define user identity as user and ip address as ip.  You will use nodrop so that even if the incoming logs do not have a user identity or ip address they will still show up in your results. Nodrop is a best practice to avoid losing any data. This should result in the following query:

// Select our _sourceCategory and parse the user and their ip address
_sourceCategory=Labs/AWS/CloudTrail
| json "userIdentity.userName","sourceIPAddress" as user, ip nodrop

  1. To be able to filter, you will create a parameter for actor. For the query to execute properly you will need an asterisk inserted for the actor parameter. You will find the first time each user logged in and what public IP address they had.  And you will will sort by user and their login time.  This should result in the following query:

/* Filter out where a valid public IP address and create the start of when 
a user logs in at their IP address and then look across all the times that that user has logged in from the same IP Address */

| where user matches {{actor}}
| where isPublicIP(ip)
| min(_messagetime) AS login_time BY user, ip
| sort BY user, +login_time

 

Your results should look like this:


clipboard_edd806b563758e8b8a35f0ad56f896325.png 

Filter non-exsitent previous IP and convert 

  1. You will convert the IP address into a number, and display the previous ip address where the user was logged in from and display the previous login time.  For this you will need to ip4ToNumber and the backshift operator. Finally you will filter out any non-existent previous records to avoid miscalculations using a where filter.  You will use the backshift operator to allow the comparison between a field between 2 consecutive rows from a result set.  This operator compares values as they change over time. You query should look like this: 

// This filters on users with only a single login or the latest event per user & avoids 'null' error messages
| ipv4ToNumber(ip) AS ip_decimal
| backshift ip_decimal BY user
| backshift login_time AS previous_login 
| where !(isNull(_backshift))

  1. Using toInt, you will convert the previous ip addresses from decimal form back to the specific ip address integers called octets (for example, 123.3.45.921 has 4 octets 123, 3, 45, and 921) of the previous ip address using the formula listed in this query. Take note of the use of mathmatical operators floor, pow. You can add this to the bottom of your query: 

// Convert the decimal back into an IP address
| toInt(floor(_backshift/pow(256,3))) AS octet1 | toInt(floor((_backshift-octet1*pow(256,3))/pow(256,2))) AS octet2 | toInt(floor((_backshift-(octet1*pow(256,3)+octet2*pow(256,2)))/256)) AS octet3 | toInt(_backshift-(octet1*pow(256,3)+octet2*pow(256,2)+octet3*256)) AS octet4 | concat(octet1,".",octet2,".",octet3,".",octet4) AS previous_ip 
 

Geo lookup tables and calculate the speed

  1. You are ready to perform a Geo Lookup for both the current and previous ip addresses. You will need to retrieve latitude, longitude and country_name.  You should add this to the end of your query: 

    // A geo-lookup for each IP address 
    | lookup latitude AS lat1, longitude AS long1, country_name AS country_name1 FROM geo://location ON ip 
    | lookup latitude AS lat2, longitude AS long2, country_name AS country_name2 FROM geo://location ON ip=previous_ip 



    Your results should look like this:

    Screen Shot 2020-08-07 at 9.59.18 PM.png
  2. You are now ready to calculate the distance between the two coordinates using the Haversine function. Screen Shot 2020-08-07 at 10.06.15 PM.pngThis function determines the distance between two different latitudes and longitudes in kilometers taking into account our spherical world. Add this to your query:

// Calculate the distance between a user's successive logins using the haversine formula 
| haversine(lat1, long1, lat2, long2) AS distance_kms 

  1. Now you will calculate the speed a user would have to travel (aparent_velocity_kph) in order to do this.  You take the time change between logins, convert them to hours using 3600000 seconds per hour, and divide by the distance you just calculated. Seconds cancel and you get kph. Add this to your query:

// Calculate the speed a user would have to travel at in order to have traveled that distance 
| (login_time - previous_login)/3600000 AS login_time_delta_hrs 
| distance_kms/login_time_delta_hrs AS apparent_velocity_kph 
| where apparent_velocity_kph > 0

Detect for further investigation

  1. You realize that 500 km/hour is difficult and at least suspicious enough to be further investigated, and you should monitor this user.  So you will add to your query this value and then filter values against it as shown below:

// Specify the suspicious speed here (km/hr) 
| 500 AS suspicious_speed 
| where apparent_velocity_kph > suspicious_speed 

  1. Using formatting techniques, you are ready to clean up the results.  You notice that you might have two different countries represented or just one.  If you have two, you will concatenate them together as a string, using our concat operator.  If there is one, you only will display the country once. Change the order using the fields operator to display the user, both ip addresses, countries, the distance in kilometers, difference of login times, apparent velocity in KPH, and sort by apparent velocity in KPH. 


// Format for presentation 
| concat(ip,", ",previous_ip) AS ip_addresses 
| if(country_name1 <> country_name2,concat(country_name1,", ",country_name2),country_name1) AS countries 
| fields user, ip_addresses, countries, distance_kms, login_time_delta_hrs,apparent_velocity_kph 
| where !isNull(user)
| where apparent_velocity_kph != "Infinity"
| sort by apparent_velocity_kph 

  1. Your final query should look like this: 

// Select our _sourceCategory and parse the user and their ip address
_sourceCategory=Labs/AWS/CloudTrail
| json "userIdentity.userName","sourceIPAddress" as user, ip nodrop


/* Filter out where a valid public IP address and create the start of when 
a user logs in at their IP address and then look across all the times that that user has logged in from the same IP Address */

| where user matches {{actor}}
| where isPublicIP(ip)
| min(_messagetime) AS login_time BY user, ip 
| sort BY user, +login_time


// This filters on users with only a single login or the latest event per user & avoids 'null' error messages

| ipv4ToNumber(ip) AS ip_decimal
| backshift ip_decimal BY user
| backshift login_time AS previous_login 
| where !(isNull(_backshift))

// Convert the decimal back into an IP address
| toInt(floor(_backshift/pow(256,3))) AS octet1 | toInt(floor((_backshift-octet1*pow(256,3))/pow(256,2))) AS octet2 | toInt(floor((_backshift-(octet1*pow(256,3)+octet2*pow(256,2)))/256)) AS octet3 | toInt(_backshift-(octet1*pow(256,3)+octet2*pow(256,2)+octet3*256)) AS octet4 | concat(octet1,".",octet2,".",octet3,".",octet4) AS previous_ip 

// A geo-lookup for each IP address filter on only legitimate values returned
| lookup latitude AS lat1, longitude AS long1, country_name AS country_name1 FROM geo://location ON ip 
| lookup latitude AS lat2, longitude AS long2, country_name AS country_name2 FROM geo://location ON ip=previous_ip 
|where !(isNull(lat1))
|where !(isNull(long1))
|where !(isNull(lat2))
|where !(isNull(long2))

// 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 traveled that distance 
| (login_time - previous_login)/3600000 AS login_time_delta_hrs 
| distance_kms/login_time_delta_hrs AS apparent_velocity_kph 
| where apparent_velocity_kph > 0

// Specify the suspicious speed here (km/hr) 
| 500 AS suspicious_speed 
| where apparent_velocity_kph > suspicious_speed 

// Format for presentation 
| concat(ip,", ",previous_ip) AS ip_addresses 
| if(country_name1 <> country_name2,concat(country_name1,", ",country_name2),country_name1) AS countries 
| fields user, ip_addresses, countries, distance_kms, login_time_delta_hrs,apparent_velocity_kph 
| where !isNull(user)
| where apparent_velocity_kph != "Infinity"
| sort by apparent_velocity_kph 

 

  1. Your results should look like this:

clipboard_e032bbd08119e1cc86423936ff9c6184e.png

Add the panel to the dashboard

  1. Now we have the data we want that we want to add to our existing dashboard.  Let's add this to the dashboard you created to your SOC_<your intials###> dashboard, by clicking Add to Dashboard button in the aggregates tab. Use the following panel name, Landspeed Violations in the popup window.


Screen Shot 2020-08-05 at 6.16.41 PM.png

  1. This popup appears click Add Panel. The reason why you get the Filter Already exists is because other panels in your starter SOC dashboard have a matching parameter field.

Screen Shot 2020-07-29 at 2.21.09 PM.png

  1. Now you have created three panels for your starter SOC_<your initials###> dashboard. Your dashboard should look like the one below.clipboard_e9bbfdc0ff22bac05ea3256035dee4268.png

Quiz (True or False?)

  1. Geo lookup receives an ip address and returns location.

  2. The haversine formula was used to convert octets to decimals.

  3. Landspeed violations allow you to detect suspicious user activity.

Summary

Congratulations! You’ve completed these tasks:

  1. You learned how to format with fields.

  2. You will use the operators  backshift, GEO Lookup, ipv4ToNumber

  3. You learned several mathematical operators - pow, floor, min