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!

Splunk Answers Content Calendar, June Edition

Get ready for this week’s post dedicated to Splunk Dashboards! We're celebrating the power of community by ...

What You Read The Most: Splunk Lantern’s Most Popular Articles!

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

See your relevant APM services, dashboards, and alerts in one place with the updated ...

As a Splunk Observability user, you have a lot of data you have to manage, prioritize, and troubleshoot on a ...