Query Details
RULE 01 SP Sign In Suspicious Country
Query
// Rule : Workload Identity - Service Principal Sign-in from Suspicious Country
// Severity: High
// Tactics : InitialAccess, CredentialAccess
// MITRE : T1078.004 (Valid Accounts: Cloud Accounts)
// Freq : PT1H Period: PT1H
//==========================================================================================
// ---- Network Allowlist (exclude trusted IPs / CIDR / ranges) --------------
let _allow = materialize(union isfuzzy=true (print R="" | take 0), (_GetWatchlist('NetworkAllowlist') | project R = tostring(IPOrRange)) | where isnotempty(R));
let _allowCIDR = toscalar(_allow | where not(R matches regex @'^\d+\.\d+\.\d+\.\d+-\d+\.\d+\.\d+\.\d+$') | extend R = iff(R has '/', R, strcat(R, '/32')) | summarize make_list(R));
let _allowRange = toscalar(_allow | where R matches regex @'^\d+\.\d+\.\d+\.\d+-\d+\.\d+\.\d+\.\d+$' | summarize make_list(R));
let _ExcludeAllowlistedIPs = (T:(IPAddress:string)) {
T
| extend IPAddress = tostring(IPAddress)
| where array_length(_allowCIDR) == 0 or isnull(ipv4_is_in_any_range(IPAddress, _allowCIDR)) or not(ipv4_is_in_any_range(IPAddress, _allowCIDR))
| mv-apply _r = _allowRange to typeof(string) on (
extend _lo = tostring(split(_r,'-')[0]), _hi = tostring(split(_r,'-')[1])
| extend _inRange = ipv4_compare(IPAddress, _lo) >= 0 and ipv4_compare(IPAddress, _hi) <= 0
| summarize _anyInRange = max(toint(_inRange)))
| where isnull(_anyInRange) or _anyInRange == 0
| project-away _anyInRange
};
// ---------------------------------------------------------------------------
let HighRiskCountries = dynamic([
"CN", "RU", "KP", "IR", "NG", "IQ", "PK", "KZ", "UA", "BY",
"SY", "LY", "YE", "VE", "CU", "ZW", "MM", "AF"
]);
let PrivateRanges = dynamic(["10.", "192.168.", "172.16.", "172.17.", "172.18.",
"172.19.", "172.20.", "172.21.", "172.22.", "172.23.", "172.24.", "172.25.",
"172.26.", "172.27.", "172.28.", "172.29.", "172.30.", "172.31.",
"127.", "169.254.", "168.63."]);
// --- Baseline: countries this SP has historically signed in from ---
let HistoricalCountries = (AADServicePrincipalSignInLogs | invoke _ExcludeAllowlistedIPs())
| where TimeGenerated between (ago(14d) .. ago(1h))
| where ResultType == "0"
| extend GeoInfo = geo_info_from_ip_address(IPAddress)
| extend Country = tostring(GeoInfo.country_iso_code)
| where isnotempty(Country)
| summarize KnownCountries = make_set(Country, 50) by ServicePrincipalId;
// --- Current window ---
(AADServicePrincipalSignInLogs | invoke _ExcludeAllowlistedIPs())
| where TimeGenerated > ago(1h)
| where ResultType == "0"
| where isnotempty(IPAddress)
| where not(IPAddress has_any (PrivateRanges))
| extend GeoInfo = geo_info_from_ip_address(IPAddress)
| extend Country = tostring(GeoInfo.country_iso_code)
| extend City = tostring(GeoInfo.city)
| where Country in (HighRiskCountries)
| join kind=leftouter HistoricalCountries on ServicePrincipalId
| extend IsNewCountry = not(set_has_element(KnownCountries, Country))
| summarize
SigninCount = count(),
UniqueIPs = dcount(IPAddress),
IPList = make_set(IPAddress, 10),
Resources = make_set(ResourceDisplayName, 10),
Countries = make_set(Country, 5),
Cities = make_set(City, 5),
CredTypes = make_set(ClientCredentialType, 5),
IsNewCountry = any(IsNewCountry),
FirstSeen = min(TimeGenerated),
LastSeen = max(TimeGenerated)
by ServicePrincipalName, ServicePrincipalId, AppId
| extend RiskScore = case(
IsNewCountry == true, "Critical",
UniqueIPs > 3, "High",
"Medium")
Explanation
This query is designed to detect potentially suspicious sign-ins by service principals from high-risk countries. Here's a simplified breakdown of what it does:
-
Allowlist Filtering: It first defines a list of trusted IP addresses or ranges (allowlist) and filters out any sign-ins from these trusted sources. This is to focus only on potentially suspicious activities.
-
High-Risk Countries: It specifies a list of countries considered high-risk for security purposes, such as China, Russia, North Korea, and others.
-
Historical Baseline: The query checks the historical sign-in data for each service principal over the past 14 days (excluding the last hour) to establish a baseline of countries from which these service principals have previously signed in successfully.
-
Current Sign-ins: It then examines sign-ins from the last hour, excluding those from private IP ranges, and checks if they originate from any of the high-risk countries.
-
Comparison and Analysis: The query compares current sign-ins against the historical baseline to identify if a service principal is signing in from a new country that wasn't previously recorded.
-
Summary and Risk Assessment: It summarizes the findings, counting the number of sign-ins, unique IPs, and listing the countries and cities involved. It also assesses the risk level:
- "Critical" if the sign-in is from a new country.
- "High" if there are more than three unique IPs involved.
- "Medium" otherwise.
This query helps in identifying unusual or potentially unauthorized access attempts by service principals, which could indicate a security threat.
Details
David Alonso
Released: April 21, 2026
Tables
Keywords
Operators