Conditional Access Report

Query

// 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 

// MITRE ATT&CK Mapping

// The KQL query primarily focuses on analyzing Conditional Access Policies, which can be associated with the following MITRE ATT&CK techniques:

// T1078 - Valid Accounts:
// Description: Adversaries may use valid accounts to gain initial access, persist, escalate privileges, or evade detection.
// Relevance: The query checks for Conditional Access Policies, which are often used to enforce multi-factor authentication (MFA) and other security measures to protect valid accounts.
// T1110 - Brute Force:
// Description: Adversaries may use brute force techniques to attempt to gain access to accounts.
// Relevance: The query can be adapted to filter for failed login attempts (e.g., by uncommenting the ResultType == "0" line), which can help detect brute force attempts.
// T1190 - Exploit Public-Facing Application:
// Description: Adversaries may exploit vulnerabilities in public-facing applications to gain access.
// Relevance: Conditional Access Policies can help mitigate the risk of exploitation by enforcing additional authentication requirements.
// T1071 - Application Layer Protocol:
// Description: Adversaries may use application layer protocols to communicate with systems under their control.
// Relevance: Monitoring Conditional Access Policies can help detect unusual or unauthorized access patterns that may indicate the use of such protocols.

Explanation

This KQL (Kusto Query Language) query is designed to analyze Conditional Access (CA) policies from the Signin Logs in a Microsoft Sentinel environment. Here's a simple breakdown of what the query does:

Time Filter: It looks at sign-in logs from the past 30 days.
Conditional Access Policies: The query focuses on entries where Conditional Access Policies are applied (i.e., not empty).
Data Expansion: It expands the data to look at each policy individually.
Data Extraction: For each policy, it extracts the policy's display name and the result of the policy application (e.g., "Success", "Failure", "Not applied").
Data Summarization: It counts the number of times each policy resulted in each type of outcome (success, failure, not applied).
Sorting: The results are sorted alphabetically by the policy display name.

The query helps in understanding how often each Conditional Access policy is triggered and its outcomes, which can be useful for assessing and improving security controls.

Additionally, the query is linked to MITRE ATT&CK techniques, highlighting its relevance in detecting and mitigating potential security threats such as unauthorized access attempts, brute force attacks, and exploitation of vulnerabilities.