Splunk Search

Working with periods in spath command

Rjbeckwith
Explorer

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?

Labels (2)
0 Karma
1 Solution

manjunathmeti
SplunkTrust
SplunkTrust

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.

View solution in original post

manjunathmeti
SplunkTrust
SplunkTrust

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.

0 Karma

Rjbeckwith
Explorer

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)

0 Karma

manjunathmeti
SplunkTrust
SplunkTrust

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.

Rjbeckwith
Explorer

That worked thank you!

0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...