SigninLogs — First New Country Sign-In for Privileged Account (Deterministic)
20 SIGNIN New Country Privileged Admin
Query
// ---- 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 lookback = 6h;
let baselinePeriod = 14d;
let privilegedKeywords = dynamic([
"admin", "adm-", "adm.", "-adm", "_adm",
"da-", "ea-", "ga-", "pa-",
"tier0", "tier-0", "t0-",
"priv", "svc.adm", "svc-adm",
"globaladmin", "secadmin", "cloudadmin"
]);
let KnownAdminLocations =
SigninLogs
| invoke _ExcludeAllowlistedIPs()
| where TimeGenerated between (ago(baselinePeriod) .. ago(lookback))
| where ResultType == "0"
| where UserPrincipalName has_any (privilegedKeywords)
| where isnotempty(Location) and Location != "Unknown"
| distinct UserPrincipalName, Location;
SigninLogs
| invoke _ExcludeAllowlistedIPs()
| where TimeGenerated > ago(lookback)
| where ResultType == "0"
| where UserPrincipalName has_any (privilegedKeywords)
| where isnotempty(Location) and Location != "Unknown"
| join kind=leftanti KnownAdminLocations on UserPrincipalName, Location
| project
TimeGenerated, UserPrincipalName,
IPAddress, Location, AppDisplayName,
RiskLevel = RiskLevelDuringSignIn, ConditionalAccessStatus,
IsInteractiveExplanation
This query is designed to detect when privileged accounts sign in from a country that hasn't been seen in their login history over the past 30 days. It is a deterministic rule, meaning it doesn't rely on machine learning and doesn't require Entra ID P2. It specifically targets privileged accounts, which are at a higher risk of being compromised. The query is scheduled to run every 6 hours and looks back over a 14-day period.
Here's a simplified breakdown of what the query does:
-
Network Allowlist: It excludes trusted IP addresses or ranges from the analysis to focus only on potentially suspicious activity.
-
Lookback and Baseline Periods: It defines a 6-hour lookback period and a 14-day baseline period to identify new sign-in locations.
-
Privileged Keywords: It identifies privileged accounts by looking for specific keywords in the usernames, such as "admin," "globaladmin," and others.
-
Known Admin Locations: It creates a list of known locations from which privileged accounts have signed in successfully in the past 14 days.
-
New Sign-In Detection: It checks for successful sign-ins from locations not in the known list during the lookback period.
-
Alert Generation: If a new country sign-in is detected, it generates an alert with details like the time, user, IP address, location, application name, risk level, and conditional access status.
-
Incident Configuration: It creates an incident for each alert and groups incidents by account, but does not reopen closed incidents.
Overall, this query helps security teams quickly identify and investigate potentially compromised privileged accounts based on unusual sign-in locations.
Details
David Alonso
Released: April 20, 2026
Tables
Keywords
Operators