topic Re: How to parse JSON mvfield into a proper table with a different line for each node named for a value in the node in Getting Data In

How to parse JSON mvfield into a proper table with a different line for each node named for a value in the node

stroud_bc — Mon, 24 Aug 2020 17:05:58 GMT

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

Re: How to parse JSON mvfield into a proper table with a different line for each node named for a value in the node

ITWhisperer — Tue, 25 Aug 2020 17:18:13 GMT

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)?

Re: How to parse JSON mvfield into a proper table with a different line for each node named for a value in the node

to4kawa — Wed, 26 Aug 2020 10:59:52 GMT

... | spath appliedConditionalAccessPolicies{} output=appliedConditionalAccessPolicies | stats count by appliedConditionalAccessPolicies | spath input=appliedConditionalAccessPolicies | table as_you_like

"stats by" is used for JSON arrays.

Re: How to parse JSON mvfield into a proper table with a different line for each node named for a value in the node

stroud_bc — Wed, 26 Aug 2020 20:45:22 GMT

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)