I have run into this barrier a lot while processing Azure logs: I want to do something intuitive like
|stats count by appliedConditionalAccessPolicies{}.displayName, appliedConditionalAccessPolicies{}.result
but since there are multiple instances of each displayName-d policy per event and all of the sub-values that have the same name are MV-fielded together, my results are much less meaningful than I had intended.
I'm sure the answer to this involves |spath, but I'm struggling to wrap the examples I see here and here around my data.
Ideal result makes this:
|stats count by appliedConditionalAccessPolicies{}.displayName AS policy_name, appliedConditionalAccessPolicies{}.result AS result
produce something like this:
policy_name | ||
application_policy | failure | 12398 |
application_policy | success | 9889898 |
phone_policy | success | 1238988 |
...
| spath appliedConditionalAccessPolicies{} output=appliedConditionalAccessPolicies
| stats count by appliedConditionalAccessPolicies
| spath input=appliedConditionalAccessPolicies
| table as_you_like
"stats by" is used for JSON arrays.
...
| spath appliedConditionalAccessPolicies{} output=appliedConditionalAccessPolicies
| stats count by appliedConditionalAccessPolicies
| spath input=appliedConditionalAccessPolicies
| table as_you_like
"stats by" is used for JSON arrays.
THANK YOU. I've been wrestling with spath for a long time and this example made a lot of things click for me. Exactly what I was looking for (and what I have been looking for when dealing with JSON for ages)
Can you use spath to get your two mv arrays (displayName and result), then mvzip them to a new array, mvexpand that array to get new events, and do your stats on those events (perhaps splitting up the values in the new array to get displayName and result in separate columns again)?