CODEWITHSUNDEEP
splunksplunk-querysplunk-dashboard
stricq
stricq

Reputation: 856

In Splunk, Need to Pull Data from Nested JSON Array in an Array

I have some data that is an array inside an array. If it makes it easier, there will only ever be the one nested array inside of StopData.

{
  "Name": "ExceptionLogs",
  "Id": "Id1",
  "StopData": [
    [
      {
        "level": "ERROR",
        "code": "UCV019",
        "description": "Invalid port type",
        "detail": "100000 Mbps"
      },
      {
        "level": "WARN",
        "code": "UCV023",
        "description": "Unrecognized key for config option",
        "detail": "sourceSystemProductName"
      }
    ]
  ]
}

I would like to have the fields of objects in a table, level, code, description, and detail. Also, this is one row in splunk. There will be many rows where the Id field will be different. And there can be many entries in the array. I'd like to have all the rows in one long table.

Id  level code   description         detail
--- ----- ----   -----------         ------
Id1 ERROR UCV019 Invalid port type   100000 Mbps
Id1 WARN  UCV023 Unrecognized key... sourceSystemProductName
Id2 ERROR UCL013 Invalid Config      334115687

I have tried searching and searching but I am unable to piece together anything that shows any data at all.

Upvotes: 2

Views: 2940

Answers (2)

Daniel Price
Daniel Price

Reputation: 483

Alternative Answer, Similar to Richg's answer That fixes an issue if any of the fields you need to extract in the json list do not exist in every element, they will not be extracted correctly.

Also this avoids MVZIP which can be a nightmare to add more fields to extract and works for all fields in the json list.

| spath 
| spath StopData{}{} output=StopData
| mvexpand StopData
| spath input=StopData
| fields - StopData _raw _time

Example 1:

  | makeresults 
  | eval _raw="{
    \"Name\": \"ExceptionLogs\",
    \"Id\": \"Id1\",
    \"StopData\": [
      [
        {
          \"level\": \"ERROR\",
          \"code\": \"UCV019\",
          \"description\": \"Invalid port type\",
          \"detail\": \"100000 Mbps\"
            },
        {
          \"level\": \"WARN\",
          \"code\": \"UCV023\",
          \"description\": \"Unrecognized key for config option\",
          \"detail\": \"sourceSystemProductName\",
          \"test\":\"123\"
        }
      ]
    ]
  }"

Example 2:

| makeresults 
| eval _raw="{
  \"Name\": \"ExceptionLogs\",
  \"Id\": \"Id1\",
  \"StopData\": [
    [
      {
        \"level\": \"ERROR\",
        \"code\": \"UCV019\",
        \"description\": \"Invalid port type\",
        \"detail\": \"100000 Mbps\"
          },
      {
        \"level\": \"WARN\",
        \"code\": \"UCV023\",
        \"description\": \"Unrecognized key for config option\",
        \"detail\": \"sourceSystemProductName\",
        \"test\":\"123\"
      },
      {
        \"level\": \"INFO\",
        \"code\": \"UCV021\",
        \"description\": \"Price key for config option\",
        \"detail\": \"sourceSystemProductPrice\",
        \"test\":\"456\"
      }
    ]
  ]
}"

Upvotes: 2

RichG
RichG

Reputation: 9916

It would help to know what searches you've tried so we can help you learn why you got no results.

Start with the spath command to parse the JSON data into fields. That will give you a few multi-value fields for each Id. If we only had a single multi-value field then we'd use mvexpand to break it into separate events, but that won't work with several fields. To work around that, use mvzip to combine all multi-value fields into a single multi-value field then expand it and unzip it.

Try this run-anywhere example query.

| makeresults | eval _raw="{
  \"Name\": \"ExceptionLogs\",
  \"Id\": \"Id1\",
  \"StopData\": [
    [
      {
        \"level\": \"ERROR\",
        \"code\": \"UCV019\",
        \"description\": \"Invalid port type\",
        \"detail\": \"100000 Mbps\"
      },
      {
        \"level\": \"WARN\",
        \"code\": \"UCV023\",
        \"description\": \"Unrecognized key for config option\",
        \"detail\": \"sourceSystemProductName\"
      }
    ]
  ]
}" 
```Everything above is just test data.  Don't use IRL```
```Parse the JSON```
| spath 
```Simplify the field names```
| rename "StopData{}{}.*" as *
```Combine the multi-value fields```
| eval deets=mvzip(level,mvzip(code, mvzip(description,detail)))
```Create separate events for the fields in each Id```
| mvexpand deets
```Unzip the multi-value fields```
| eval deets=split(deets,",")
| eval level=mvindex(deets,0), code=mvindex(deets,1), description=mvindex(deets,2), detail=mvindex(deets,3)
```Display the results```
| table Id level code   description         detail

Upvotes: 1

Related Questions