Dashboards & Visualizations

How to parse JSON Arrays together to create a table?

tayvionp
Explorer

I've recently onboarded data from Gsuite to Splunk. I'm currently trying to create a few queries, but I'm having problem creating queries do to the JSON format.  I'm currently just trying to create a table with owner name, file name, time, etc.

I've tried using the spath command and json formatting, but I can't seem to get the data in a table. Here's an example query 

 

 

 

index="gsuite" sourcetype="gws:reports:drive" 
| spath events{}.parameters{}.value.doc_title

 

 

 

but the field isn't created. 

Here's the data in the events{}.parameters{}.value field

Screen Shot 2022-05-06 at 5.25.40 PM.png

 

Here's a sample of the data.

 

 

 

{
	"actor": {
		"profileId": "Sample Text"
	},
	"etag": "\"Sample Text\"",
	"events": [{
		"name": "sheets_import_range",
		"parameters": [{
			"boolValue": true,
			"name": "primary_event"
		}, {
			"name": "billable"
		}, {
			"name": "recipient_doc",
			"value": "123456789"
		}, {
			"name": "doc_id",
			"value": "123456789"
		}, {
			"name": "doc_type",
			"value": "spreadsheet"
		}, {
			"name": "is_encrypted"
		}, {
			"name": "doc_title",
			"value": "sampletext.xls"
		}, {
			"name": "visibility",
			"value": "shared_externally"
		}, {
			"name": "actor_is_collaborator_account"
		}, {
			"name": "owner",
			"value": "johndoe@gmail.com"
		}, {
			"name": "owner_is_shared_drive"
		}, {
			"name": "owner_is_team_drive"
		}],
		"type": "access"
	}],
	"id": {
		"applicationName": "drive",
		"customerId": "123456789",
		"time": "2022-05-06T20:55:00.285Z",
		"uniqueQualifier": "-123456789"
	},
	"kind": "admin#reports#activity"
}

 

 

 

I would like the data to look like this 

 

 

owner              doc_title        doc_type      visibility
johndoe@gmail.com. sampletext.xls  spreadsheet   shared_externally

 

 

 

Labels (3)
Tags (1)
0 Karma
1 Solution

ITWhisperer
SplunkTrust
SplunkTrust

Try something like this

| spath events{}.parameters{} output=parameters
| mvexpand parameters
| spath input=parameters
| eval {name}=value
| stats values(*) as * by _raw
| fields - parameters name value

View solution in original post

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

Try something like this

| spath events{}.parameters{} output=parameters
| mvexpand parameters
| spath input=parameters
| eval {name}=value
| stats values(*) as * by _raw
| fields - parameters name value
0 Karma

tayvionp
Explorer

Thanks so much. This worked. If you have time could you explain each line so I can understand what's going on? If not that's fine. I'm grateful you were able to help

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

The first spath extracts the parameters array / collection into a multi-value field called parameters

The mvexpand creates separate events for each of the values in the parameters field

The second spath extracts the fields from the parameters field

The eval uses the value in the name field to create a field with that name and the value from the value field

The stats collected the values of all the fields grouped by the original _raw field i.e. the original event

Finally, the fields command removes (-) the extra unwanted fields.

0 Karma

gcusello
Legend

Hi @tayvionp,

did you tried to use the "spath" command without options?

It should extract all fields that you need to use in the table command.

Otherwise you should use the spath command more times to extract one by one all the fields you need (https://docs.splunk.com/Documentation/Splunk/latest/SearchReference/Spath).

Ciao.

Giuseppe

0 Karma
Get Updates on the Splunk Community!

Improve Your Security Posture

Watch NowImprove Your Security PostureCustomers are at the center of everything we do at Splunk and security ...

Maximize the Value from Microsoft Defender with Splunk

 Watch NowJoin Splunk and Sens Consulting for this Security Edition Tech TalkWho should attend:  Security ...

This Week's Community Digest - Splunk Community Happenings [6.27.22]

Get the latest news and updates from the Splunk Community here! News From Splunk Answers ✍️ Splunk Answers is ...