KQL Search

Query Details

HomeAI ToolsDevice Query

Identity Find CA Failure Percentage

Query

//Calculate the percentage of signins failing against each of your Conditional Access policies. If the percentage is high it may be worth evaulating the policy if it is fit for purpose.

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

SigninLogs
| where TimeGenerated > ago(30d)
| project ConditionalAccessPolicies
| extend CA = parse_json(ConditionalAccessPolicies)
| mv-expand bagexpansion=array CA
| extend ['CA Policy Name'] = tostring(CA.displayName)
| extend  ['CA Outcome'] = tostring(CA.result)
| summarize
    ['Total Signin Count']=count(),
    ['Total Failed Count']=countif(['CA Outcome'] == "failure")
    by ['CA Policy Name']
| extend ['Failed Percentage'] = todouble(['Total Failed Count']) * 100 / todouble(['Total Signin Count'])
| project-reorder
    ['CA Policy Name'],
    ['Total Signin Count'],
    ['Total Failed Count'],
    ['Failed Percentage']
| order by ['Failed Percentage']

Explanation

This query calculates the percentage of sign-ins that fail against each Conditional Access policy in Azure Active Directory. It helps determine if a policy is effective or needs to be evaluated. The query uses the SigninLogs data connector and filters for sign-ins within the last 30 days. It extracts the Conditional Access policies from the logs, expands them into separate rows, and calculates the total sign-in count and failed count for each policy. It then calculates the percentage of failed sign-ins and presents the results in a table, ordered by the highest failed percentage.

Details

Matt Zorich profile picture

Matt Zorich

Released: June 17, 2022

Tables

SigninLogs

Keywords

SigninLogs,TimeGenerated,ConditionalAccessPolicies,CA,CAPolicyName,CAOutcome,TotalSigninCount,TotalFailedCount,FailedPercentage

Operators

whereprojectextendmv-expandsummarizecount()countif()bytodouble()project-reorderorder by

Actions