need help with spath for json Array

[att35]

Hi,

Need some help with the following JSON data.

 ModifiedProperties: [ [-]
     { [-]
       Name: Group.ObjectID
       NewValue: 111111-2222222-333333-444444
       OldValue:
     }
     { [-]
       Name: Group.DisplayName
       NewValue: Group A
       OldValue:
     }
     { [-]
       Name: Group.WellKnownObjectName
       NewValue:
       OldValue:
     }
   ]

I want to extract the 2nd set of values for each event such that Group.DisplayName can become a field in itself, e.g. Group.DisplayName.NewValue=A, Group.DisplayName.OldValue=B.

But right now, default extraction is doing something like this

How can I create KV pairs for Group.DisplayName within this JSON array? I tried few combinations using spath but was not successful.

Thank you

[tscroggins]

Hi @att35,

Assuming _raw is properly formatted--and both your original Splunk Web screenshot and your new formatted event imply it is--you can use a combination of eval and spath commands to iterate over the array and create new fields:

| eval tmp="{".mvjoin(mvmap(json_array_to_mv(json_extract(json(_raw), "ModifiedProperties")), "\"".spath(_raw, "Name").".NewValue\":\"".spath(_raw, "NewValue")."\",\"".spath(_raw, "Name").".OldValue\":\"".spath(_raw, "OldValue")."\""), ",")."}"
| spath input=tmp
| fields - tmp

The eval command creates a tmp field with the following value:

{"Group.ObjectID.NewValue":"111111-2222222-333333-444444","Group.ObjectID.OldValue":"","Group.DisplayName.NewValue":"Group A","Group.DisplayName.OldValue":"","Group.WellKnownObjectName.NewValue":"","Group.WellKnownObjectName.OldValue":""}

The spath command extracts the *.NewValue and *.OldValue fields from the tmp field.

Note that empty values will be empty strings and null values will have the string value 'null'. If you want null values to be null fields, you can use the foreach command the nullif() eval function to override them:

| foreach Group.*.NewValue [ eval "<<FIELD>>"=nullif('<<FIELD>>', "null") ]
| foreach Group.*.OldValue [ eval "<<FIELD>>"=nullif('<<FIELD>>', "null") ]

Search memory usage may be higher when using temporary fields to store and manipulate JSON objects in this way, and you may need to run multiple searches over smaller time ranges, depending on your user's search limits and workload policy.

[PickleRick]

This is again an example of data formatted in a way non-friendly to Splunk (at least in your case). While you can use @ITWhisperer 's search to get your results, bear in mind that it's not field extraction that happens in this search, it's data manipulation. You don't _have_ the fields in your data, you have to create them manually by manipulating and correlating other data extracted from events.

It is one of the hard decisions that must be made when exporting data do json. You have two possible approaches - one is to dynamically name the fields like

{ "Group1":  {  "NewValue":"a", "OldValue":"b" }, "Group2": { "NewValue":"c", "OldValue":"d" }}

Another is - as is in your case - to have what you have as field name, exported as a "label" to a constant name.

[ { "Name": "Group1", "NewValue": "a", "OldValue":"b"}, { " Name": "Group1", "NewValue": "c", "OldValue": "d" }}]

Each of those approaches has its pros and cons.

The first form is not very friendly if you want to do some aggregations and other similar manipulations because your fields are not statically named so you might have to do some strange things with foreach and wildcarding in stats when manipulating them. But you can search with conditions like "Group1.NewValue=a". But you can't do "stats count by GroupName" or something like that

The second form though doesn't "tie" values with the name - Splunk doesn't support structured data, it flattens the JSONs and XMLs on parsing - so you have to bend over backwards to get your specific values for interesting "field" and you can't simply use conditions like "Group1.NewValue=a" in your search. but you can do "stats count by Name".

So it's always something. One way or another your data format will end up being a burden.

[ITWhisperer]

Please share the raw text of your events, anonymised appropriately, in a code block, not a picture, to assist volunteers designing a solution to meet your requirements.

[att35]

Thanks @ITWhisperer 

I have updated the original post with event text.

[ITWhisperer]

What you have posted is not the raw text, and is therefore not valid JSON!

Having said that, try something like this

| spath ModifiedProperties{} output=ModifiedProperties
| eval ModifiedProperties=mvindex(ModifiedProperties,1)
| spath input=ModifiedProperties
| eval {Name}.NewValue=NewValue
| eval {Name}.OldValue=OldValue
| fields - Name NewValue OldValue ModifiedProperties