Archive

extracting value from complex JSON

New Member

Hi, need help on how to extract dat from this JSON.
i have used spath to extract a part of my JSON to get this data structure:
{
<"app name1">: {
"reason": "all ok",
"upstreams": 1,
"dialouts": {
"idle": 40,
"total": 40,
"connecting": 0
},

},
<"app name2": {
"reason": "all ok",
"upstreams": 2,
"dialouts": {
"idle": 15,
"total": 15,
"connecting": 0
},

},
<"app name3": {
"reason": "all ok",
"upstreams": 2,
"dialouts": {
"idle": 15,
"total": 15,
"connecting": 0
},

},
.....
}

what i want to do is to have a table to for each i have the idle, total and connecting from "dialouts":
how can i do that?

Tags (1)
0 Karma

Esteemed Legend

Like this:

| makeresults 
| eval _raw="{
\"app name1\": { \"reason\": \"all ok\", \"upstreams\": 1, \"dialouts\": { \"idle\": 40, \"total\": 40, \"connecting\": 0 }, },
\"app name2\": { \"reason\": \"all ok\", \"upstreams\": 2, \"dialouts\": { \"idle\": 15, \"total\": 15, \"connecting\": 0 }, },
\"app name3\": { \"reason\": \"all ok\", \"upstreams\": 2, \"dialouts\": { \"idle\": 15, \"total\": 15, \"connecting\": 0 }, }
}"
| rename COMMENT AS "Everything above generates sample event data; everything below is your solution"

| rex field=_raw max_match=0 "(?ms).*?(?<event>\"app name.*?},\s*\})(?=[\r\n\s\",]+(?:\}|app name))"
| mvexpand event
| rename event AS _raw
| eval _raw = "{" . _raw . "}"
| rex "(?<app_name>app[^\"]+)"
| rex mode=sed "s/app name\d+/app/"
| spath
| rename app.* AS *
| table app_name dialouts.*
0 Karma

New Member

I think you can regex to extract 2 element:
- <"app name2" => save fields app_name
- { "reason": "all ok", "upstreams": 2, "dialouts": { "idle": 15, "total": 15, "connecting": 0 }, } => data_app_name

after, use

|spath input=data_app_name

, will extract all data values in data_app_name.

0 Karma

New Member

can you add the regex itself? not so strong in regex

0 Karma

New Member

You can write 2 regex:
1. extract app_name: https://regex101.com/r/vU0fJI/1
2. extract data: https://regex101.com/r/vU0fJI/3

I just thought of another way.
Can remove characters . It's example, if wrong syntax, please fix it.
| rex mode=sed "s/<//g"
| rex mode=sed "s/>//g"

0 Karma
State of Splunk Careers

Access the Splunk Careers Report to see real data that shows how Splunk mastery increases your value and job satisfaction.

Find out what your skills are worth!