Not able to read nested json array in SPLUNK

Question

I am using "spath" to read json structure from a log file.

{"failure_reason":null,"gen_flag":"GENERATED","gen_date":"2020-02-15","siteid":"ABC","_action":"Change","order":"123"}

I am able to parse above json.

However, "spath" function is not able to read nested array inside that json:

{"failure_reason":"[{"module":"Status Report","reason":"Status Report is not available","statusCode":"503"}]","gen_flag":"GENERATED_PARTIAL","gen_date":"2020-02-15","siteid":"ABC","_action":"Change","wonum":"321"}.

please help!

Answer 1

Your event is not valid JSON. A JSON array should not be surrounded by "s.

Copy your event into any of the following JSON validators, and confirm that it is incorrect.

• https://jsonformatter.curiousconcept.com/
• https://jsonlint.com/
• https://jsonformatter.org/

Now, try with the corrected event.

{"failure_reason":[{"module":"Status Report","reason":"Status Report is not available","statusCode":"503"}],"gen_flag":"GENERATED_PARTIAL","gen_date":"2020-02-15","siteid":"ABC","_action":"Change","wonum":"321"}

You can see that spath works correctly with the modified JSON with the following search.

| makeresults 
| eval raw="{\"failure_reason\":[{\"module\":\"Status Report\",\"reason\":\"Status Report is not available\",\"statusCode\":\"503\"}],\"gen_flag\":\"GENERATED_PARTIAL\",\"gen_date\":\"2020-02-15\",\"siteid\":\"ABC\",\"_action\":\"Change\",\"wonum\":\"321\"}" 
| spath input=raw

If you need a way to pre-process your event to remove the "s from the array, you may be able to try the following, which may remove the extra "s. This is really dependent on the structure of the event, and may not be 100%, but should be enough to get you started. Try to fix the format of the event at the source.

| makeresults | eval raw="{\"failure_reason\":\"[{\"module\":\"Status Report\",\"reason\":\"Status Report is not available\",\"statusCode\":\"503\"}]\",\"gen_flag\":\"GENERATED_PARTIAL\",\"gen_date\":\"2020-02-15\",\"siteid\":\"ABC\",\"_action\":\"Change\",\"wonum\":\"321\"}" 
| rex mode=sed field=raw "s/\"\[/[/" | rex mode=sed field=raw "s/\]\"/]/"
| spath input=raw