KQL Search

Query Details

HomeAI ToolsDevice Query

HUNT 08 SP First Time Resource 30d

Query

// Hunt     : Workload Identity - SP First-Time Resource Access (New Accesses, 30d lookback)
// Tactics  : Discovery, CredentialAccess
// MITRE    : T1087.004, T1552.001
// Purpose  : Identifies SP–resource pairs where access was observed in the last 7 days
//            but had NO history in the prior 23 days. Indicates a new or compromised SP
//            expanding its access footprint. Correlated with AuditLogs for SP changes.
//==========================================================================================

let HistoricalAccess = (AADServicePrincipalSignInLogs | invoke ExcludeAllowlistedIPs())
    | where TimeGenerated between (ago(30d) .. ago(7d))
    | where ResultType == "0"
    | distinct ServicePrincipalId, ResourceDisplayName;
let RecentAccess = (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)
    | summarize
        NewAccessCount   = count(),
        UniqueIPs        = dcount(IPAddress),
        Countries        = make_set(Country, 10),
        CredTypes        = make_set(ClientCredentialType, 5),
        FirstAccess      = min(TimeGenerated),
        LastAccess       = max(TimeGenerated)
        by ServicePrincipalName, ServicePrincipalId, AppId, ResourceDisplayName
    | join kind=leftanti HistoricalAccess on ServicePrincipalId, ResourceDisplayName;
// --- Correlate with AuditLogs SP changes ---
let RecentAuditChanges = AuditLogs
    | where TimeGenerated > ago(30d)
    | where OperationName has_any (
        "Add service principal", "Add service principal credentials",
        "Update application – Certificates and secrets management")
    | where Result =~ "success"
    | extend SPId      = tostring(TargetResources[0].id)
    | extend Initiator = coalesce(tostring(InitiatedBy.user.userPrincipalName),
        tostring(InitiatedBy.app.displayName))
    | summarize
        ChangeCount    = count(),
        ChangeTypes    = make_set(OperationName, 5),
        Initiators     = make_set(Initiator, 5),
        LastChange     = max(TimeGenerated)
        by SPId;
RecentAccess
| join kind=leftouter RecentAuditChanges on $left.ServicePrincipalId == $right.SPId
| extend HasRecentChange = isnotempty(LastChange)
| project-away SPId
| order by NewAccessCount desc

Explanation

This query is designed to identify service principals (SPs) that have accessed resources for the first time in the last 7 days, without any prior access in the preceding 23 days. This could indicate a new or potentially compromised SP expanding its access. Here's a simplified breakdown:

  1. Historical Access Check:

    • It looks at SP sign-in logs from 30 to 7 days ago to identify SP-resource pairs that had successful access during that period.

  2. Recent Access Check:

    • It examines SP sign-in logs from the last 7 days to find SP-resource pairs with successful access.
    • It gathers additional information such as the number of accesses, unique IP addresses, countries of access, credential types used, and the first and last access times.

  3. Identify New Access:

    • It identifies SP-resource pairs that appear in the recent access logs but not in the historical access logs, indicating new access.

  4. Correlate with Audit Logs:

    • It checks audit logs for any changes related to service principals in the last 30 days, such as adding new SPs or updating credentials.
    • It gathers information about the changes, including the type of change, who initiated it, and when it last occurred.

  5. Combine and Analyze:

    • It combines the new access data with audit log changes to see if there have been any recent changes related to the SPs with new access.
    • It flags SPs with recent changes and orders the results by the number of new accesses.

This query helps in detecting potentially unauthorized or suspicious access patterns by SPs, which could be indicative of a security threat.

Details

David Alonso profile picture

David Alonso

Released: April 21, 2026

Tables

AADServicePrincipalSignInLogsAuditLogs

Keywords

WorkloadIdentityServicePrincipalResourceAccessAuditLogsGeoInfoCountryOperationNameInitiator

Operators

let|invokebetween..==distinct>extendtostringsummarizecountdcountmake_setminmaxbyjoinkind=leftantionhas_any=~coalesceproject-awayorder bydescisnotempty

Actions