Apr 18 2019
04:24 AM
- last edited on
Apr 07 2022
05:45 PM
by
TechCommunityAP
Apr 18 2019
04:24 AM
- last edited on
Apr 07 2022
05:45 PM
by
TechCommunityAP
Hi
In Azure Sentinel, I need to find events where access to a resource was blocked by specific conditional access policy.
Can anyone help with the query ?
Apr 18 2019 08:21 AM
let policyname = "MCAS"; // some text that matches the policy name SigninLogs | where ConditionalAccessPolicies has policyname | where ConditionalAccessStatus == "success" | project AppliedConditionalAccessPolicies
Maybe start to look for the policy name...
May 06 2019 05:31 AM
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...
May 09 2019 02:25 AM
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), ConditionalAccessStatus
May 09 2019 03:53 AM - edited May 09 2019 04:09 AM
Thank 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
May 09 2019 05:06 AM
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)
May 09 2019 05:07 AM
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)
May 09 2019 05:59 AM
| extend CAPoliciesJson = parse_json(tostring(PropertiesJSON))
This will parse the first item of the new array.
May 04 2020 10:42 PM - edited May 05 2020 12:09 AM
I know this question is over a year old but I want to answer with what I did in Log Analytics for someone else searching like I was, your where clauses will of course be different, mine was looking for legacy auth requests blocked by a particular policy:
SigninLogs
| where TimeGenerated >= ago(24h)
| where ClientAppUsed !in ("Browser","Mobile Apps and Desktop Clients","")
| mvexpand PolicyResults = ConditionalAccessPolicies
| where PolicyResults.id == "<Policy ID/GUID>" and PolicyResults.result != "reportOnlyNotApplied"
| project UserPrincipalName, ClientAppUser, tostring(PolicyResults.result), TimeGenerated
It's lines 4 and 5 that you need. Hope it helps someone
Jun 05 2020 03:00 AM