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!

Dashboards: Hiding charts while search is being executed and other uses for tokens

There are a couple of features of SimpleXML / Classic dashboards that can be used to enhance the user ...

Splunk Observability Cloud's AI Assistant in Action Series: Explaining Metrics and ...

This is the fourth post in the Splunk Observability Cloud’s AI Assistant in Action series that digs into how ...

Brains, Bytes, and Boston: Learn from the Best at .conf25

When you think of Boston, you might picture colonial charm, world-class universities, or even the crack of a ...