KQL Search

Query Details

HomeAI ToolsDevice Query

Sign In Attempts From Multiple Countries

Query

**Success sign-in from more than 3 countries in one day based on the Latitude and Longitude distance among them**

**Description:** A while ago, I was annoyed with some Defender XDR alerts related to "User Impossible travel". I had different false positives,
users who were using VPN, different devices, different countries on the same day (not a surprise if you live in central Europe)
and others. So, I decided to create a query to find cases where the user success logins during the same day are from countries 
that are really distant.
The query checks the Longitude and Latitude difference of the first 4 countries, if I have more than 4 countries I will also be
notified. Sorry if there is any error with the calculation, I was (and I am) very bad at math :P 

```
let substring = ",";
AADSignInEventsBeta
| where Timestamp > ago(1d)
| where ErrorCode == 0
| where isnotempty(Country)
| project AccountUpn, Timestamp, ClientAppUsed, Country, Latitude, Longitude, ReportId, DeviceTrustType
| summarize ['Count of countries']=dcount(Country), ['List of countries']=make_set(Country), ['ListofLatitudes']=make_set(Latitude),
 ['ListofLongitudes']=make_set(Longitude) by AccountUpn, DeviceTrustType
 | where ['Count of countries'] >= 3
 // | where DeviceTrustType !contains "Azure AD registered"
| project splitted=split(ListofLatitudes, '"'),splitted1=split(ListofLongitudes, '"'), ['List of countries'], AccountUpn, ['Count of countries']
//split Latitude and transform it output (if you want to add more countries, add Lat(+1)= splitted[+2] From the last, example --> Lat5 = splitted[9] )
| mv-expand Lat1=splitted[1], Lat2=splitted[3], Lat3=splitted[5], Lat4= splitted[7]
| extend Lat1 =todouble(Lat1), Lat2 = todouble(Lat2), Lat3 = todouble(Lat3), Lat4 = todouble(Lat4)
| extend Lat1 = round(Lat1), Lat2 = round(Lat2), Lat3 = round(Lat3), Lat4 = round(Lat4)
//split Longitude and transform it output (if you want to add more countries, add Long(+1)= splitted[+2] From the last, example --> Long = splitted[9])
| mv-expand Long1=splitted1[1], Long2=splitted1[3], Long3=splitted1[5], Long4= splitted1[7]
| extend Long1 =todouble(Long1), Long2 = todouble(Long2), Long3= todouble(Long3), Long4 = todouble(Long4)
| extend Long1 = round(Long1), Long2 = round(Long2), Long3 = round(Long3), Long4 = round(Long4)
// susbstract operations
| serialize resta = Lat1 - Lat2, resta2 = Lat1 - Lat2, resta3 = Lat2 - Lat3, resta4 = Lat1 - Lat4
| serialize restal = Long1 - Long2, restal2 = Long1 - Long3, restal3 = Long2 - Long3
// Calculate the distance, add more than 15 or 20 to see more distant countries
| where (resta > 15 and resta2 > 15 and resta3> 20 and Lat1 != Lat2 and Lat1!= Lat2 and Lat2!= Lat3) or (resta < -20 and resta2 < -15 and resta3 < -15) or (restal > 20 and restal2 > 20 and restal3> 20 and Long1 != Long2 and Long1!= Long2 and Long2!= Long3) or (restal < -20 and restal2 < -20 and restal3 < -20) or (['Count of countries'] >4)
| project AccountUpn,['List of countries']
```

Explanation

This KQL query is designed to identify users who have successfully signed in from more than three different countries within a single day, where the countries are significantly distant from each other based on their latitude and longitude coordinates. Here's a simplified breakdown of what the query does:

  1. Filter Sign-In Events:

    • It looks at sign-in events from the past day (Timestamp > ago(1d)).
    • It only considers successful sign-ins (ErrorCode == 0).
    • It ensures that the country information is available (isnotempty(Country)).

  2. Project Relevant Fields:

    • It selects relevant fields like AccountUpn, Timestamp, ClientAppUsed, Country, Latitude, Longitude, ReportId, and DeviceTrustType.

  3. Summarize Data:

    • It groups the data by user (AccountUpn) and device trust type (DeviceTrustType).
    • It counts the number of distinct countries each user has signed in from and creates lists of these countries, latitudes, and longitudes.

  4. Filter Users with Multiple Countries:

    • It filters the results to include only users who have signed in from at least three different countries in one day.

  5. Split and Transform Latitude and Longitude:

    • It splits the lists of latitudes and longitudes into individual values.
    • It converts these values to double precision numbers and rounds them for easier comparison.

  6. Calculate Differences:

    • It calculates the differences between the latitudes and longitudes of the first four countries to determine the distance between them.

  7. Identify Distant Countries:

    • It applies conditions to check if the differences in latitudes and longitudes are significant (greater than 15 or 20 degrees).
    • It also checks if the user has signed in from more than four countries.

  8. Output:

    • It projects the final output, showing the user (AccountUpn) and the list of countries they signed in from.

In summary, this query helps identify potential suspicious activity by flagging users who have signed in from multiple, geographically distant countries within a single day, which could indicate unusual behavior such as account compromise or misuse of VPNs.

Details

Sergio Albea profile picture

Sergio Albea

Released: July 4, 2024

Tables

AADSignInEventsBeta

Keywords

UserDevicesCountriesLatitudeLongitudeTimestampAccountUpnClientAppUsedDeviceTrustTypeReportIdErrorCode

Operators

letsubstringAADSignInEventsBeta|where>ago==isnotemptyprojectsummarizedcountmake_setby>=splitmv-expandextendtodoubleroundserialize-or

Actions