topic Re: How to extract JSON arrays in Splunk in Splunk Search

How to extract JSON arrays in Splunk?

sahilmits — Wed, 08 Feb 2023 18:34:39 GMT

Here is the query i have and need to extract the "sts:ExternalId"

requestParameters: { [-]
policyDocument: {
"Version": "2012-10-17",
"Statement": [
{
"Sid": "AllowAssumeRoleForAnotherAccount",
"Effect": "Allow",
"Principal": {
"AWS": "arn:aws:iam::384280045676:role/jenkins-node-custom-efep"
},
"Action": "sts:AssumeRole",
"Condition": {
"StringEquals": {
"sts:ExternalId": "efep"
}
}
}
]
}

Re: How to extract JSON arrays in Splunk

gcusello — Wed, 08 Feb 2023 14:45:48 GMT

Hi @sahilmits,

you can use the spath command (https://docs.splunk.com/Documentation/SplunkCloud/latest/SearchReference/Spath) to extract all fields from a json format event.

Ciao.

Giuseppe

Re: How to extract JSON arrays in Splunk

sahilmits — Wed, 08 Feb 2023 14:54:14 GMT

I have tried to extract but getting error

index=X "sts:ExternalId" | spath path= policyDocument output=policyDocument | fields - _raw | fields Version, Statement x | mvexpand x | spath input=x | rename Condition{} as Condition | mvexpand Condition | stats count as Count by Condition, Statement.

Error in 'spath' command: You have not specified a path. Try using "path=mypath" as an argument to spath.

Can you suggest the SPL ?

Re: How to extract JSON arrays in Splunk

yuanliu — Wed, 08 Feb 2023 18:26:12 GMT

Several pointers:

SPL command options must not have space after =.
The actual path you are trying to reach is requestParameters.policyDocument.
If you use path to reach requestParameters.policyDocument, that node will be extracted as raw JSON, therefore you need to perform spath again.

Hence,

index=X "sts:ExternalId" | spath path=requestParameters.policyDocument output=policyDocument | spath input=policyDocument | fields - _raw | fields Version, Statement | mvexpand Statement | spath input=Statement | rename Condition{} as Condition | mvexpand Condition | stats count as Count by Condition, Statement

If you are only interested in requestParameters.policyDocument, you can bypass the first spath altogether,

index=X "sts:ExternalId" | spath input=requestParameters.policyDocument | fields - _raw | fields Version, Statement | mvexpand Statement | spath input=Statement | rename Condition{} as Condition | mvexpand Condition | stats count as Count by Condition, Statement

Re: How to extract JSON arrays in Splunk

sahilmits — Thu, 09 Feb 2023 11:40:10 GMT

@yuanliu I have tried the Suggested SPL but it is not extracting the results

index=x "sts:ExternalId"
| spath input="requestParameters.policyDocument" path=Statement{}
| mvexpand Statement{}
| spath input="Statement{}"
| spath input="userIdentity"
| eval Permissions = mvappend('Action{}', Action), Resources = mvappend('Resource{}' ,Resource)
| table _time, StringEquals

Re: How to extract JSON arrays in Splunk

yuanliu — Fri, 10 Feb 2023 11:52:30 GMT

Not sure what you are trying to do with userIdentity or Resource because your illustrated data don't contain any of these. And the table doesn't include them, anyway. So, I'll omit all that and just list Action and StringEquals nodes. Note: Your illustrated data indicates that Action will not be multivalue after mvexpand. So, mvappend is not useful.

| spath path=requestParameters.policyDocument.Statement{} OUTPUT=Statement | mvexpand Statement | spath input=Statement | spath input=Statement path=Condition.StringEquals output=StringEquals | table _time Action StringEquals

Using your illustrated data (correcting for JSON syntax), I get

_time	Action	StringEquals
2023-02-10 03:47:37	sts:AssumeRole	{ "sts:ExternalId": "efep" }

Re: How to extract JSON arrays in Splunk

sahilmits — Tue, 14 Feb 2023 09:18:29 GMT

@yuanliu Thanks for your response , Not sure why it is populate the results.

Re: How to extract JSON arrays in Splunk

yuanliu — Wed, 15 Feb 2023 05:21:06 GMT

It is best to go back to basics. Do you get requestParameters.policyDocument.Statement{} before doing any spath? In fact, there are some loose ends in that data you illustrated.

Is it from a raw event?
The illustration is not properly quoted for conformance. Can you illustrate an event in raw text? A conformant JSON would look like

{ "requestParameters": { "foo": "bar", "policyDocument": { "Version": "2012-10-17", "Statement": [ { "Sid": "AllowAssumeRoleForAnotherAccount", "Effect": "Allow", "Principal": { "AWS": "arn:aws:iam::384280045676:role/jenkins-node-custom-efep" }, "Action": "sts:AssumeRole", "Condition": { "StringEquals": { "sts:ExternalId": "efep" } } } ] } } }

(The above is what I used for emulation.)