Query Details
RULE 08 SP Created Used High Risk Country
Query
// Rule : Workload Identity - SP Created and Used Same Day from High-Risk Country
// Severity: High
// Tactics : Persistence, InitialAccess
// MITRE : T1136.003 (Create Cloud Account), T1078.004
// Freq : PT6H Period: P1D
//==========================================================================================
// ---- 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", "BY", "SY", "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."]);
// --- Service principals created in the last 24 hours ---
let NewSPs = AuditLogs
| where TimeGenerated > ago(1d)
| where OperationName =~ "Add service principal"
| where Result =~ "success"
| extend SPId = tostring(TargetResources[0].id)
| extend SPName = tostring(TargetResources[0].displayName)
| extend AppId = tostring(TargetResources[0].modifiedProperties[0].newValue)
| extend Creator = coalesce(tostring(InitiatedBy.user.userPrincipalName),
tostring(InitiatedBy.app.displayName))
| project CreatedTime = TimeGenerated, SPId, SPName, Creator;
// --- Sign-ins by those newly created SPs ---
(AADServicePrincipalSignInLogs | invoke _ExcludeAllowlistedIPs())
| where TimeGenerated > ago(1d)
| 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)
| where Country in (HighRiskCountries)
| join kind=inner NewSPs on $left.ServicePrincipalId == $right.SPId
| where TimeGenerated >= CreatedTime
| summarize
SigninCount = count(),
UniqueIPs = dcount(IPAddress),
IPList = make_set(IPAddress, 5),
Countries = make_set(Country, 5),
Resources = make_set(ResourceDisplayName, 10),
MinutesAfterCreate = min(datetime_diff("minute", TimeGenerated, CreatedTime)),
FirstSignin = min(TimeGenerated),
LastSignin = max(TimeGenerated)
by ServicePrincipalName, ServicePrincipalId, AppId, Creator, CreatedTime
Explanation
This query is designed to detect potentially suspicious activity involving newly created service principals (SPs) in a cloud environment. Here's a simplified breakdown of what the query does:
-
Network Allowlist: It first defines a list of trusted IP addresses or ranges (allowlist) that should be excluded from further analysis. This is done by retrieving a watchlist of trusted networks and processing it to handle both CIDR notation and IP ranges.
-
High-Risk Countries: It specifies a list of countries considered high-risk, such as China (CN), Russia (RU), North Korea (KP), and others.
-
Private IP Ranges: It defines a list of private IP address ranges that should be excluded from analysis.
-
New Service Principals: It identifies service principals that were created successfully within the last 24 hours. For each new service principal, it captures details such as the creation time, ID, name, and creator.
-
Sign-ins by New Service Principals: It looks for sign-in attempts by these newly created service principals within the last 24 hours. The query:
- Excludes sign-ins from allowlisted IPs.
- Filters out unsuccessful sign-ins and those from private IP ranges.
- Retrieves geographical information from the IP addresses and checks if the sign-ins originated from high-risk countries.
- Joins this sign-in data with the list of newly created service principals to find matches.
-
Summary of Findings: For each service principal that matches the criteria, it summarizes the findings, including:
- The number of sign-in attempts.
- The number of unique IP addresses used.
- A list of up to five IP addresses and countries involved.
- A list of up to ten resources accessed.
- The time difference in minutes between the creation of the service principal and its first sign-in.
- The timestamps of the first and last sign-in attempts.
Overall, this query aims to identify and highlight service principals that were created and used for sign-ins from high-risk countries on the same day, which could indicate a security threat such as unauthorized access or persistence tactics.
Details
David Alonso
Released: April 21, 2026
Tables
Keywords
Operators