Splunk Search

Grab json value with dynamic key from splunk

darrfang
Engager

Hi everyone,

 

I have a json data payload as below:

 

 

{
    location: US
    all_results: {
      serial_a: {
        result: PASS,
        version: 123,
        data:[
          data1,
          data2,
          data3
        ]
      },
      serial_b: {
        result: PASS,
        version: 456,
        data:[
          data4,
          data5
        ]
      },
      serial_c: {
        result: FAIL,
        version: 789,
        data:[
          data6,
          data7
        ]
      }
    }
  }

 

 

 

 

and I would like to use splunk query and make a table as:

serial_number resultversion data
serial_a
PASS
123
data1,
data2,
data3
serial_b
PASS
456
data4,
data5,
serial_c
fail789
data6,
data7

 

how to use splunk query to organize the result?
I know I'm able to grab the data by:
| spath path=all_results output=all_results
| eval all_results=json_extract(all_results)

The difficult part is at the serial_number. They have some common prefix serial, but it's dynamic. Therefore , when I try to grab the data inside serial_number, for example version, I'm not able to use query like:
| spath  output=version path=all_result.serial*.version

Could you give me some idea to do that? thank you!

Labels (3)
0 Karma

yuanliu
SplunkTrust
SplunkTrust

First, thank you for using text to illustrate data, and clearly present desired result.  But next time make sure you preserve valid JSON syntax.  Your illustrated text is missing quotation marks required by JSON.  Correcting for syntax, I assume that the original data would look like

 

{
    "location": "US",
    "all_results": {
      "serial_a": {
        "result": "PASS",
        "version": "123",
        "data":[
          "data1",
          "data2",
          "data3"
        ]
      },
      "serial_b": {
        "result": "PASS",
        "version": "456",
        "data":[
          "data4",
          "data5"
        ]
      },
      "serial_c": {
        "result": "FAIL",
        "version": "789",
        "data":[
          "data6",
          "data7"
        ]
      }
    }
  }

 

This same ask has come several times recently, and there are several ways to do this.  This time, I'll try something new, and less roundabout in logic.

 

| fields location
| spath path=all_results
| fields - _*
| eval serial_number = json_array_to_mv(json_keys(all_results))
| mvexpand serial_number
| eval all_results = json_extract(all_results, serial_number)
| spath input=all_results
| fields - all_results
| rename data{} as data
| eval data = mvjoin(data, ",")

 

You data gives

locationdataresultserial_numberversion
USdata1,data2,data3PASSserial_a123
USdata4,data5PASSserial_b456
USdata6,data7FAILserial_c789

This is an emulation for you to play around and compare with real data

 

| makeresults
| eval _raw = "{
    \"location\": \"US\",
    \"all_results\": {
      \"serial_a\": {
        \"result\": \"PASS\",
        \"version\": \"123\",
        \"data\":[
          \"data1\",
          \"data2\",
          \"data3\"
        ]
      },
      \"serial_b\": {
        \"result\": \"PASS\",
        \"version\": \"456\",
        \"data\":[
          \"data4\",
          \"data5\"
        ]
      },
      \"serial_c\": {
        \"result\": \"FAIL\",
        \"version\": \"789\",
        \"data\":[
          \"data6\",
          \"data7\"
        ]
      }
    }
  }"
| spath
``` data emulation above ```

 

 

0 Karma
Get Updates on the Splunk Community!

Splunk Answers Content Calendar, June Edition

Get ready for this week’s post dedicated to Splunk Dashboards! We're celebrating the power of community by ...

What You Read The Most: Splunk Lantern’s Most Popular Articles!

Splunk Lantern is a Splunk customer success center that provides advice from Splunk experts on valuable data ...

See your relevant APM services, dashboards, and alerts in one place with the updated ...

As a Splunk Observability user, you have a lot of data you have to manage, prioritize, and troubleshoot on a ...