topic extracting value from complex JSON in Splunk Search

extracting value from complex JSON

mcohen13 — Thu, 03 Oct 2019 08:59:25 GMT

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?

Re: extracting value from complex JSON

longnh26 — Wed, 30 Sep 2020 02:28:13 GMT

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.

Re: extracting value from complex JSON

mcohen13 — Thu, 03 Oct 2019 11:09:51 GMT

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

Re: extracting value from complex JSON

longnh26 — Fri, 04 Oct 2019 01:55:58 GMT

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"

Re: extracting value from complex JSON

woodcock — Sat, 05 Oct 2019 17:13:37 GMT

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.*