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!

.conf24 | Day 0

Hello Splunk Community! My name is Chris, and I'm based in Canberra, Australia's capital, and I travelled for ...

Enhance Security Visibility with Splunk Enterprise Security 7.1 through Threat ...

(view in My Videos)Struggling with alert fatigue, lack of context, and prioritization around security ...

Troubleshooting the OpenTelemetry Collector

  In this tech talk, you’ll learn how to troubleshoot the OpenTelemetry collector - from checking the ...