topic Re: Splunk Query to form a table with multiple JSON fields in Splunk Search

Splunk Query to form a table with multiple JSON fields

shashaikhhh — Thu, 16 Jun 2022 13:58:36 GMT

Below is my splunk raw event data

{
"additional": {
"method": "POST",
"url": "/api/resource/getContentEditorData",
"headers": {
"cloudfront-viewer-country": "US",
"origin": "https://www.site1.com",
"sec-ch-ua-platform": "\"Android\"",
}
},
"level": "notice",
"message": "INCOMING REQUEST: POST /api/resource/getContentEditorData"
}

I need count of cloudfront-viewer-country and sec-ch-ua-platform for each Origin

Please help.

Expected Result:

Origin	Platform	Platform Count	Country	Country Count
https://www.site1.com	Android	10	US	22
	macOS	12	UK	3
	Windows	6	AU	1
https://www.site2.com	Android	4	US	8
	macOS	4	UK	1
	Windows	2	AU	1

Re: Splunk Query to form a table with multiple JSON fields

ITWhisperer — Thu, 16 Jun 2022 15:38:13 GMT

Please clarify your requirement.

What if site1 had only 2 countries, or site2 had an extra platform, what would your expected / desired result look like then?

Btw, your JSON example isn't valid JSON (there is a spurious comma after Android.

Re: Splunk Query to form a table with multiple JSON fields

shashaikhhh — Thu, 16 Jun 2022 15:50:27 GMT

If site1 has only 2 countries, then we need to display 2 records.

Updated Splunk event data:
{
"additional": {
"method": "POST",
"url": "/api/resource/getContentEditorData",
"headers": {
"cloudfront-viewer-country": "US",
"origin": "https://www.site1.com",
"sec-ch-ua-platform": "\"Android\""
}
},
"level": "notice",
"message": "INCOMING REQUEST: POST /api/resource/getContentEditorData"
}

============

{
"additional": {
"method": "POST",
"url": "/api/resource/getContentEditorData",
"headers": {
"cloudfront-viewer-country": "UK",
"origin": "https://www.site1.com",
"sec-ch-ua-platform": "\"Windows\""
}
},
"level": "notice",
"message": "INCOMING REQUEST: POST /api/resource/getContentEditorData"
}

=========================

{
"additional": {
"method": "POST",
"url": "/api/resource/getContentEditorData",
"headers": {
"cloudfront-viewer-country": "AU",
"origin": "https://www.site2.com",
"sec-ch-ua-platform": "\"Windows\""
}
},
"level": "notice",
"message": "INCOMING REQUEST: POST /api/resource/getContentEditorData"
}

Re: Splunk Query to form a table with multiple JSON fields

shashaikhhh — Thu, 16 Jun 2022 15:54:29 GMT

If site1 has only 2 countries and site2 has one extra platform, then the expected result should be like below.

Origin	Platform	Platform Count	Country	Country Count
https://www.site1.com	Android	10	US	22
	macOS	12	UK	3
	Windows	6
https://www.site2.com	Android	4	US	8
	macOS	4	UK	1
	Windows	2	AU	1
			IND	5