Splunk Search

How to extract JSON arrays in Splunk?

sahilmits
Engager

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

Labels (1)
0 Karma

gcusello
SplunkTrust
SplunkTrust

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

0 Karma

sahilmits
Engager

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 ?
Tags (1)
0 Karma

yuanliu
SplunkTrust
SplunkTrust

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
0 Karma

sahilmits
Engager

@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

sahilmits_0-1675942728799.png

sahilmits_1-1675942780149.png

 

 

Tags (1)
0 Karma

yuanliu
SplunkTrust
SplunkTrust

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

_timeActionStringEquals
2023-02-10 03:47:37sts:AssumeRole{ "sts:ExternalId": "efep" }
0 Karma

sahilmits
Engager

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

 

sahilmits_0-1676366295841.png

 

0 Karma

yuanliu
SplunkTrust
SplunkTrust

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.)

0 Karma
Get Updates on the Splunk Community!

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...

Let’s Get You Certified – Vegas-Style at .conf24

Are you ready to level up your Splunk game? Then, let’s get you certified live at .conf24 – our annual user ...