Query Details
Identity Conditional Access Pivot Table
Query
//Create a pivot table showing all conditional access policy outcomes over the last 30 days
//Data connector required for this query - Azure Active Directory - Signin Logs
SigninLogs
| where TimeGenerated > ago(30d)
| extend CA = parse_json(ConditionalAccessPolicies)
| mv-expand bagexpansion=array CA
| evaluate bag_unpack(CA)
| extend
['CA Outcome']=tostring(column_ifexists('result', "")),
['CA Policy Name'] = column_ifexists('displayName', "")
| evaluate pivot(['CA Outcome'], count(), ['CA Policy Name'])Explanation
This query creates a pivot table that displays the outcomes of conditional access policies over the last 30 days. It uses the Azure Active Directory - Signin Logs data connector. The query filters the logs to only include data from the past 30 days. It then expands and unpacks the conditional access policies, and extracts the outcome and policy name. Finally, it pivots the data to show the count of each outcome for each policy name in the pivot table.
Details
Matt Zorich
Released: June 17, 2022
Tables
SigninLogs
Keywords
SigninLogs,TimeGenerated,ConditionalAccessPolicies,CA,bagexpansion,CAOutcome,CAPolicyName
Operators
whereextendmv-expandevaluatebag_unpackcolumn_ifexiststostringpivotcount