Home

Find events where access was blocked by specific condional access policy

%3CLINGO-SUB%20id%3D%22lingo-sub-470860%22%20slang%3D%22en-US%22%3EFind%20events%20where%20access%20was%20blocked%20by%20specific%20condional%20access%20policy%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-470860%22%20slang%3D%22en-US%22%3E%3CP%3EHi%3C%2FP%3E%3CP%3EIn%20Azure%20Sentinel%2C%20I%20need%20to%20find%20events%20where%20access%20to%20a%20resource%20was%20blocked%20by%20specific%20conditional%20access%20policy.%3C%2FP%3E%3CP%3ECan%20anyone%20help%20with%20the%20query%20%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-470860%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EAzure%20Sentinel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-472022%22%20slang%3D%22en-US%22%3ERe%3A%20Find%20events%20where%20access%20was%20blocked%20by%20specific%20condional%20access%20policy%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-472022%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F127591%22%20target%3D%22_blank%22%3E%40Grzegorz%20Wierzbicki%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CPRE%3Elet%20policyname%20%3D%20%22MCAS%22%3B%20%20%2F%2F%20some%20text%20that%20matches%20the%20policy%20name%0ASigninLogs%0A%7C%20where%20ConditionalAccessPolicies%20has%20policyname%0A%7C%20where%20ConditionalAccessStatus%20%3D%3D%20%22success%22%0A%7C%20project%20AppliedConditionalAccessPolicies%20%3C%2FPRE%3E%0A%3CP%3EMaybe%20start%20to%20look%20for%20the%20policy%20name...%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-534475%22%20slang%3D%22en-US%22%3ERe%3A%20Find%20events%20where%20access%20was%20blocked%20by%20specific%20condional%20access%20policy%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-534475%22%20slang%3D%22en-US%22%3E%3CP%3EThis%20is%20not%20it.%3C%2FP%3E%3CP%3EConditionalAccessPolicies%20is%20an%20%3CEM%3Earray%3C%2FEM%3E%20of%20all%20the%20policies%20found%20in%20the%20tenant.%3C%2FP%3E%3CP%3EEach%20policy%20can%20have%20a%20status%20of%20%3CSTRONG%3Esuccess%3C%2FSTRONG%3E%2C%20%3CSTRONG%3EnotApplied%3C%2FSTRONG%3E%20or%20%3CSTRONG%3EnotEnabled%3C%2FSTRONG%3E%20(possibly%20more%3F)%3C%2FP%3E%3CP%3EIn%20PowerShell%20this%20would%20be%20a%20no-brainer.%3C%2FP%3E%3CP%3E%24policyid%20%3D%20%3CGUID%3E%3C%2FGUID%3E%3C%2FP%3E%3CP%3E%24ConditionalAccessPolicies%20%7C%20%3F%7B%24_.id%20-eq%20%24policyid%20-AND%20%24_.result%20-eq%20%22success%22%7D%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20just%20don't%20know%20how%20do%20that%20in%20this%20query%20language...%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-551779%22%20slang%3D%22en-US%22%3ERe%3A%20Find%20events%20where%20access%20was%20blocked%20by%20specific%20condional%20access%20policy%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-551779%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F127591%22%20target%3D%22_blank%22%3E%40Grzegorz%20Wierzbicki%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EMore%20like%3C%2FP%3E%0A%3CPRE%3ESigninLogs%0A%7C%20where%20tostring(ConditionalAccessPolicies.%5B0%5D.displayName)%20!%3D%22%22%0A%7C%20summarize%20count()%20by%20%2F%2FTimeGenerated%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20CAPolicyName%20%3D%20tostring(ConditionalAccessPolicies.%5B0%5D.displayName)%2C%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20ConditionalAccessStatus%3C%2FPRE%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20876px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F112716i1B3B72E71D0D6FEC%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22Annotation%202019-05-09%20102251.png%22%20title%3D%22Annotation%202019-05-09%20102251.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EFilter%20on%20'success'%20with%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CPRE%3ESigninLogs%0A%7C%20where%20tostring(ConditionalAccessPolicies.%5B0%5D.displayName)%20!%3D%22%22%0A%7C%20where%20ConditionalAccessStatus%20%3D%3D%20%22success%22%0A%7C%20summarize%20count()%20by%20%2F%2FTimeGenerated%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20CAPolicyName%20%3D%20tostring(ConditionalAccessPolicies.%5B0%5D.displayName)%2C%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20ConditionalAccessStatus%3C%2FPRE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-552060%22%20slang%3D%22en-US%22%3ERe%3A%20Find%20events%20where%20access%20was%20blocked%20by%20specific%20condional%20access%20policy%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-552060%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F239477%22%20target%3D%22_blank%22%3E%40Clive%20Watson%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20for%20trying%20%3A)%3C%2Fimg%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThis%20will%20not%20work.%3C%2FP%3E%3CP%3EIn%20your%20example%20you%20are%20only%20checking%20the%20first%20policy%20from%20the%20array%20(with%20index%20%5B0%5D).%3C%2FP%3E%3CP%3EI%20don't%20know%20at%20which%20position%20in%20the%20array%20my%20policy%20is.%3C%2FP%3E%3CP%3EI%20can%20find%20out%20by%20checking%20the%20logs%20(today%20it%20is%2027)%20but%20that%20position%20can%20change%20as%20older%20policies%20are%20removed%20from%20the%20tenant.%3C%2FP%3E%3CP%3EQuery%20must%20be%20based%20on%20specific%20policy%20ID%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-552180%22%20slang%3D%22en-US%22%3ERe%3A%20Find%20events%20where%20access%20was%20blocked%20by%20specific%20condional%20access%20policy%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-552180%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F127591%22%20target%3D%22_blank%22%3E%40Grzegorz%20Wierzbicki%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EI%20only%20have%20the%20one%20policy%2C%20so%20always%20%5B0%5D%20%3A(%3C%2Fimg%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EHowever%20I%20think%20(not%20tested)%20mvexpand%20might%20help%20here%2C%20this%20might%20do%20multiple%20array%20positions%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CPRE%3ESigninLogs%0A%7C%20extend%20PropertiesJSON%20%3D%20parse_json(ConditionalAccessPolicies)%0A%7C%20extend%20CAPoliciesJson%20%3D%20parse_json(tostring(PropertiesJSON))%20%0A%7C%20mvexpand%20CAPoliciesJson%0A%2F%2F%7C%20project%20CAPoliciesJson%20.displayName%0A%7C%20where%20CAPoliciesJson.displayName%20!%3D%22%22%0A%7C%20summarize%20count()%20by%20%2F%2FTimeGenerated%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20CAPolicyName%20%3D%20tostring(CAPoliciesJson.displayName)%20%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20tostring(CAPoliciesJson.result)%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20tostring(CAPoliciesJson.id)%3C%2FPRE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-552182%22%20slang%3D%22en-US%22%3ERe%3A%20Find%20events%20where%20access%20was%20blocked%20by%20specific%20condional%20access%20policy%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-552182%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F127591%22%20target%3D%22_blank%22%3E%40Grzegorz%20Wierzbicki%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EI%20only%20have%20the%20one%20policy%2C%20so%20always%20%5B0%5D%20%3A(%3C%2Fimg%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EHowever%20I%20think%20(not%20tested)%20mvexpand%20might%20help%20here%2C%20this%20might%20do%20multiple%20array%20positions%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CPRE%3ESigninLogs%0A%7C%20extend%20PropertiesJSON%20%3D%20parse_json(ConditionalAccessPolicies)%0A%7C%20extend%20CAPoliciesJson%20%3D%20parse_json(tostring(PropertiesJSON))%20%0A%7C%20mvexpand%20CAPoliciesJson%0A%2F%2F%7C%20project%20CAPoliciesJson%20.displayName%0A%7C%20where%20CAPoliciesJson.displayName%20!%3D%22%22%0A%7C%20summarize%20count()%20by%20%2F%2FTimeGenerated%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20CAPolicyName%20%3D%20tostring(CAPoliciesJson.displayName)%20%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20tostring(CAPoliciesJson.result)%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20tostring(CAPoliciesJson.id)%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-552356%22%20slang%3D%22en-US%22%3ERe%3A%20Find%20events%20where%20access%20was%20blocked%20by%20specific%20condional%20access%20policy%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-552356%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F239477%22%20target%3D%22_blank%22%3E%40Clive%20Watson%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CPRE%3E%7C%20extend%20CAPoliciesJson%20%3D%20parse_json(tostring(PropertiesJSON))%3C%2FPRE%3E%3CP%3EThis%20will%20parse%20the%20first%20item%20of%20the%20new%20array.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Grzegorz Wierzbicki
Occasional Contributor

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 ?

7 Replies

@Grzegorz Wierzbicki 

 

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...

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...

@Grzegorz Wierzbicki 

 

More like

SigninLogs
| where tostring(ConditionalAccessPolicies.[0].displayName) !=""
| summarize count() by //TimeGenerated,
                        CAPolicyName = tostring(ConditionalAccessPolicies.[0].displayName),                                            ConditionalAccessStatus

Annotation 2019-05-09 102251.png

 

Filter on 'success' with 

 

SigninLogs
| where tostring(ConditionalAccessPolicies.[0].displayName) !=""
| where ConditionalAccessStatus == "success"
| summarize count() by //TimeGenerated,
                        CAPolicyName = tostring(ConditionalAccessPolicies.[0].displayName),                                            ConditionalAccessStatus

@Clive Watson 

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

@Grzegorz Wierzbicki 

 

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)

@Grzegorz Wierzbicki 

 

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)

 

@Clive Watson 

| extend CAPoliciesJson = parse_json(tostring(PropertiesJSON))

This will parse the first item of the new array.

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
46 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
13 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
29 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies