(I have no experience in Splunk searches)
The question is simple:
I have a JSON like this:
{
"name": "Testdata",
"children": [
{
"name": "A",
"children": [
{
"name": "A1",
"value": 436
},
{
"name": "A2",
"value": 546
},
{
"name": "A3",
"value": 223
},
{
"name": "A4",
"value": 132
},
{
"name": "A5",
"value": 115
},
{
"name": "A6",
"value": 96
}]
},
{
"name": "B",
"children": [
{
"name": "B1",
"value": 453
},
{
"name": "B2",
"value": 344
},
{
"name": "B3",
"value": 35
},
{
"name": "B4",
"value": 65
},
{
"name": "B5",
"value": 789
},
{
"name": "B6",
"value": 648
},
{
"name": "B7",
"value": 147
}]
}
]
}
and I want a table like this:
parent |name |value
A |A1 | 436
A |A2 | 546
... |.. |..
B |B1 |443
(Sorry for the bad drawn table)
Thanks for the answers!
Use spath
to parse the json. Here is an example
| makeresults |eval json="{
\"name\": \"Testdata\",
\"children\": [
{
\"name\": \"A\",
\"children\": [
{
\"name\": \"A1\",
\"value\": 436
},
{
\"name\": \"A2\",
\"value\": 546
},
{
\"name\": \"A3\",
\"value\": 223
},
{
\"name\": \"A4\",
\"value\": 132
},
{
\"name\": \"A5\",
\"value\": 115
},
{
\"name\": \"A6\",
\"value\": 96
}]
},
{
\"name\": \"B\",
\"children\": [
{
\"name\": \"B1\",
\"value\": 453
},
{
\"name\": \"B2\",
\"value\": 344
},
{
\"name\": \"B3\",
\"value\": 35
},
{
\"name\": \"B4\",
\"value\": 65
},
{
\"name\": \"B5\",
\"value\": 789
},
{
\"name\": \"B6\",
\"value\": 648
},
{
\"name\": \"B7\",
\"value\": 147
}]
}
]
}"
|spath input=json|table children{}.name,children{}.children{}.name,children{}.children{}.value
|rename children{}.children{}.name as grand_child_name,children{}.name as child_name,children{}.children{}.value as grand_child_value
|eval zipped=mvzip(grand_child_name,grand_child_value)|table child_name,zipped|mvexpand zipped
|mvexpand child_name|eval x=split(zipped,",")|eval grand_child_name=mvindex(x,0),grand_child_value=mvindex(x,1)
|table child_name,grand_child_name,grand_child_value|eval match=substr(grand_child_name,0,1)|where child_name==match|fields - match
Use spath
to parse the json. Here is an example
| makeresults |eval json="{
\"name\": \"Testdata\",
\"children\": [
{
\"name\": \"A\",
\"children\": [
{
\"name\": \"A1\",
\"value\": 436
},
{
\"name\": \"A2\",
\"value\": 546
},
{
\"name\": \"A3\",
\"value\": 223
},
{
\"name\": \"A4\",
\"value\": 132
},
{
\"name\": \"A5\",
\"value\": 115
},
{
\"name\": \"A6\",
\"value\": 96
}]
},
{
\"name\": \"B\",
\"children\": [
{
\"name\": \"B1\",
\"value\": 453
},
{
\"name\": \"B2\",
\"value\": 344
},
{
\"name\": \"B3\",
\"value\": 35
},
{
\"name\": \"B4\",
\"value\": 65
},
{
\"name\": \"B5\",
\"value\": 789
},
{
\"name\": \"B6\",
\"value\": 648
},
{
\"name\": \"B7\",
\"value\": 147
}]
}
]
}"
|spath input=json|table children{}.name,children{}.children{}.name,children{}.children{}.value
|rename children{}.children{}.name as grand_child_name,children{}.name as child_name,children{}.children{}.value as grand_child_value
|eval zipped=mvzip(grand_child_name,grand_child_value)|table child_name,zipped|mvexpand zipped
|mvexpand child_name|eval x=split(zipped,",")|eval grand_child_name=mvindex(x,0),grand_child_value=mvindex(x,1)
|table child_name,grand_child_name,grand_child_value|eval match=substr(grand_child_name,0,1)|where child_name==match|fields - match
Converted this to an answer because it answers the question with an excellent run-anywhere example.
Thanks for your help! Works perfectly!
Thanks @DalJeanis !