Skip to main content
Sumo Logic

Lab 6 - Azure Landspeed Violation(s) by User

Explore the functionality of the backshift, haversine and other operators in a security query.

In today's society, the user community is mobile and can  login from anywhere around the globe. Detecting landspeed violations is a critical factor to protect the security your corporate environment.  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. 

In this lab you will merge various capabilities that you have previously learned with additional advanced query practices. You will use these operators  backshift, GEO Lookup, ip4toNumber, and several mathematical operators - pow, floor, min

 

Create the Landspeed Violations by User query.

 

  1. Because this query is going to take a bit of work, it might be helpful to add comments in our search query to help understand the query elements that you will need to write.  Start a new Log Search Query  for a 24 hour period and add the following comments in: 

// select our _sourceCategory and parse the user and their ip address
// filter out where there is no IP address and create the start of when 
// user logs in and their IP address and look across the times that that user hass logged in and from the 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 

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

// Filter out logins from the same IP 
// Specify the impossible speed here (km/hr) 

// Clean it up for presentation 

  1. For this first part of this, you will need to select the Azure AD sourceCategory and parse the identity and callerIpAddress.  You want to refer to identity as user and callerIpAddress as ip.  You do NOT want to filter any log information that does NOT have an callerIpAddress or identity, by adding nodrop to your parse statement.  Finally you do want to filter out any blank ip addresses.   This should result in the following query:

    // select our _sourceCategory and parse the user and their ip address
    // filter out where there is no IP address and create the start of when 
    // user logs in and their IP address and look across the times that that user hass logged in and from the IP Address

    _sourceCategory=Labs/Azure/AD
    | json "identity","callerIpAddress" as user, ip nodrop 
    | where ip != "<null>"

  2. You want to find the first time each user logged in and what IP address they had.  And you will want to sort by user and their message time.  This should result in the following query:

    // select our _sourceCategory and parse the user and their ip address
    // filter out where there is no IP address and create the start of when 
    // user logs in and their IP address and look across the times that that user hass logged in and from the IP Address

    _sourceCategory=Labs/Azure/AD
    | json "identity","callerIpAddress" as user, ip nodrop 
    | where ip != "<null>"
    | min(_messagetime) AS login_time BY user, ip 
    | sort BY user, +login_time


    Your results should look like this:
    clipboard_edd806b563758e8b8a35f0ad56f896325.png 

  3. You now want to convert the IP address into a number,  and display the previous ip address where the user was logged in from and display previous login time.  For this you will need to ip4ToNumber and the backshift operator..  Finally you want to filter where there is no previous record.  You will use the backshift operator for the first time.  This operator compares values as they change over time. You query should look like this: 

    // select our _sourceCategory and parse the user and their ip address
    // filter out where there is no IP address and create the start of when 
    // user logs in and their IP address and look across the times that that user hass logged in and from the IP Address

    _sourceCategory=Labs/Azure/AD
    | json "identity","callerIpAddress" as user, ip nodrop 
    | where ip != "<null>"
    | min(_messagetime) AS login_time BY user, ip 
    | sort BY user, +login_time 
    | ipv4ToNumber(ip) AS ip_decimal
    | backshift ip_decimal BY user
    | backshift login_time AS previous_login 
    | where !(isNull(_backshift))

  4. Now you want to convert the previous ip addresses to the specific elements called octets of the previous ip address using the formula listed in this query. You can add this to the bottom of your query: 

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

    | 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 


    Your results should look like this:
     
    clipboard_ec559fb3474b642d8cecf4372706cd9dd.png

  5. You are now ready to do the next section of comments where we need to do a Geo Lookup of the IP address for our current IP address and the Previous IP Address.  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 

  6. You are now ready to calculate the distance between the two coordinates using the Haversine function. This function determines the distance between two different latitudes and longitudes in kilometers. 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 

  7. Now you will want to calculate the speed a user would have to travel in order to do this.  Add this to your query:
    // 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 
    | distance_kms/login_time_delta_hrs AS apparent_velocity_kph 
    | where apparent_velocity_kph > 0

  8. You realize that 500 km/hour is an impossible and impractical speed.  So you will add to your query this value and then filter values against it as shown below:
    // Filter out logins from the same IP 
    // Specify the impossible speed here (km/hr) 
    | 500 AS impossible_speed 
    | where apparent_velocity_kph > impossible_speed 

     

  9. You are now ready to clean up the results.  You notice that you might have two different countries represented or just one.  If you have two, you want to concatenate them together as a string.  If there is one, you only want to display the country once. Change the order using the Fields operator to display the User, both the IP addresses, countries and the distance in kilometers, difference of login times, apparent velocity in KPH and sort by apparent velocity in KPH.  You final query should look like this: 

    // select our _sourceCategory and parse the user and their ip address
    // filter out where there is no IP address and create the start of when 
    // user logs in and their IP address and look across the times that that user hass logged in and from the IP Address

    _sourceCategory=Labs/Azure/AD
    | json "identity","callerIpAddress" as user, ip nodrop 
    | where ip != "<null>"
    | min(_messagetime) AS login_time BY user, ip 
    | sort BY user, +login_time 
    | ipv4ToNumber(ip) AS ip_decimal
    | backshift 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

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

    // 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 
    | 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 
    // 
    // Clean it up 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 
    | sort BY apparent_velocity_kph



    Your results should look like this:
    clipboard_e032bbd08119e1cc86423936ff9c6184e.png

  10. Save this query as Landspeed Violations and publish this to your existing dashboard and we will organize this dashboard in the next lab.
    clipboard_e5d087910fa64c86dbbe342b7b33e0302.png