topic Re: SPL help to parse JSON into new fields in Splunk Search

Is there a way for me to create a table output of the JSON keys?

Strangertinz — Thu, 22 Jun 2023 10:59:10 GMT

Hi Splunk Community!

Is there a way for me to create a table output of the output of the json keys below & (possibly filter the keys to be a different name as well to represent their uniqueness). Example below..

Json data:

{
"Key1": "Value1",
"Key2": {
"subKey2_1": "sub value1 for key2",
"Manifest": [
{
"flight": "start",
"City": "Los Angeles",
"code": 7870,
"Inventory": {
"snacks": 300,
"status": "full"
}
},
{
"flight": "end",
"City": "Las Vegas",
"code": 7470,
"Inventory": {
"snacks": 56,
"status": "near empty"
}
}
],
"subKey2_3": "sub value3 for key2"
},
"Key3": "Value3",
"Key4": "Value4"
}

I am looking to create a table with a single row of the fields and values below

Field = Value

city_origin = "Los Angeles"

code_origin = 7870

inventory_snacks_origin=300

inventory_status_origin="full"

city_end = "Las Vegas"

code_end = 7470

inventory_snacks_end=56

inventory_status_end="near empty"

Re: SPL help to parse JSON into new fields

Strangertinz — Thu, 22 Jun 2023 01:13:11 GMT

Sample picture of the goal output

Re: SPL help to parse JSON into new fields

yuanliu — Thu, 22 Jun 2023 04:26:23 GMT

Try this:

| spath path=Key2.Manifest{} | mvexpand Key2.Manifest{} | spath input=Key2.Manifest{} | foreach City Inventory.* code [eval <<FIELD>>_origin = if(flight == "start", '<<FIELD>>', null()), <<FIELD>>_end = if(flight == "end", '<<FIELD>>', null())] | stats values(*_*) as *_*

Your sample data gives

City_end	City_origin	Inventory.snacks_end	Inventory.snacks_origin	Inventory.status_end	Inventory.status_origin	code_end	code_origin
Las Vegas	Los Angeles	56	300	near empty	full	7470	7870

Here is a data emulation you can play with and compare with real data

| makeresults | eval _raw = "{ \"Key1\": \"Value1\", \"Key2\": { \"subKey2_1\": \"sub value1 for key2\", \"Manifest\": [ { \"flight\": \"start\", \"City\": \"Los Angeles\", \"code\": 7870, \"Inventory\": { \"snacks\": 300, \"status\": \"full\" } }, { \"flight\": \"end\", \"City\": \"Las Vegas\", \"code\": 7470, \"Inventory\": { \"snacks\": 56, \"status\": \"near empty\" } } ], \"subKey2_3\": \"sub value3 for key2\" }, \"Key3\": \"Value3\", \"Key4\": \"Value4\" }" ``` data emulation above ```

Re: SPL help to parse JSON into new fields

Strangertinz — Fri, 23 Jun 2023 02:31:33 GMT

This was perfect! Thanks!