How to parse this json data?

[sdhiaeddine]

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

[ITWhisperer]

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

[sdhiaeddine]

Thank you @ITWhisperer for your help.

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

[ITWhisperer]

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.

[sdhiaeddine]

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
}

[yuanliu]

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