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
Path Finder

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.

stroud_bc_0-1598281993750.png

 

 

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

 

 

 

 

 

Labels (1)
Tags (2)
0 Karma
1 Solution

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

 "stats by" is used for JSON arrays. 

View solution in original post

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

 "stats by" is used for JSON arrays. 

View solution in original post

stroud_bc
Path Finder

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)

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

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

0 Karma
Did you miss .conf21 Virtual?

Good news! The event's keynotes and many of its breakout sessions are now available online, and still totally FREE!