KQL Search

Query Details

HomeAI ToolsDevice Query

HUNT 03 SP CA Bypass History 30d

Query

// Hunt     : Workload Identity - SP Conditional Access Bypass History (30d)
// Tactics  : InitialAccess, DefenseEvasion
// MITRE    : T1078.004, T1562
// Purpose  : Full 30-day timeline of SPs with ConditionalAccessStatus = notApplied or failure.
//            Surfaces SPs repeatedly bypassing CA policies. Correlate with RULE-04 to understand
//            which bypasses led to downstream cloud app activity.
//==========================================================================================

let PrivateRanges = dynamic(["10.", "192.168.", "172.16.", "127.", "169.254.", "168.63."]);
(AADServicePrincipalSignInLogs | invoke ExcludeAllowlistedIPs())
| where TimeGenerated > ago(30d)
| where ResultType == "0"
| where ConditionalAccessStatus in ("notApplied", "failure")
| where isnotempty(IPAddress)
| where not(IPAddress has_any (PrivateRanges))
| extend GeoInfo   = geo_info_from_ip_address(IPAddress)
| extend Country   = tostring(GeoInfo.country_iso_code)
| extend CAPolicies = tostring(ConditionalAccessPolicies)
| summarize
    BypassCount      = count(),
    UniqueIPs        = dcount(IPAddress),
    UniqueCountries  = dcount(Country),
    Countries        = make_set(Country, 10),
    IPList           = make_set(IPAddress, 20),
    PolicyStatuses   = make_set(ConditionalAccessStatus, 3),
    Resources        = make_set(ResourceDisplayName, 10),
    FirstBypass      = min(TimeGenerated),
    LastBypass       = max(TimeGenerated)
    by ServicePrincipalName, ServicePrincipalId, AppId
| where BypassCount > 10
| order by BypassCount desc

Explanation

This query is designed to identify and analyze service principals (SPs) that have bypassed conditional access policies over the past 30 days. Here's a simplified breakdown of what the query does:

  1. Data Source: It starts by examining Azure Active Directory service principal sign-in logs.

  2. Time Frame: It focuses on logs from the last 30 days.

  3. Filter Criteria:

    • Only considers logs where the sign-in result was successful (ResultType == "0").
    • Looks for instances where conditional access policies were either not applied or failed (ConditionalAccessStatus in ("notApplied", "failure")).
    • Excludes any IP addresses that are private or allowlisted.

  4. Geolocation: It retrieves geographical information based on the IP addresses involved in the sign-ins.

  5. Data Aggregation:

    • Counts the number of times conditional access policies were bypassed (BypassCount).
    • Counts the number of unique IP addresses and countries involved.
    • Collects lists of countries, IP addresses, policy statuses, and resources accessed.
    • Records the first and last instances of bypassing.

  6. Significant Bypasses: It filters the results to only include service principals that bypassed policies more than 10 times.

  7. Output: The results are sorted by the number of bypasses in descending order, highlighting the most frequently bypassed service principals.

The purpose of this query is to surface service principals that might be repeatedly bypassing conditional access policies, potentially indicating security risks or policy misconfigurations.

Details

David Alonso profile picture

David Alonso

Released: April 21, 2026

Tables

AADServicePrincipalSignInLogs

Keywords

WorkloadIdentityInitialAccessDefenseEvasionTimelineServicePrincipalConditionalAccessStatusPoliciesIPAddressGeoInfoCountryResourcesServicePrincipalNameServicePrincipalIdAppId

Operators

letinvoke|where>ago==inisnotemptynothas_anyextendgeo_info_from_ip_addresstostringsummarizecountdcountmake_setminmaxbyorder bydesc

Actions