How to parse JSON Arrays together to create a table?

[tayvionp]

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

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

[ITWhisperer · Accepted solution]

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

[tayvionp]

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

[ITWhisperer]

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.

[gcusello]

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