Splunk Search

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

Strangertinz
Path Finder
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"
Labels (6)
Tags (2)
0 Karma
1 Solution

yuanliu
SplunkTrust
SplunkTrust

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_endCity_originInventory.snacks_endInventory.snacks_originInventory.status_endInventory.status_origincode_endcode_origin
Las VegasLos Angeles56300near emptyfull74707870

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

 

View solution in original post

0 Karma

yuanliu
SplunkTrust
SplunkTrust

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_endCity_originInventory.snacks_endInventory.snacks_originInventory.status_endInventory.status_origincode_endcode_origin
Las VegasLos Angeles56300near emptyfull74707870

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

 

0 Karma

Strangertinz
Path Finder

This was perfect! Thanks!

0 Karma

Strangertinz
Path Finder

Sample picture of the goal output


Screen Shot 2023-06-21 at 8.12.09 PM.png

0 Karma
Get Updates on the Splunk Community!

What's New in Splunk Cloud Platform 9.2.2403?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.2.2403! Analysts can ...

Stay Connected: Your Guide to July and August Tech Talks, Office Hours, and Webinars!

Dive into our sizzling summer lineup for July and August Community Office Hours and Tech Talks. Scroll down to ...

Edge Processor Scaling, Energy & Manufacturing Use Cases, and More New Articles on ...

Splunk Lantern is a Splunk customer success center that provides advice from Splunk experts on valuable data ...