Forum Discussion
Find events where access was blocked by specific condional access policy
This is not it.
ConditionalAccessPolicies is an array of all the policies found in the tenant.
Each policy can have a status of success, notApplied or notEnabled (possibly more?)
In PowerShell this would be a no-brainer.
$policyid = <guid>
$ConditionalAccessPolicies | ?{$_.id -eq $policyid -AND $_.result -eq "success"}
I just don't know how do that in this query language...
More like
SigninLogs
| where tostring(ConditionalAccessPolicies.[0].displayName) !=""
| summarize count() by //TimeGenerated,
CAPolicyName = tostring(ConditionalAccessPolicies.[0].displayName), ConditionalAccessStatus
Filter on 'success' with
SigninLogs
| where tostring(ConditionalAccessPolicies.[0].displayName) !=""
| where ConditionalAccessStatus == "success"
| summarize count() by //TimeGenerated,
CAPolicyName = tostring(ConditionalAccessPolicies.[0].displayName), ConditionalAccessStatusThank you for trying :)
This will not work.
In your example you are only checking the first policy from the array (with index [0]).
I don't know at which position in the array my policy is.
I can find out by checking the logs (today it is 27) but that position can change as older policies are removed from the tenant.
Query must be based on specific policy ID
I only have the one policy, so always [0] :-(
However I think (not tested) mvexpand might help here, this might do multiple array positions
SigninLogs | extend PropertiesJSON = parse_json(ConditionalAccessPolicies) | extend CAPoliciesJson = parse_json(tostring(PropertiesJSON)) | mvexpand CAPoliciesJson //| project CAPoliciesJson .displayName | where CAPoliciesJson.displayName !="" | summarize count() by //TimeGenerated, CAPolicyName = tostring(CAPoliciesJson.displayName) , tostring(CAPoliciesJson.result), tostring(CAPoliciesJson.id)| extend CAPoliciesJson = parse_json(tostring(PropertiesJSON))
This will parse the first item of the new array.
I only have the one policy, so always [0] :-(
However I think (not tested) mvexpand might help here, this might do multiple array positions
SigninLogs | extend PropertiesJSON = parse_json(ConditionalAccessPolicies) | extend CAPoliciesJson = parse_json(tostring(PropertiesJSON)) | mvexpand CAPoliciesJson //| project CAPoliciesJson .displayName | where CAPoliciesJson.displayName !="" | summarize count() by //TimeGenerated, CAPolicyName = tostring(CAPoliciesJson.displayName) , tostring(CAPoliciesJson.result), tostring(CAPoliciesJson.id)