Splunk Search
Highlighted

Parsing of highly nested JSON events with arrays

Explorer

We want to parse highly nested jsons into expanded tables. We found that the following code works, given we apply the | rename . as _ as many times as deep the nesting is. Without replacing the "." Splunk does not make all fields and subfields available. Might there be a more generic solution?

index="adm_compute_qcheck" 
| rename *.* as *_*  | rename *.* as *_* | rename *.* as *_*  | rename *.* as *_* 
| rename *_{}* as ** | rename *{}_* as **  | rename *{}_* as ** 

Here the first part of the JSON:

[
                  {
                      "BIOS":  {
                                   "manufacturer":  "INSYDE Corp.",
                                   "SystemBiosMajorVersion":  0,
                                   "SystemBiosMinorVersion":  41,
                                   "SMBIOSBIOSVersion":  "0.99"
                               }
                  },
                  {
                      "Checkpoint":  {

                                     }
                  },
                  {
                      "ClusterName":  null
                  },
                  {
                      "CPUType":  {
                                      "NumberOfCores":  16
                                  }
                  },
                  {
                      "HBA":  [
                                  {
                                      "active":  true,
                                      "drivername":  "elxfc",
                                      "driverversion":  "12.2.207.0",
                                      "firmwareversion":  "11.4.204.25",
                                      "optionromversion":  "11.4.204.25",
                                      "manufacturer":  "Emulex Corporation",
                                      "model":  "LPe32002-AP",
                                      "serialnumber":  "FC83980875"
                                  },
                                  {
                                      "active":  true,
                                      "drivername":  "elxfc",
                                      "driverversion":  "12.2.207.0",
                                      "firmwareversion":  "11.4.204.25",
                                      "optionromversion":  "11.4.204.25",
                                      "manufacturer":  "Emulex Corporation",
                                      "model":  "LPe32002-AP",
                                      "serialnumber":  "FC83980875"
                                  }
                              ]
                  },
                  {
                      "HPE":  [

                              ]
                  },
                  {
                      "HPEDiskCount":  0
                  },
                  {
                      "HPELogicalDisks":  {
                                              "Status":  null,
                                              "RaidLevel":  null,
                                              "ID":  null,
                                              "Capacity":  null
                                          }
                  },
                  {
                      "HPEPhysicalDisks":  [

                                           ]
                  },
                  {
                      "Mig":  {
                                  "VirtualMachineMigrationEnabled":  true,
                                  "VirtualMachineMigrationPerformanceOption":  2,
                                  "VirtualMachineMigrationAuthenticationType":  1,
                                  "MaximumVirtualMachineMigrations":  2,
                                  "MaximumStorageMigrations":  2
                              }
                  },
0 Karma
Highlighted

Re: Parsing of highly nested JSON events with arrays

SplunkTrust
SplunkTrust

Hi,

Running spath against the JSON block you included here seems to extract everything:

| makeresults
| eval myJSON = "
 [
                   {
                       \"BIOS\":  {
                                    \"manufacturer\":  \"INSYDE Corp.\",
                                    \"SystemBiosMajorVersion\":  0,
                                    \"SystemBiosMinorVersion\":  41,
                                    \"SMBIOSBIOSVersion\":  \"0.99\"
                                }
                   },
                   {
                       \"Checkpoint\":  {

                                      }
                   },
                   {
                       \"ClusterName\":  null
                   },
                   {
                       \"CPUType\":  {
                                       \"NumberOfCores\":  16
                                   }
                   },
                   {
                       \"HBA\":  [
                                   {
                                       \"active\":  true,
                                       \"drivername\":  \"elxfc\",
                                       \"driverversion\":  \"12.2.207.0\",
                                       \"firmwareversion\":  \"11.4.204.25\",
                                       \"optionromversion\":  \"11.4.204.25\",
                                       \"manufacturer\":  \"Emulex Corporation\",
                                       \"model\":  \"LPe32002-AP\",
                                       \"serialnumber\":  \"FC83980875\"
                                   },
                                   {
                                       \"active\":  true,
                                       \"drivername\":  \"elxfc\",
                                       \"driverversion\":  \"12.2.207.0\",
                                       \"firmwareversion\":  \"11.4.204.25\",
                                       \"optionromversion\":  \"11.4.204.25\",
                                       \"manufacturer\":  \"Emulex Corporation\",
                                       \"model\":  \"LPe32002-AP\",
                                       \"serialnumber\":  \"FC83980875\"
                                   }
                               ]
                   },
                   {
                       \"HPE\":  [

                               ]
                   },
                   {
                       \"HPEDiskCount\":  0
                   },
                   {
                       \"HPELogicalDisks\":  {
                                               \"Status\":  null,
                                               \"RaidLevel\":  null,
                                               \"ID\":  null,
                                               \"Capacity\":  null
                                           }
                   },
                   {
                       \"HPEPhysicalDisks\":  [

                                            ]
                   },
                   {
                       \"Mig\":  {
                                   \"VirtualMachineMigrationEnabled\":  true,
                                   \"VirtualMachineMigrationPerformanceOption\":  2,
                                   \"VirtualMachineMigrationAuthenticationType\":  1,
                                   \"MaximumVirtualMachineMigrations\":  2,
                                   \"MaximumStorageMigrations\":  2
                               }
                   },
]
"
| spath input=myJSON
| fields - myJSON, _time

Output:

alt text
alt text

Can you give us an example of a field that Splunk is not making available and the SPL you are using?

0 Karma
Highlighted

Re: Parsing of highly nested JSON events with arrays

Ultra Champion

props.conf

[adm_compute_qcheck]
INDEXED_EXTRACTIONS = JSON
JSON_TRIM_BRACES_IN_ARRAY_NAMES = true
KV_MODE = none

please add your setting.

0 Karma
Speak Up for Splunk Careers!

We want to better understand the impact Splunk experience and expertise has has on individuals' careers, and help highlight the growing demand for Splunk skills.