KQL Search

Query Details

HomeAI ToolsDevice Query

Identity Conditional Access Policies Notin Use

Query

//Find Azure AD conditional access policies that have no hits for 'success' or 'failure' over the last month

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

//Check that these policies are configured correctly or still required
SigninLogs
| where TimeGenerated > ago (30d)
| project TimeGenerated, ConditionalAccessPolicies
| mv-expand ConditionalAccessPolicies
| extend CAResult = tostring(ConditionalAccessPolicies.result)
| extend ['Conditional Access Policy Name'] = tostring(ConditionalAccessPolicies.displayName)
| summarize ['Conditional Access Result']=make_set(CAResult) by ['Conditional Access Policy Name']
| where ['Conditional Access Result'] !has "success"
    and ['Conditional Access Result'] !has "failure"
    and ['Conditional Access Result'] !has "unknownFutureValue"
| sort by ['Conditional Access Policy Name'] asc

Explanation

This query finds Azure AD conditional access policies that have not had any successful or failed attempts in the last month. It checks if these policies are configured correctly or still necessary. The query requires the Azure Active Directory - Signin Logs data connector.

Details

Matt Zorich profile picture

Matt Zorich

Released: June 17, 2022

Tables

SigninLogs

Keywords

SigninLogs,TimeGenerated,ConditionalAccessPolicies,CAResult,ConditionalAccessPolicyName,ConditionalAccessResult

Operators

whereagoprojectmv-expandextendtostringsummarizeby!hassort by

Actions