Query Details
HUNT 06 SP Critical Resource Access 90d
Query
// Hunt : Workload Identity - SP Critical Resource Access Full History (90d)
// Tactics : CredentialAccess, Discovery
// MITRE : T1552.001, T1087.004
// Purpose : 90-day view of all SP accesses to critical Azure/M365 resources: Microsoft Graph,
// Key Vault, ARM, Exchange, SharePoint, AAD. Correlate with AuditLogs to link
// first-time access events to recent SP creation or credential changes.
//==========================================================================================
let CriticalResources = dynamic([
"Microsoft Graph", "Azure Key Vault", "Azure Active Directory",
"Windows Azure Active Directory", "Azure Resource Manager",
"Office 365 Exchange Online", "Office 365 SharePoint Online"
]);
let PrivateRanges = dynamic(["10.", "192.168.", "172.16.", "127.", "169.254.", "168.63."]);
// --- SP access to critical resources ---
let SPAccess = (AADServicePrincipalSignInLogs | invoke ExcludeAllowlistedIPs())
| where TimeGenerated > ago(90d)
| where ResultType == "0"
| where ResourceDisplayName in (CriticalResources)
| where isnotempty(IPAddress)
| extend GeoInfo = geo_info_from_ip_address(IPAddress)
| extend Country = tostring(GeoInfo.country_iso_code)
| summarize
AccessCount = count(),
UniqueIPs = dcount(IPAddress),
UniqueCountries = dcount(Country),
Countries = make_set(Country, 10),
CredTypes = make_set(ClientCredentialType, 5),
FirstAccess = min(TimeGenerated),
LastAccess = max(TimeGenerated)
by ServicePrincipalName, ServicePrincipalId, AppId, ResourceDisplayName;
// --- Recent SP lifecycle events from AuditLogs ---
let SPLifecycleEvents = AuditLogs
| where TimeGenerated > ago(90d)
| where OperationName has_any (
"Add service principal", "Add service principal credentials",
"Update application – Certificates and secrets management",
"Delete service principal")
| where Result =~ "success"
| extend SPId = tostring(TargetResources[0].id)
| extend Initiator = coalesce(tostring(InitiatedBy.user.userPrincipalName),
tostring(InitiatedBy.app.displayName))
| summarize
LifecycleEvents = count(),
EventTypes = make_set(OperationName, 5),
Initiators = make_set(Initiator, 5),
LatestChange = max(TimeGenerated)
by SPId;
SPAccess
| join kind=leftouter SPLifecycleEvents on $left.ServicePrincipalId == $right.SPId
| project-away SPId
| order by AccessCount desc
Explanation
This query is designed to monitor and analyze the access patterns of Service Principals (SPs) to critical Azure and Microsoft 365 resources over the past 90 days. Here's a simplified breakdown:
-
Critical Resources: The query focuses on access to key resources such as Microsoft Graph, Azure Key Vault, Azure Active Directory, Azure Resource Manager, Office 365 Exchange Online, and SharePoint Online.
-
Service Principal Access: It retrieves logs of SP sign-ins to these critical resources, filtering out any access from allowlisted IP addresses and only considering successful access attempts. It gathers data on:
- The number of times each SP accessed the resources.
- The number of unique IP addresses and countries from which the accesses originated.
- The types of credentials used.
- The first and last access times for each SP.
-
Service Principal Lifecycle Events: It also looks at recent lifecycle events related to SPs, such as creation, credential updates, and deletions, from the AuditLogs. It captures:
- The number of lifecycle events.
- The types of events.
- The initiators of these events.
- The most recent change time.
-
Correlation: The query then correlates the access data with the lifecycle events to identify any connections between first-time access events and recent SP creation or credential changes.
-
Output: Finally, it orders the results by the number of accesses, providing a comprehensive view of SP activity related to critical resources, along with any recent changes to those SPs. This helps in identifying potential security risks or unusual access patterns.
Details
David Alonso
Released: April 21, 2026
Tables
Keywords
Operators