topic Re: extract multivalue nested json in Getting Data In

extract multivalue nested json

hugo_vazquez — Tue, 29 Sep 2020 20:06:09 GMT

I have a multivalve nested json that I need to parse, auto_kv_json is enabled on my props.conf file, and it is extracting most of my key values. But for some reason, there are a few that splunk is not extracting, I can see those values if I check the raw data, but splunk won't present them to me in the results as json data.
This is how my json looks:

"some_name": {
"my.very.nested.json.output.some.more.strings.tomakeitcomplicated": {
"count": 1,
"max": 0.5,
"mean": 0.1,
"min": 0.092808133,
"mean_rate": 0.30310791967810413,
"duration_units": "seconds",
"rate_units": "calls/second"
},

I need to extract the count, so I can present it on a table.

Re: extract multivalue nested json

cpetterborg — Wed, 20 Jun 2018 04:46:20 GMT

What is extracted and what isn't extracted? And is there supposed to be an ending } that you have left off?

Re: extract multivalue nested json

hugo_vazquez — Wed, 20 Jun 2018 15:52:03 GMT

Thanks you cpetterborg.
I need to extract the count, so I can present it on a table.
"count": 1,

You're right there's a } missing, but this is because I'm showing only a part of the entire json, which is huge.
The json string is fine, splunk is extracting most of the key values, the problem is with only a few, like the one in the example I posted

Re: extract multivalue nested json

cpetterborg — Wed, 20 Jun 2018 17:40:01 GMT

This is a JSON string that is in a field, not the entire event, right? If that is the case, you cannot depend on Splunk to extract all the JSON fields (that could be expected if the event were only a JSON string and you configured it to be extracted that way).

I would suggest doing an auto-field-extraction for the sourcetype, which will take some regular expression knowledge, or using the field extraction tool (which has its own problems, but may work fine for this case). Without knowing the entire event contents it's hard (though not impossible) to provide a field extraction that would always work. As a quick hack at the regex:

"count":\s*(?P<count>\d+),

You could use that is an auto-field-extraction, or in a rex command like this:

... | rex field=<the-json-string-field-name> "\"count\":\s*(?P<count>\d+),"

If this is not producing good results for you, post more here about the problem.

Re: extract multivalue nested json

hugo_vazquez — Wed, 20 Jun 2018 23:28:30 GMT

This is a JSON string that is in a field, not the entire event, right? Right
Thanks for the regex, I tried it in the search bar and it will return the same results without the value I need(count=*)
I also tried extracting the field with no luck.I even checked the json with an online json viewer to make sure its a valid json.
What really troubles me is the fact that the result of my query won't change when I add the rex command

Re: extract multivalue nested json

cpetterborg — Wed, 20 Jun 2018 23:46:03 GMT

With the rex embedded in your search, do you get the field count having values in the events? If not, then the field is not available for you to use in your search.

From your example the rex I provided should extract the field count (unless you are calculating another count field using something like the stats command).

Re: extract multivalue nested json

saurabhkharkar — Mon, 17 Dec 2018 15:57:06 GMT

Try This ,

index=yourindex | rex field=_raw ".*?\"count\":\s(?\d+)\,"

Re: extract multivalue nested json

saurabhkharkar — Tue, 29 Sep 2020 22:29:39 GMT

index=yourindex | rex field=_raw ".*?\"count\":\s(?<_raw>\d+)\,"

Re: extract multivalue nested json

saurabhkharkar — Mon, 17 Dec 2018 19:53:02 GMT

in the regular expression replace the _raw before \d by count. Not sure why i cant type it in the search.

Re: extract multivalue nested json

macadminrohit — Mon, 17 Dec 2018 22:04:11 GMT

why not using the spath to traverse to the specific field in the nested json in your case it should be some_name."my.very.nested.json.output.some.more.strings.tomakeitcomplicated"."count"