topic Parsing of highly nested JSON events with arrays in Splunk Search

Parsing of highly nested JSON events with arrays

wfskmoney — Mon, 20 Apr 2020 09:29:38 GMT

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
                              }
                  },

Re: Parsing of highly nested JSON events with arrays

javiergn — Mon, 20 Apr 2020 09:48:44 GMT

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:

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

Re: Parsing of highly nested JSON events with arrays

to4kawa — Mon, 20 Apr 2020 10:20:11 GMT

props.conf

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

please add your setting.