Query Details
Identity Summarize Conditional Access Policiesfailures
Query
//Create a summary showing which of your Azure AD conditional access policies are preventing the most signins and for what reasons //Data connector required for this query - Azure Active Directory - Signin Logs SigninLogs | where TimeGenerated > ago (7d) | project TimeGenerated, ConditionalAccessPolicies, ResultType, ResultDescription | mv-expand ConditionalAccessPolicies | extend CAResult = tostring(ConditionalAccessPolicies.result) | extend ['Conditional Access Policy Name'] = tostring(ConditionalAccessPolicies.displayName) | where CAResult == "failure" | summarize ['Count of Failures']=count()by ['Conditional Access Policy Name'], ResultType, ResultDescription | sort by ['Count of Failures'] desc
Explanation
This query analyzes Azure AD conditional access policies to determine which policies are preventing the most sign-ins and why. It uses the Azure Active Directory - Signin Logs data connector. The query filters the logs for the past 7 days and selects the relevant columns. It expands the conditional access policies column and extracts the result and display name. It then filters for failures and summarizes the count of failures by policy name, result type, and result description. The results are sorted in descending order based on the count of failures.
Details
Matt Zorich
Released: June 17, 2022
Tables
SigninLogs
Keywords
AzureAD,ConditionalAccessPolicies,SigninLogs,TimeGenerated,ResultType,ResultDescription,CAResult,ConditionalAccessPolicyName,CountofFailures
Operators
whereagoprojectmv-expandextendsummarizebysort