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

[rps462]

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!

[yuanliu · Accepted solution]

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

key	value	expire
10.0.0.1	some v4 ip	1749267900
2001:53f1:3:2ee:2252:12e3:228a:112a	some v6 ip

[rps462]

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.

[yuanliu]

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

key	value	expire
10.0.0.1	some v4 ip	1749267900
2001:53f1:3:2ee:2252:12e3:228a:112a	some v6 ip
10.0.0.2	some v4 ip	1749267900
2001:53f1:3:2ee:2252:12e3:228a:112b	some 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.)

[rps462]

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:

Thank you!

[rps462]

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 !