- Mark as New
- Bookmark Message
- Subscribe to Message
- Mute Message
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
How to extract JSON arrays in Splunk?
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"
}
}
}
]
}
- Mark as New
- Bookmark Message
- Subscribe to Message
- Mute Message
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
- Mark as New
- Bookmark Message
- Subscribe to Message
- Mute Message
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.
- Mark as New
- Bookmark Message
- Subscribe to Message
- Mute Message
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
- Mark as New
- Bookmark Message
- Subscribe to Message
- Mute Message
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
@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
- Mark as New
- Bookmark Message
- Subscribe to Message
- Mute Message
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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" } |
- Mark as New
- Bookmark Message
- Subscribe to Message
- Mute Message
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
- Mark as New
- Bookmark Message
- Subscribe to Message
- Mute Message
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.)