KQL Search

Query Details

HomeAI ToolsDevice Query

HUNT 04 SP Cloud App Events Correlation

Query

// Hunt     : Workload Identity - SP Activity Correlated with CloudAppEvents (7d)
// Tactics  : Exfiltration, InitialAccess
// MITRE    : T1567, T1530, T1078.004
// Purpose  : Surfaces SPs that authenticated from high-risk geos AND performed high-risk cloud app
//            actions (file downloads, mail access, deletions). Joins AADServicePrincipalSignInLogs
//            with CloudAppEvents on ServicePrincipalId / AccountObjectId. Full 7-day investigation view.
//==========================================================================================

let HighRiskCountries = dynamic([
    "CN", "RU", "KP", "IR", "NG", "IQ", "PK", "KZ", "BY", "AF", "SY"
]);
let HighRiskActions = dynamic([
    "FileDownloaded", "FileSyncDownloadedFull", "FileDeleted", "FileMoved",
    "FileCopied", "SendAs", "MailItemsAccessed", "MassDelete", "DataExfiltration",
    "SharePointFileOperation"
]);
let PrivateRanges = dynamic(["10.", "192.168.", "172.16.", "127.", "169.254.", "168.63."]);
// --- SPs with suspicious sign-ins in the last 7 days ---
let SuspiciousSPSignins = (AADServicePrincipalSignInLogs | invoke ExcludeAllowlistedIPs())
    | where TimeGenerated > ago(7d)
    | where ResultType == "0"
    | where isnotempty(IPAddress)
    | where not(IPAddress has_any (PrivateRanges))
    | extend GeoInfo = geo_info_from_ip_address(IPAddress)
    | extend Country = tostring(GeoInfo.country_iso_code)
    | where Country in (HighRiskCountries)
        or ConditionalAccessStatus in ("notApplied", "failure")
    | summarize
        SigninCount   = count(),
        Countries     = make_set(Country, 5),
        Resources     = make_set(ResourceDisplayName, 5),
        SigninIPs     = make_set(IPAddress, 10),
        FirstSignin   = min(TimeGenerated),
        LastSignin    = max(TimeGenerated)
        by ServicePrincipalId, ServicePrincipalName, AppId;
// --- Cloud app events by those SPs ---
// isfuzzy=true: CloudAppEvents only exists when Defender for Cloud Apps is connected
union isfuzzy=true
    (CloudAppEvents | invoke ExcludeAllowlistedIPs()),
    (datatable(TimeGenerated:datetime, ActionType:string, AccountObjectId:string,
               ObjectName:string, Application:string, IPAddress:string)[])
| where TimeGenerated > ago(7d)
| where ActionType in (HighRiskActions)
| join kind=inner SuspiciousSPSignins on $left.AccountObjectId == $right.ServicePrincipalId
| summarize
    ActionCount      = count(),
    Actions          = make_set(ActionType, 10),
    AffectedObjects  = make_set(ObjectName, 20),
    Applications     = make_set(Application, 10),
    CloudEventIPs    = make_set(IPAddress, 10),
    Countries        = any(Countries),
    SigninIPs        = any(SigninIPs),
    FirstAction      = min(TimeGenerated),
    LastAction       = max(TimeGenerated)
    by ServicePrincipalName, ServicePrincipalId, AppId
| order by ActionCount desc

Explanation

This query is designed to identify potentially suspicious activities involving service principals (SPs) in a cloud environment over the past seven days. Here's a simplified breakdown of what the query does:

  1. Define High-Risk Countries and Actions: It sets up lists of countries considered high-risk (e.g., China, Russia) and actions that are considered high-risk (e.g., file downloads, mail access).

  2. Identify Suspicious Sign-ins:

    • It looks at sign-in logs for service principals and filters out those that occurred in the last seven days.
    • It excludes sign-ins from private IP ranges and focuses on those from high-risk countries or with failed conditional access.
    • It summarizes these sign-ins by counting occurrences, listing countries, resources accessed, and IP addresses used.

  3. Correlate with Cloud App Events:

    • It checks for cloud application events involving the same service principals that performed high-risk actions within the same timeframe.
    • It joins these events with the suspicious sign-ins based on matching identifiers.

  4. Summarize and Order Results:

    • It summarizes the correlated data by counting the number of actions, listing types of actions, affected objects, applications involved, and IP addresses.
    • It orders the results by the number of actions to highlight the most active or potentially risky service principals.

Overall, this query helps in detecting service principals that might be involved in unauthorized or risky activities by correlating their sign-in data with cloud application events.

Details

David Alonso profile picture

David Alonso

Released: April 21, 2026

Tables

AADServicePrincipalSignInLogsCloudAppEvents

Keywords

WorkloadIdentityCloudAppEventsAADServicePrincipalSignInLogsGeoInfoConditionalAccessStatusCloudAppEventsDefenderForCloudApps

Operators

letdynamicinvoke|where>ago==isnotemptynothas_anyextendgeo_info_from_ip_addresstostringinorsummarizecountmake_setminmaxbyunionisfuzzydatatablejoinkindon$order bydesc

Actions