How to extract JSON arrays in Splunk?

[sahilmits]

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"
}
}
}
]
}

[gcusello]

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

[sahilmits]

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 ?

[yuanliu]

Several pointers:

1. SPL command options must not have space after =.
2. The actual path you are trying to reach is requestParameters.policyDocument.
3. 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

[sahilmits]

@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

[yuanliu]

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" }

[sahilmits]

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

[yuanliu]

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.

1. Is it from a raw event?
2. 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.)