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 | result | version | data |
serial_a | PASS | 123 | data1, data2, data3 |
serial_b | PASS | 456 | data4, data5, |
serial_c | fail | 789 | 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!
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
location | data | result | serial_number | version |
US | data1,data2,data3 | PASS | serial_a | 123 |
US | data4,data5 | PASS | serial_b | 456 |
US | data6,data7 | FAIL | serial_c | 789 |
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 ```