Display only certain properties

[JandrevdM]

Good day, 

I have a query to check my Entra logs to see what Conditional access policies gets hit. The returns results like this but I would like it to display only the policies that were success or Applied and not the ones that was not applied.

CA	CAName
success failure failure	CA-Office-MFA   CA-Signin-LocationBased CA-HybridJoined
notApplied success failure	CA-Office-MFA   CA-Signin-LocationBased CA-HybridJoined
notApplied success success	CA-Office-MFA   CA-Signin-LocationBased CA-HybridJoined
What I want instead
success failure failure	CA-Office-MFA   CA-Signin-LocationBased CA-HybridJoined
success success	CA-Signin-LocationBased CA-HybridJoined
success failure	CA-Signin-LocationBased CA-HybridJoined

index=db_azure_entraid sourcetype="azure:monitor:aad" command="Sign-in activity" category=SignInLogs "properties.clientAppUsed"!=null
NOT app="Windows Sign In"
| spath "properties.appliedConditionalAccessPolicies{}.result"
| search "properties.appliedConditionalAccessPolicies{}.result"=notApplied
| rename "properties.appliedConditionalAccessPolicies{}.result" as CA
| rename "properties.appliedConditionalAccessPolicies{}.displayName" as CAName
| dedup CA
| table CA CAName

[ITWhisperer]

| spath "properties.appliedConditionalAccessPolicies{}" output=appliedConditionalAccessPolicies
| mvexpand appliedConditionalAccessPolicies
| where json_extract_exact(appliedConditionalAccessPolicies,"result") != "notApplied"

[PickleRick]

One small hint for future - if you paste search code, use preformatted paragraph or code block - it makes it easier to read and prevents accidental interpretation of some character sequences as emojis or something.

But to the point.

Your search is a bit flawed conceptually.

1. Your json gets parsed into multivalued fields. Separate ones. there is no guarantee that subsequent values of each of those multivalued fields correspond with each other. Especailly after additional processing.

A simple run-anywhere example to illustrate my point

| makeresults
| eval _raw="[{\"a\":\"a\",\"b\":\"b\"},{\"a\":\"c\"},{\"b\":\"d\"}]"
| spath

As you can see, the event consists of an array of three structures with fields from second and third of them being completely unrelated to one another. After parsing, the multivalued fields "suggest" that the "a" field with value "c" matches field "b" with value "d".

And if you wanted to reorder those pairs (even assuming you can know for sure that for your particular data the order does match both fields well) so they keep in proper order... that's very ugly and inefficient.

So I'd advise to separately parse out whole properties.appliedConditionalAccessPolicies{}, then do mvexpand so that they get into separate results (maybe cutting out all other fields if you don't need them so they don't get dragged along and fill memory unnecessarily). And then parse the values from the resulting json "substructures".

Then you can simply filter with where or do whatever you want.

2. Be careful with dedup - it leaves just the first event (or n events if you specify limit) for each value(s) of given field(s). It doesn't matter that other fields do not change and you capture all their values. So that might not be what you want.