topic Working with periods in spath command in Splunk Search

Working with periods in spath command

Rjbeckwith — Wed, 10 Mar 2021 15:35:25 GMT

I have a lot of json data that contains periods in the keys. I want to be able to expand one of the arrays in the data with the spath command. It does not seem to work with a period in the json data in the simple example below:

| makeresults | eval _raw=" { \"content\":{ \"jvm.memory\": [{\"num\":1.0},{\"num\":2.0}] } }" | spath | spath path=content.jvm.memory{} output=event_data | mvexpand event_data | eval _raw=event_data | kv

The following query does work with an underscore in the key name.

| makeresults | eval _raw=" { \"content\":{ \"jvm_memory\": [{\"num\":1.0},{\"num\":2.0}] } }" | spath | spath path=content.jvm_memory{} output=event_data | mvexpand event_data | eval _raw=event_data | kv

Are there any ways to work around the periods in the keys? Maybe some sort of mass replace of the periods in the key names only (not the values) or some sort of way to escape the periods in the spath command?

Re: Working with periods in spath command

manjunathmeti — Wed, 10 Mar 2021 16:51:59 GMT

You can rename the field with dots in name.

| makeresults | eval _raw=" { \"content\":{ \"jvm.memory\": [{\"num\":1.0},{\"num\":2.0}] } }" | spath | rename content.jvm.memory{}.* as * | mvexpand num

If this reply helps you, an upvote/like would be appreciated.

Re: Working with periods in spath command

Rjbeckwith — Wed, 10 Mar 2021 20:21:52 GMT

Thank you for the response. That did work for that simple example. Unfortunately my data has a lot of other fields underneath the array so I cannot specify the specific field to mvexpand.

In this slightly more complicated example I am not able to unroll by just specifying one field like in the solution you provided. (My dataset has many keys underneath the jvm_memory field so naming them manually won't work)

| makeresults | eval _raw=" { \"misc_field\": 0, \"content\":{ \"jvm_memory\": [{\"num\":1.0, \"data\": {\"test\":2.4, \"test2\": 2.3}},{\"num\":2.0, \"data\":{\"test\":3, \"test2\": 2.3}}], \"field_test2\": 3 } }" | spath | spath path=content.jvm_memory{} output=event_data | mvexpand event_data | eval _raw=event_data | kv

This above yields what I want (The Json array elements have been split into separate rows and the keys have become columns)

Re: Working with periods in spath command

manjunathmeti — Thu, 11 Mar 2021 06:23:42 GMT

Then try this:

| makeresults | eval _raw=" { \"misc_field\": 0, \"content\":{ \"jvm.memory\": [{\"num\":1.0, \"data\": {\"test\":2.4, \"test2\": 2.3}},{\"num\":2.0, \"data\":{\"test\":3, \"test2\": 2.3}}], \"field_test2\": 3 } }" | spath path=content output=content | eval content=replace(content, "jvm\.memory", "jvm_memory") | spath input=content path=jvm_memory{} output=event_data | mvexpand event_data | spath input=event_data

If this reply helps you, an upvote/like would be appreciated.

Re: Working with periods in spath command

Rjbeckwith — Thu, 11 Mar 2021 15:18:40 GMT

That worked thank you!