KQL Search

// Hunt     : Workload Identity - Service Principals Not Used in 90 Days (Stale/Abandoned SPs)
// Tactics  : Persistence
// MITRE    : T1098.001 (Account Manipulation: Additional Cloud Credentials), T1078.004
// Purpose  : A stale SP with active credentials is a standing attack surface — attackers
//            target them because they have no active owners, rarely rotate credentials,
//            and generate no alerts when used after a long gap. This query surfaces SPs
//            whose last successful sign-in was more than 90 days ago but are still present
//            in the directory. Correlates with AuditLogs to show if credentials were
//            added recently (reactivation attempt), and AzureActivity to check for any
//            recent management plane calls that may not appear in sign-in logs.
//==========================================================================================

// --- SPs that have signed in at any point (to know their last activity) ---
let AllSPActivity = (AADServicePrincipalSignInLogs | invoke ExcludeAllowlistedIPs())
    | where TimeGenerated > ago(180d)
    | where ResultType == "0"
    | summarize
        TotalSignins     = count(),
        LastSignin       = max(TimeGenerated),
        UniqueResources  = dcount(ResourceDisplayName),
        LastResources    = make_set(ResourceDisplayName, 5)
        by ServicePrincipalId, ServicePrincipalName, AppId;

// --- Inactive SPs: last sign-in was >90 days ago ---
let InactiveSPs = AllSPActivity
    | where LastSignin < ago(90d);

// --- Check for recent credential additions to inactive SPs (reactivation signal) ---
let RecentCredChanges = AuditLogs
    | where TimeGenerated > ago(90d)
    | where OperationName has_any (
        "Add service principal credentials",
        "Update application – Certificates and secrets management",
        "Update service principal")
    | where Result =~ "success"
    | extend SPId      = tostring(TargetResources[0].id)
    | extend Initiator = coalesce(
        tostring(InitiatedBy.user.userPrincipalName),
        tostring(InitiatedBy.app.displayName))
    | summarize
        RecentChanges    = count(),
        ChangeTypes      = make_set(OperationName, 5),
        Initiators       = make_set(Initiator, 5),
        LastChange       = max(TimeGenerated)
        by SPId;

// --- Check for recent AzureActivity calls from inactive SPs (headless management access) ---
// isfuzzy=true: AzureActivity may not be connected in all environments
let RecentMgmtActivity = union isfuzzy=true
    AzureActivity,
    (datatable(TimeGenerated:datetime, ActivityStatusValue:string, OperationNameValue:string, Caller:string)[])
    | where TimeGenerated > ago(90d)
    | where ActivityStatusValue =~ "Success"
    | summarize
        MgmtOps          = count(),
        LastMgmtOp       = max(TimeGenerated),
        Operations        = make_set(OperationNameValue, 5)
        by Caller;

InactiveSPs
| join kind=leftouter RecentCredChanges on $left.ServicePrincipalId == $right.SPId
| join kind=leftouter RecentMgmtActivity on $left.ServicePrincipalId == $right.Caller
| extend DaysInactive         = datetime_diff("day", now(), LastSignin)
| extend HasRecentCredChange  = RecentChanges > 0
| extend HasMgmtActivity      = MgmtOps > 0
| extend RiskAssessment = case(
    HasRecentCredChange == true and HasMgmtActivity == true,
        "Critical — Stale SP Reactivated with New Creds + Mgmt Activity",
    HasRecentCredChange == true,
        "High — Stale SP Had Credential Change (Possible Reactivation)",
    HasMgmtActivity == true,
        "High — Stale SP Has Recent Management Plane Activity",
    DaysInactive > 180,
        "Medium — Very Long Dormancy (>180 days), Cleanup Recommended",
        "Low — Stale SP, No Recent Activity or Changes")
| project
    ServicePrincipalName, ServicePrincipalId, AppId,
    LastSignin, DaysInactive, TotalSignins,
    UniqueResources, LastResources,
    HasRecentCredChange, RecentChanges, ChangeTypes, Initiators, LastChange,
    HasMgmtActivity, MgmtOps, LastMgmtOp, Operations,
    RiskAssessment
| order by RiskAssessment asc, DaysInactive desc