Splunk Search

How to create a table using SPATH usage on simple JSON?

rps462
Path Finder

Hi All -

I am working with a very simple database that stores lists of key=value pairs with a potential expiration date and provides a REST API that outputs this data in JSON.

I've played with spath for a few hours now and am completely stumped.

Note: The JSON retrieved is not from a search or from another data input. It's from a custom curl command that creates its own results and displays them. I do not believe modifying the kv_mode on this app I'm working on would have any effect.

Here is an example of the data I'm working with. Each entry in the object is an IP address, with a value and an optional expiration along with it.

 

{
	"ip_addresses": {
		"10.0.0.1": {
			"value": "some v4 ip",
			"expire": 1749267900
		},
		"2001:53f1:3:2ee:2252:12e3:228a:112a": {
			"value": "some v6 ip"
		}
	}
}

 


I need to be able to display this information in a table like:

Key Value Expiration
10.0.0.1 some v4 ip 1749267900
2001:53f1:3:2ee:2252:12e3:228a:112a some v6 ip  

 

Any help on this would be greatly appreciated. Thank you!

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

yuanliu
SplunkTrust
SplunkTrust

This is very similar to https://community.splunk.com/t5/Splunk-Search/How-to-Extract-JSON-format-as-fields/m-p/586925.  What makes this one different is that the missing portion is in field name, not array value. But it can be handled.  Here is a test with the sample data

 

``` after spath ```
| foreach ip_addresses.*.value
    [eval keyvalue=mvappend(keyvalue, "<<MATCHSTR>>" . "|" . '<<FIELD>>')]
| mvexpand keyvalue
| eval key=mvindex(split(keyvalue, "|"), 0), value=mvindex(split(keyvalue, "|"), 1)
| foreach ip_addresses.*.expire
    [eval mvexpire=mvappend(mvexpire, "<<MATCHSTR>>" . "|" . '<<FIELD>>')]
| eval expire=mvindex(mvexpire, mvfind(mvexpire, "^" . replace(key, "\.", "\\.") . "\|"))
| eval expire=mvindex(split(expire, "|"), 1)
``` end ```
| table key value expire

 

keyvalueexpire
10.0.0.1some v4 ip1749267900
2001:53f1:3:2ee:2252:12e3:228a:112asome v6 ip 

View solution in original post

yuanliu
SplunkTrust
SplunkTrust

This is very similar to https://community.splunk.com/t5/Splunk-Search/How-to-Extract-JSON-format-as-fields/m-p/586925.  What makes this one different is that the missing portion is in field name, not array value. But it can be handled.  Here is a test with the sample data

 

``` after spath ```
| foreach ip_addresses.*.value
    [eval keyvalue=mvappend(keyvalue, "<<MATCHSTR>>" . "|" . '<<FIELD>>')]
| mvexpand keyvalue
| eval key=mvindex(split(keyvalue, "|"), 0), value=mvindex(split(keyvalue, "|"), 1)
| foreach ip_addresses.*.expire
    [eval mvexpire=mvappend(mvexpire, "<<MATCHSTR>>" . "|" . '<<FIELD>>')]
| eval expire=mvindex(mvexpire, mvfind(mvexpire, "^" . replace(key, "\.", "\\.") . "\|"))
| eval expire=mvindex(split(expire, "|"), 1)
``` end ```
| table key value expire

 

keyvalueexpire
10.0.0.1some v4 ip1749267900
2001:53f1:3:2ee:2252:12e3:228a:112asome v6 ip 

rps462
Path Finder

Sorry to bother .. a few last questions on this (I hope):

For the purposes of having some lists at the top of the list, they have a "_" in front of them. So, if in the example the list was named "_ip_address", it doesn't seem to work. I'm guessing it had something to do with:

 

| foreach _ip_address.*.value
    [eval keyvalue=mvappend(keyvalue, "<<MATCHSTR>>" . "|" . '<<FIELD>>')]

 

 

I get the error: Field 'keyvalue' does not exist in the data.

I get the same error if there are no records in a particular list. I was trying to figure out how to set it if it didn't exist, but wasn't sure where to do that.

Thanks again for your help.

0 Karma

yuanliu
SplunkTrust
SplunkTrust

In my test, whether the field name is ip_addresses or _ip_addresses, "foreach ip_addresses.*.value" matches just as well.  So, you shouldn't have to make any change.  How does it work in your case?

Below, one simulated event uses ip_addresses, the other _ip_addresses.

 

| makeresults
| eval data=mvappend("{
        \"ip_addresses\": {
                \"10.0.0.1\": {
                        \"value\": \"some v4 ip\",
                        \"expire\": 1749267900
                },
                \"2001:53f1:3:2ee:2252:12e3:228a:112a\": {
                        \"value\": \"some v6 ip\"
                }
        }
}","{
        \"_ip_addresses\": {
                \"10.0.0.2\": {
                        \"value\": \"some v4 ip\",
                        \"expire\": 1749267900
                },
                \"2001:53f1:3:2ee:2252:12e3:228a:112b\": {
                        \"value\": \"some v6 ip\"
                }
        }
}")
| mvexpand data
| rename data as _raw
| spath
``` simulate data ```

 

 

The search works in both

keyvalueexpire
10.0.0.1some v4 ip1749267900
2001:53f1:3:2ee:2252:12e3:228a:112asome v6 ip 
10.0.0.2some v4 ip1749267900
2001:53f1:3:2ee:2252:12e3:228a:112bsome v6 ip 

 

(If there is neither ip_address nor _ip_address in raw data, yes, mvexpand will not have data to work with. The warning is therefore expected.)

0 Karma

rps462
Path Finder

Hi, thank you again for your response.

Generating data from a list that starts with _ seems to be ok, the error occurs after:

 

| foreach _ip_addresses.*.value
    [eval keyvalue=mvappend(keyvalue, "<<MATCHSTR>>" . "|" . '<<FIELD>>')]
| mvexpand keyvalue

 

 

Specifically, it's "mvexpand keyvalue" that generates the error, because apparently "keyvalue" contains no data.

When I run this search here:

 

| makeresults
| eval data=mvappend("{
        \"ip_addresses\": {
                \"10.0.0.1\": {
                        \"value\": \"some v4 ip\",
                        \"expire\": 1749267900
                },
                \"2001:53f1:3:2ee:2252:12e3:228a:112a\": {
                        \"value\": \"some v6 ip\"
                }
        }
}","{
        \"_ip_addresses\": {
                \"10.0.0.2\": {
                        \"value\": \"some v4 ip\",
                        \"expire\": 1749267900
                },
                \"2001:53f1:3:2ee:2252:12e3:228a:112b\": {
                        \"value\": \"some v6 ip\"
                }
        }
}")
| mvexpand data
| rename data as _raw
| spath
| foreach _ip_addresses.*.value
    [eval keyvalue=mvappend(keyvalue, "<<MATCHSTR>>" . "|" . '<<FIELD>>')]
| mvexpand keyvalue

 

 

I get the error: image.png

Thank you!

0 Karma

rps462
Path Finder

Thank you very much for this!!! 

This is exactly what I needed here and based on the solution, I know there's no chance I would have come even close!

I don't have to ask questions in here too often because I can usually find a solution in another answer. So grateful for everybody that responds.

Thanks @yuanliu !

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 ...