Query Details
HUNT 01 SP Suspicious IP Country Timeline
Query
// Hunt : Workload Identity - Service Principal Sign-ins from Suspicious IPs and Countries (7d)
// Tactics : InitialAccess, CredentialAccess
// MITRE : T1078.004
// Purpose : Full geo-analysis of SP sign-in IP activity. Surfaces SPs authenticating from high-risk
// countries and private/RFC1918 ranges (lateral movement via internal IPs). Use to baseline
// normal SP geolocations before tuning RULE-01.
//==========================================================================================
let HighRiskCountries = dynamic([
"CN", "RU", "KP", "IR", "NG", "IQ", "PK", "KZ", "UA", "BY",
"SY", "LY", "YE", "VE", "CU", "ZW", "MM", "AF"
]);
(AADServicePrincipalSignInLogs | invoke ExcludeAllowlistedIPs())
| where TimeGenerated > ago(7d)
| where ResultType == "0"
| extend GeoInfo = geo_info_from_ip_address(IPAddress)
| extend Country = tostring(GeoInfo.country_iso_code)
| extend City = tostring(GeoInfo.city)
| extend Latitude = toreal(GeoInfo.latitude)
| extend Longitude = toreal(GeoInfo.longitude)
| extend IsHighRisk = Country in (HighRiskCountries)
| extend IsPrivateRange = IPAddress startswith "10." or IPAddress startswith "172."
or IPAddress startswith "192.168."
| summarize
AuthCount = count(),
UniqueIPs = dcount(IPAddress),
Countries = make_set(Country, 20),
Cities = make_set(City, 10),
HighRiskAuths = countif(IsHighRisk),
PrivateRangeAuths = countif(IsPrivateRange),
IPList = make_set(IPAddress, 20),
Resources = make_set(ResourceDisplayName, 10),
CredTypes = make_set(ClientCredentialType, 5),
FirstSeen = min(TimeGenerated),
LastSeen = max(TimeGenerated)
by ServicePrincipalName, ServicePrincipalId, AppId
| order by HighRiskAuths desc, AuthCount desc
Explanation
This query is designed to analyze the sign-in activity of service principals (SPs) in Azure Active Directory over the past seven days. It focuses on identifying potentially suspicious sign-ins based on geographic location and IP address characteristics. Here's a simplified breakdown of what the query does:
-
High-Risk Countries: It defines a list of countries considered high-risk for security purposes, such as China, Russia, and North Korea.
-
Data Source: The query examines the Azure Active Directory Service Principal Sign-In Logs, excluding any IPs that are on an allowlist.
-
Time Frame: It only considers sign-in attempts that occurred within the last seven days.
-
Successful Sign-Ins: It filters for successful sign-ins (where the result type is "0").
-
Geolocation Information: For each sign-in, it retrieves geolocation information based on the IP address, including the country, city, latitude, and longitude.
-
Risk Assessment:
- High-Risk Countries: It checks if the sign-in originated from one of the high-risk countries.
- Private IP Ranges: It checks if the IP address falls within private IP ranges (commonly used for internal networks).
-
Summary Statistics: It summarizes the data by service principal, providing:
- Total number of authentications.
- Number of unique IP addresses used.
- List of countries and cities from which sign-ins occurred.
- Count of authentications from high-risk countries.
- Count of authentications from private IP ranges.
- List of IP addresses used.
- List of resources accessed.
- Types of credentials used.
- First and last time the service principal was seen in the logs.
-
Ordering: The results are ordered by the number of authentications from high-risk countries, followed by the total number of authentications.
This query helps in identifying unusual or potentially malicious sign-in patterns for service principals, which can be useful for security monitoring and establishing a baseline for normal activity.
Details
David Alonso
Released: April 21, 2026
Tables
Keywords
Operators