Splunk Search

How to parse this json data?

sdhiaeddine
Explorer

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
Labels (3)
Tags (2)
0 Karma

ITWhisperer
SplunkTrust
SplunkTrust
| spath list_element{} output=list_element
| mvexpand list_element
| spath input=list_element
| spath input=element
| table var1 var2
0 Karma

sdhiaeddine
Explorer

Thank you @ITWhisperer for your help.

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

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

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.

0 Karma

sdhiaeddine
Explorer

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
}

 

0 Karma

yuanliu
SplunkTrust
SplunkTrust

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

 

Tags (1)
0 Karma
Get Updates on the Splunk Community!

Investigate Security and Threat Detection with VirusTotal and Splunk Integration

As security threats and their complexities surge, security analysts deal with increased challenges and ...

Observability Highlights | January 2023 Newsletter

 January 2023New Product Releases Splunk Network Explorer for Infrastructure MonitoringSplunk unveils Network ...

Security Highlights | January 2023 Newsletter

January 2023 Splunk Security Essentials (SSE) 3.7.0 ReleaseThe free Splunk Security Essentials (SSE) 3.7.0 app ...