Splunk Search

extracting value from complex JSON

mcohen13
Loves-to-Learn

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

woodcock
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

longnh26
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

mcohen13
Loves-to-Learn

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

0 Karma

longnh26
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
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...