Conditional Access Report

// Conditional Access Report // https://www.linkedin.com/posts/activity-7192037878783205377-lq27/ // Do you know you can access all these CA statistics from Signin Logs table using KQL? 😎 // Using Sentinel and the below KQL will provide you the statistics ("Success"/"Failure"/"Not applied") of all your conditional access rules based on certain number of days. You can further summarize these set of data to better understand your conditional access rule trigger and tweak it to improve the security controls. 🛡 SigninLogs | where TimeGenerated > ago(30d) // Additional Toggle to determine CA result for success/failure login //| where ResultType == "0" | where ConditionalAccessPolicies != "[]" | mv-expand ConditionalAccessPolicies | extend CADisplayName = tostring(ConditionalAccessPolicies.displayName) | extend CAResult = tostring(ConditionalAccessPolicies.result) | summarize Count=count() by CADisplayName, CAResult | sort by CADisplayName asc

Explanation

This query is designed to generate a report on Conditional Access (CA) policies using data from the Signin Logs table. Here's a simple summary of what it does:

Filter by Time: It looks at sign-in logs from the past 30 days.
Filter by Conditional Access Policies: It only includes logs where Conditional Access policies were applied.
Expand Policies: It breaks down the Conditional Access policies into individual entries.
Extract Details: It extracts the display name and result (e.g., "Success", "Failure", "Not applied") of each Conditional Access policy.
Summarize Data: It counts how many times each policy resulted in each outcome.
Sort Results: It sorts the results alphabetically by the policy display name.

The output helps you understand how often each Conditional Access policy was triggered and its result, allowing you to tweak and improve your security controls.

KQL Search

Conditional Access Report

Query

Explanation

Details

Actions