topic Re: How to parse a JSON with a mutable node? in Splunk Search

How to parse a JSON with a mutable node?

JChris_ — Thu, 08 Jul 2021 13:23:05 GMT

I receive some logs in json format, but one of the nodes is mutable, sometimes it's an array, sometimes it is not. Take for example the two possible logs below:

Single record:

{ "root": { "metadata": { "name": "Jay Doe", "email": "jay.doe@example.com" }, "record": { "row": { "source_ip": "8.8.8.8", "count": "1" }, "identifiers": { "to": "companyfoo.com", "from": "example.com", "header_from": "example.com" } } } }

Multiple records:

{ "root": { "metadata": { "name": "Bob Doe", "email": "bob.doe@example.com" }, "record": [ { "row": { "source_ip": "8.8.8.8", "count": "1" }, "identifiers": { "to": "companyfoo.com", "from": "example.com", "header_from": "example.com" } }, { "row": { "source_ip": "8.8.4.4", "count": "5" }, "identifiers": { "to": "companybar.com", "from": "example.com", "header_from": "example.com" } } ] } }

The only part that is mutable is root.record. I want to be able to parse both formats and have a table like so:

name	email	source_ip	count	to	from	header_from
Jay Doe	jay.doe@exampel.com	8.8.8.8	1	companyfoo.com	example.com	example.com
Bob Doe	bob.doe@example.com	8.8.8.8	1	companyfoo.com	example.com	example.com
Bob Doe	bob.doe@example.com	8.8.4.4	5	companybar.com	example.com	example.com

Is it possible without using heavy and/or complex queries?

Re: How to parse a JSON with a mutable node?

venkatasri — Thu, 08 Jul 2021 13:55:17 GMT

Hi @JChris_

Can you try this,

<your_search_goes_here> | spath input=_raw path=root.record{} output=array_items | spath input=_raw path=root.metadata.name output=name | spath input=_raw path=root.metadata.email output=email | mvexpand array_items | spath input=array_items path=row output=row | spath input=array_items path=identifiers output=id | spath input=row | spath input=id | table name email count source_ip to from header_from

Output for the second sample provided,

---

An upvote would be appreciated and Accept solution if this reply helps!

Re: How to parse a JSON with a mutable node?

JChris_ — Thu, 08 Jul 2021 14:00:08 GMT

This only works for root.record when it is an array. The query should work for both.

Re: How to parse a JSON with a mutable node?

ITWhisperer — Thu, 08 Jul 2021 14:01:59 GMT

If record is the last field in root, you can edit it to convert the single instance to an array. If not, the sed will need to be adjusted accordingly

| makeresults | eval event=split("{ \"root\": { \"metadata\": { \"name\": \"Jay Doe\", \"email\": \"jay.doe@example.com\" }, \"record\": { \"row\": { \"source_ip\": \"8.8.8.8\", \"count\": \"1\" }, \"identifiers\": { \"to\": \"companyfoo.com\", \"from\": \"example.com\", \"header_from\": \"example.com\" } } } }|{ \"root\": { \"metadata\": { \"name\": \"Bob Doe\", \"email\": \"bob.doe@example.com\" }, \"record\": [ { \"row\": { \"source_ip\": \"8.8.8.8\", \"count\": \"1\" }, \"identifiers\": { \"to\": \"companyfoo.com\", \"from\": \"example.com\", \"header_from\": \"example.com\" } }, { \"row\": { \"source_ip\": \"8.8.4.4\", \"count\": \"5\" }, \"identifiers\": { \"to\": \"companybar.com\", \"from\": \"example.com\", \"header_from\": \"example.com\" } } ] } }","|") | mvexpand event | rex field=event mode=sed "s/(?s)(\"record\":\s)\{(.*)(\})(\s+\}$)/\1[{\2]\3\4/g" | spath input=event path=root.record{} output=record | spath input=event path=root.metadata output=metadata | spath input=metadata | mvexpand record | spath input=record path=row output=row | spath input=row | spath input=record path=identifiers output=identifiers | spath input=identifiers | table name email source_ip count to from header_from

The part before the blank lines just sets up some test data with your two sample events

Re: How to parse a JSON with a mutable node?

JChris_ — Thu, 08 Jul 2021 17:50:50 GMT

Thanks! This worked, but I end up changing the source so that ALL root.record are sent as list, even if there is only one record. This makes it easier and more consistent.

Re: How to parse a JSON with a mutable node?

ITWhisperer — Thu, 08 Jul 2021 17:59:04 GMT

Changing the source is the better approach. 😀