topic Re: Help with parsing this json data in Splunk Search

How to parse this json data?

sdhiaeddine — Thu, 26 Jan 2023 19:08:59 GMT

Hi,

Please could you help with parsing this json data to table

{ "list_element": [ { "element": "{\"var1\":\"1.1.8.8:443\",\"var2\":\"1188\"}" }, { "element": "{\"var1\":\"8.8.1.1:443\",\"var2\":\"8811\"}" }, { "element": "{\"var1\":\"1.2.3.4:443\",\"var2\":\"1234\"}" } ] }

The result should look like:

var1	var2
1.1.8.8:443	1188
8.8.1.1:443	8811
1.2.3.4:443	1234

Re: Help with parsing this json data

ITWhisperer — Wed, 25 Jan 2023 17:27:24 GMT

| spath list_element{} output=list_element | mvexpand list_element | spath input=list_element | spath input=element | table var1 var2

Re: Help with parsing this json data

sdhiaeddine — Thu, 26 Jan 2023 08:42:36 GMT

Thank you @ITWhisperer for your help.

I get the correct number of rows (Events(3)) but with empty values.

Re: Help with parsing this json data

ITWhisperer — Thu, 26 Jan 2023 09:10:26 GMT

It sounds like your example isn't a close enough match to your actual events. Here is a example of it working correctly with your sample data.

| makeresults | eval _raw="{ \"list_element\": [ { \"element\": \"{\\\"var1\\\":\\\"1.1.8.8:443\\\",\\\"var2\\\":\\\"1188\\\"}\" }, { \"element\": \"{\\\"var1\\\":\\\"8.8.1.1:443\\\",\\\"var2\\\":\\\"8811\\\"}\" }, { \"element\": \"{\\\"var1\\\":\\\"1.2.3.4:443\\\",\\\"var2\\\":\\\"1234\\\"}\" } ] }" | spath list_element{} output=list_element | mvexpand list_element | spath input=list_element | spath input=element | table var1 var2

If you need any further help, please can you share your actual events, anonymised of course.

Re: Help with parsing this json data

sdhiaeddine — Thu, 26 Jan 2023 15:16:32 GMT

This is the actual event format

{event_disabled: false internal_event_id: 124578_1245 name: ELEMENT_EVENT element_list: {"list_element":[{"element": "{\"var1\":\"1.1.8.8:443\",\"var2\":\"1188\"}"},{"element": "{\"var1\":\"8.8.1.1:443\",\"var2\":\"8811\"}"},{"element": "{\"var1\":\"1.2.3.4:443\",\"var2\":\"1234\"}"}]} phone_number: 123456789 }

Re: Help with parsing this json data

yuanliu — Fri, 27 Jan 2023 07:40:43 GMT

If that is really the actual event, the developers deserve spanking. This is not conformant JSON but near garbage. First off, the nodes are not properly separated by comma. Secondly, keys and text values are not properly quoted all the way through.

Is it possible that the actual "actual" event format is like this instead?

{"event_disabled": false, "internal_event_id": "124578_1245", "name": "ELEMENT_EVENT", "element_list": {"list_element":[{"element": "{\"var1\":\"1.1.8.8:443\",\"var2\":\"1188\"}"},{"element": "{\"var1\":\"8.8.1.1:443\",\"var2\":\"8811\"}"},{"element": "{\"var1\":\"1.2.3.4:443\",\"var2\":\"1234\"}"}]}, "phone_number": 123456789 }

It is extremely important to present accurate data if you want to get concrete help (as opposed to seek general ideas).

Meanwhile, if the data is as I posted above, Splunk would have already given you a multivalue field named element_list.list_element{}.element. You'll need to run mvexpand on that as @ITWhisperer suggested, then spath again.

| mvexpand element_list.list_element{}.element | spath input=element_list.list_element{}.element | table var1 var2