topic Re: Not able to extract Nested JSON in Getting Data In

Not able to extract Nested JSON

surejsajeev — Sun, 16 May 2021 08:21:00 GMT

Hi,

I have an event that is an entire JSON. It looks something like this.

{

Key1 : {

KEY2: VAL2

KEY3: VAL3

....

KeyX : {

KEY2: VAL2

KEY3: VAL3

....

KeyY : {

KEY2: VAL2

KEY3: VAL3

....

}

Here Key1 and KeyX and KeyY are unknown to me, meaning they can change all the time. I would get around 100 such sub-dictionaries. I just was the sub-dictionary inside, as separate Splunk events.

{

KEY2: VAL2

KEY3: VAL3

....

}

I have tried a lot of different search queries using spath, but nothing seems to help.

Could someone please help me with this. I very much appreciate it.

Re: Not able to extract Nested JSON

ITWhisperer — Sun, 16 May 2021 11:27:44 GMT

There might be an easier way to do this, but you could extract all the fields with spath, then reconstruct the fields and values, removing the "unknown" part of the fieldname

| makeresults | eval _raw="{ \"KeyA\": { \"Key2\": 3, \"Key3\": 3 }, \"KeyE\": { \"Key2\": 5, \"Key3\": 4 }, \"KeyC\": { \"Key2\": 7, \"Key3\": 5 }, \"KeyH\": { \"Key2\": 9, \"Key3\": 6 }, \"KeyR\": { \"Key2\": 1, \"Key3\": 7 } }|{ \"KeyA\": { \"Key2\": 1, \"Key3\": 3 }, \"KeyE\": { \"Key2\": 2, \"Key3\": 4 }, \"KeyC\": { \"Key2\": 3, \"Key3\": 5 }, \"KeyH\": { \"Key2\": 4, \"Key3\": 6 }, \"KeyR\": { \"Key2\": 5, \"Key3\": 7 } }" | eval events=split(_raw,"|") | mvexpand events | fields - _* | rename events as _raw | spath | fields - _raw | foreach *.* [| eval Group_<<MATCHSEG1>>=if(isnull(Group_<<MATCHSEG1>>),"<<MATCHSEG2>>:".'<<FIELD>>',mvappend(Group_<<MATCHSEG1>>,"<<MATCHSEG2>>:".'<<FIELD>>'))] | fields *_* | foreach *_* [| eval events=if(isnull(events),mvjoin(<<FIELD>>,"|"),mvappend(events,mvjoin(<<FIELD>>,"|")))] | fields events | mvexpand events | streamstats count as row | eval events=split(events,"|") | mvexpand events | eval key=mvindex(split(events,":"),0) | eval value=mvindex(split(events,":"),1) | eval {key}=value | fields - key value events | stats values(*) as * by row | fields - row

This assume a two level nesting in JSON as shown in your example. More complex JSON formats might not work so well.

Another way (perhaps simpler) would be to replace all the "unknown" values with known values

Re: Not able to extract Nested JSON

surejsajeev — Mon, 17 May 2021 01:28:20 GMT

Thank you very much @ITWhisperer for the solution. The query works perfectly. However when I run it, I get this error message from splunk

"The search you ran returned a number of fields that exceeded the current indexed field extraction limit. To ensure that all fields are extracted for search, set limits.conf: [kv] / indexed_kv_limit to a number that is higher than the number of fields contained in the files that you index."

Could you advise on how I can resolve this issue, please? I am not sure of the no of fields that my query will generate. Any dynamic limit that I can see?

Your help is much appreciated.

Re: Not able to extract Nested JSON

ITWhisperer — Tue, 18 May 2021 06:00:08 GMT

Which of the two approaches gives you this error? Either way, you (or your splunk admin) should increase the limit of fields available in the configuration.

Re: Not able to extract Nested JSON

surejsajeev — Tue, 18 May 2021 06:01:50 GMT

it was the second one @ITWhisperer

Re: Not able to extract Nested JSON

ITWhisperer — Tue, 18 May 2021 06:08:23 GMT

You could try removing the unknown field once it has been used

Does the job inspector inform you how far through the search it got before it ran out of fields?

Re: Not able to extract Nested JSON

surejsajeev — Tue, 18 May 2021 06:24:25 GMT

@ITWhisperer

These are the message i see from the job inspector.

Forcefully terminated search process with sid=<SID> since its physical memory usage (34997.320000 MB) has exceeded the physical memory threshold specified in limits.conf/search_process_memory_usage_threshold (30000.000000 MB)

-----------------------------------------------------------

command.mvexpand: output will be truncated at 11700 results due to excessive memory usage. Memory threshold of 500MB as configured in limits.conf / [mvexpand] / max_mem_usage_mb has been reached.

-----------------------------------------------------------

This is the query that I am using

Re: Not able to extract Nested JSON

ITWhisperer — Tue, 18 May 2021 06:34:02 GMT

mvexpand has a memory limit - the first option might be more efficient in terms of memory usage since it removes _raw as soon as it is no longer required and similarly for other fields created along the way. If changing the configuration still isn't enough, then have a look at my post on ways around mvexpand limits

https://community.splunk.com/t5/Splunk-Search/mvexpand-limits/m-p/549178

Re: Not able to extract Nested JSON

surejsajeev — Tue, 18 May 2021 07:44:21 GMT

Thank you @ITWhisperer

I tried the first option in my query and it did not work (The reason why I used the second option). the error message from the first was this

Failed to parse templatized search for field 'ABC_DEB_SJW_AK_SIE_122_EL-3602110-134:checksumawq:wwrikahs.AEUUR

This is my query

base search | spath
| fields - _raw
| foreach *.*
[| eval Group_<<MATCHSEG1>>=if(isnull(Group_<<MATCHSEG1>>),"<<MATCHSEG2>>:".'<<FIELD>>',mvappend(Group_<<MATCHSEG1>>,"<<MATCHSEG2>>:".'<<FIELD>>'))]
| fields *_*
| foreach *_*
[| eval events=if(isnull(events),mvjoin(<<FIELD>>,"|"),mvappend(events,mvjoin(<<FIELD>>,"|")))]
| fields events
| mvexpand events
| streamstats count as row
| eval events=split(events,"|")
| mvexpand events
| eval key=mvindex(split(events,":"),0)
| eval value=mvindex(split(events,":"),1)
| eval {key}=value
| fields - key value events
| stats values(*) as * by row
| fields - row

Any idea on why this is? is it because of the "." ?

Re: Not able to extract Nested JSON

ITWhisperer — Tue, 18 May 2021 08:25:59 GMT

Possibly the "." but more likely the "-". Try putting the <<FIELD>> names in single quotes.

Re: Not able to extract Nested JSON

surejsajeev — Tue, 18 May 2021 08:31:39 GMT

@ITWhisperer

Changed the query to

index=edennet-self-organising-app-s sourcetype="_json" | spath
| fields - _raw
| foreach *.*
[| eval Group_<<MATCHSEG1>>=if(isnull(Group_<<MATCHSEG1>>),"<<MATCHSEG2>>:".'<<FIELD>>',mvappend(Group_<<MATCHSEG1>>,"<<MATCHSEG2>>:".'<<FIELD>>'))]
| fields *_*
| foreach *_*
[| eval events=if(isnull(events),mvjoin('<<FIELD>>',"|"),mvappend(events,mvjoin('<<FIELD>>',"|")))]
| fields events
| mvexpand events
| streamstats count as row
| eval events=split(events,"|")
| mvexpand events
| eval key=mvindex(split(events,":"),0)
| eval value=mvindex(split(events,":"),1)
| eval {key}=value
| fields - key value events
| stats values(*) as * by row
| fields - row

but still got the same error. The error occurs for strings without the "-" too. the strings consist of characters such as "," , "=", "_" and ":".

Re: Not able to extract Nested JSON

ITWhisperer — Tue, 18 May 2021 08:49:24 GMT

Perhaps you could share a more realistic anonymised example of the JSON you are dealing with as the initial made-up example does not appear to be representative enough. It often helps to have a more complete picture to work from.

Re: Not able to extract Nested JSON

surejsajeev — Tue, 18 May 2021 09:05:25 GMT

sure @ITWhisperer

This is how the JSON will look like.

{
"source_dict=source_name,source_dict=location,context=location_code,manage=1,function=1,data1=1,data2=12:source create": {
"COMMENT": "None",
"MODULE_DATA": "source_dict=source_name,source_dict=location,context=location_code,manage=1,function=1,data1=1,data2=12",
"MOD_1": "source_inst",
"MOD_2": "name_1",
"MOD_3": "sub_inst",
"MOD_4": "execute",
"MOD_5": "None",
"MOD_6": "XER1",
"MOD_7": "None",
"MOD_8": "source create",
"MOD_9": "A1",
"MOD_10": "None",
"MOD_11": "None",
"MOD_12": "None",
"TIMESTAMP": "test_time"
},
"source_dict=source_name,source_dict=location,context=location_code,manage=1,function=1,data1=1,data2=12:source change:param1": {
"COMMENT": "None",
"MODULE_DATA": "source_dict=source_name,source_dict=location,context=location_code,manage=1,function=1,data1=1,data2=12",
"MOD_1": "source_inst",
"MOD_2": "name_1",
"MOD_3": "sub_inst",
"MOD_4": "execute",
"MOD_5": "12",
"MOD_6": "XER1",
"MOD_7": "None",
"MOD_8": "source change",
"MOD_9": "A1",
"MOD_10": "param1",
"MOD_11": "None",
"MOD_12": "None",
"TIMESTAMP": "test_time"
},
"source_dict=source_name,source_dict=location,context=location_code,manage=1,function=1,data1=1,data2=12:source change:temo1aaa": {
"COMMENT": "None",
"MODULE_DATA": "source_dict=source_name,source_dict=location,context=location_code,manage=1,function=1,data1=1,data2=12",
"MOD_1": "source_inst",
"MOD_2": "name_1",
"MOD_3": "sub_inst",
"MOD_4": "execute",
"MOD_5": "-1231321",
"MOD_6": "XER1",
"MOD_7": "None",
"MOD_8": "source change",
"MOD_9": "A1",
"MOD_10": "temo1aaa",
"MOD_11": "None",
"MOD_12": "None",
"TIMESTAMP": "test_time"
}
}

This entire dictionary is considered as one single event in Splunk.

I wanted to parse this dictionary and extract the second-level dictionary. i.e. this part alone(see below), as an individual Splunk event, so that I can use spath on it and make it in the form of a table.

{
"COMMENT": "None",
"MODULE_DATA": "source_dict=source_name,source_dict=location,context=location_code,manage=1,function=1,data1=1,data2=12",
"MOD_1": "source_inst",
"MOD_2": "name_1",
"MOD_3": "sub_inst",
"MOD_4": "execute",
"MOD_5": "None",
"MOD_6": "XER1",
"MOD_7": "None",
"MOD_8": "source create",
"MOD_9": "A1",
"MOD_10": "None",
"MOD_11": "None",
"MOD_12": "None",
"TIMESTAMP": "test_time"
}

Re: Not able to extract Nested JSON

ITWhisperer — Tue, 18 May 2021 11:03:44 GMT

You may still have problems with mvexpand by how about this to deal with unusual names:

| makeresults | eval _raw="{ \"source_dict=source_name,source_dict=location,context=location_code,manage=1,function=1,data1=1,data2=12:source create\": { \"COMMENT\": \"None\", \"MODULE_DATA\": \"source_dict=source_name,source_dict=location,context=location_code,manage=1,function=1,data1=1,data2=12\", \"MOD_1\": \"source_inst\", \"MOD_2\": \"name_1\", \"MOD_3\": \"sub_inst\", \"MOD_4\": \"execute\", \"MOD_5\": \"None\", \"MOD_6\": \"XER1\", \"MOD_7\": \"None\", \"MOD_8\": \"source create\", \"MOD_9\": \"A1\", \"MOD_10\": \"None\", \"MOD_11\": \"None\", \"MOD_12\": \"None\", \"TIMESTAMP\": \"test_time\" }, \"source_dict=source_name,source_dict=location,context=location_code,manage=1,function=1,data1=1,data2=12:source change:param1\": { \"COMMENT\": \"None\", \"MODULE_DATA\": \"source_dict=source_name,source_dict=location,context=location_code,manage=1,function=1,data1=1,data2=12\", \"MOD_1\": \"source_inst\", \"MOD_2\": \"name_1\", \"MOD_3\": \"sub_inst\", \"MOD_4\": \"execute\", \"MOD_5\": \"12\", \"MOD_6\": \"XER1\", \"MOD_7\": \"None\", \"MOD_8\": \"source change\", \"MOD_9\": \"A1\", \"MOD_10\": \"param1\", \"MOD_11\": \"None\", \"MOD_12\": \"None\", \"TIMESTAMP\": \"test_time\" }, \"source_dict=source_name,source_dict=location,context=location_code,manage=1,function=1,data1=1,data2=12:source change:temo1aaa\": { \"COMMENT\": \"None\", \"MODULE_DATA\": \"source_dict=source_name,source_dict=location,context=location_code,manage=1,function=1,data1=1,data2=12\", \"MOD_1\": \"source_inst\", \"MOD_2\": \"name_1\", \"MOD_3\": \"sub_inst\", \"MOD_4\": \"execute\", \"MOD_5\": \"-1231321\", \"MOD_6\": \"XER1\", \"MOD_7\": \"None\", \"MOD_8\": \"source change\", \"MOD_9\": \"A1\", \"MOD_10\": \"temo1aaa\", \"MOD_11\": \"None\", \"MOD_12\": \"None\", \"TIMESTAMP\": \"test_time\" } }" | spath | transpose 0 | eval dict=if(column="_time" OR column="_raw",column,mvindex(split(column,"."),0,-2)) | dedup dict | eventstats values(dict) as dict | eval dict=mvfilter(dict!="_raw" AND dict!="_time") | eval "row 1"=if(column="_time" OR column="_raw",'row 1',dict) | eval column=if(column="_time" OR column="_raw",column,"dict") | dedup column | fields - dict | transpose 0 header_field=column | fields - column | mvexpand dict | eval _raw=replace(_raw,"\"".dict."\"","\"known\"") | spath path=known

Re: Not able to extract Nested JSON

ITWhisperer — Tue, 18 May 2021 11:52:15 GMT

Here's another approach which only uses mvexpand to create one extra row, the other rows are created with makecontinuous and populated with filldown.

If that still runs out of memory in mvexpand, there is another way to create the extra row.

Re: Not able to extract Nested JSON

surejsajeev — Wed, 19 May 2021 09:57:10 GMT

Hi @ITWhisperer,

I tried both the queries that you have mentioned and both of them showed the same errors as before. Is there any other available option?.

I can increase the limit in limits.conf file.

Currently, this is my limits.conf.

[thruput]
maxKBps = 0

[search]
enable_memory_tracker=true
search_process_memory_usage_threshold=4000

would this solve my issue?. The error message was "since its physical memory usage (30891.121000 MB) has exceeded the physical memory threshold specified in limits.conf/search_process_memory_usage_threshold (30000.000000 MB)."

I am very thankful to all the help you have been doing :).

Re: Not able to extract Nested JSON

ITWhisperer — Wed, 19 May 2021 10:09:07 GMT

To be honest, I don't know - someone else does that stuff for me 🙂 - having said that, a quick glance through the docs and I found this stanza which you might want to try too

[mvexpand]

* This stanza allows for fine tuning of mvexpand search command.

max_mem_usage_mb = <non-negative integer>
* Overrides the default value for "max_mem_usage_mb".
* Limits the amount of RAM, in megabytes (MB), a batch of events or results will
  use in the memory of a search process.
* See definition in the [default] stanza for "max_mem_usage_mb"
  for more details.
* Default: 500