KQL Search

Query Details

HomeAI ToolsDevice Query

Identity Find Multiple CA Successes

Query

//Find sign ins that have triggered multiple unique conditional access policy successes - maybe a chance to rationalize policy

//Data connector required for this query - Azure Active Directory - Signin Logs

SigninLogs
| where TimeGenerated > ago (30d)
| mv-apply ca=todynamic(ConditionalAccessPolicies) on (
    where ca.result == "success"
    | extend PolicyName = tostring(ca.displayName)
    )
| summarize
    ['Count of Poicies Applied']=dcount(PolicyName),
    ['List of Policies Applied']=make_set(PolicyName)
    by CorrelationId, UserPrincipalName
| where ['Count of Poicies Applied'] >= 2

Explanation

This query is looking for sign-ins that have triggered multiple unique conditional access policy successes. The purpose is to potentially rationalize the policies. The query requires the Azure Active Directory - Signin Logs data connector. It filters the sign-in logs for the past 30 days and applies a dynamic mapping to the conditional access policies. It then summarizes the count of policies applied and creates a list of the policies applied for each sign-in. Finally, it filters for sign-ins that have applied two or more policies.

Details

Matt Zorich profile picture

Matt Zorich

Released: November 30, 2022

Tables

SigninLogs

Keywords

SigninLogs,TimeGenerated,ConditionalAccessPolicies,PolicyName,CountofPoiciesApplied,ListofPoliciesApplied,CorrelationId,UserPrincipalName

Operators

whereagomv-applyonwhereextendsummarizedcountmake_setbyCorrelationIdUserPrincipalName

Actions