Query Details
Accounts Under Attack From Multiple Countries
Query
//This query identifies accounts that haven't changed their password in over a year and shows all countries attempting to access them
//It helps demonstrate the security risk of outdated passwords by showing the global attack surface
//The query also includes (commented out) an option to filter for single-factor authentication cases
AADSignInEventsBeta
| where Timestamp > ago(90d)
| where isnotempty(LastPasswordChangeTimestamp)
// Calculate the period from today until the last time password changed
| extend DaysSinceLastPasswordChange = datetime_diff('day', now(), LastPasswordChangeTimestamp)
// select the cases where the Last Password days is more than 1 year
| where DaysSinceLastPasswordChange >364
//create a data_set with all the countries from where are trying to take access to it account
| summarize CountryList=make_set(Country), count() by AccountUpn, AccountDisplayName, DaysSinceLastPasswordChange, AuthenticationRequirement
// count the distinct countries
| extend totalcountries = array_length(CountryList)
// | where AuthenticationRequirement contains "single" Explanation
This KQL query is designed to identify user accounts that haven't had their passwords changed in over a year and to list all the countries from which there have been attempts to access these accounts. Here's a simple breakdown of what the query does:
-
Data Source: It starts by looking at sign-in events from the
AADSignInEventsBetatable, focusing on the last 90 days. -
Filter for Relevant Data: It filters out records where the
LastPasswordChangeTimestampis empty, ensuring it only considers accounts with a recorded password change date. -
Calculate Days Since Last Password Change: It calculates how many days have passed since the last password change for each account.
-
Identify Outdated Passwords: It filters the results to include only those accounts where the password hasn't been changed in more than 364 days (over a year).
-
Summarize Access Attempts by Country: For each account, it creates a list of countries from which there have been access attempts, along with a count of these attempts.
-
Count Distinct Countries: It adds a column showing the total number of distinct countries attempting to access each account.
-
Optional Filter for Single-Factor Authentication: There is a commented-out line that, if activated, would further filter the results to include only cases where single-factor authentication was used.
Overall, this query highlights potential security risks by showing how widely exposed accounts with outdated passwords are to global access attempts.
Details
Sergio Albea
Released: November 10, 2024
Tables
Keywords
Operators