Splunk Search

Nested JSON (Returns Empty)

morgantay96
Path Finder

Hi All,

Have a search that is not returning what I would like. Need to unest some JSON but having issues.

Here is an example of the JSON

 

 

{"configuration": {"targetResourceType": "AWS::EC2::Volume", "targetResourceId": "resource123", "configRuleList": [{"configRuleId": "config1", "configRuleArn": "removed", "configRuleName": "config1rule", "complianceType": "COMPLIANT"}, {"configRuleId": "config2", "configRuleArn": "removed", "configRuleName": "config2rule", "complianceType": "COMPLIANT"}, {"configRuleId": "config3", "configRuleArn": "removed", "configRuleName": "config3rule", "complianceType": "NON_COMPLIANT"}], "complianceType": "NON_COMPLIANT"}, "configurationItemStatus": "OK", "configurationStateId": 11111111, "configurationStateMd5Hash": "", "supplementaryConfiguration": {}, "resourceId": "AWS::EC2::Volume/resource123", "resourceType": "AWS::Config::ResourceCompliance", "relatedEvents": [], "tags": {}, "relationships": [{"resourceType": "AWS::EC2::Volume", "name": "Is associated with ", "resourceId": "resource123"}], "configurationItemVersion": "1.3", "configurationItemCaptureTime": "2021-01-23T06:28:07.415Z", "awsAccountId": "removed", "awsRegion": "removed"}

 

 

 

Here is the logic I am using

 

 

MY SEARCH
| spath configuration{} output=configuration
| stats count by resourceId configuration
| eval _raw=configuration
| spath configRuleList{} output=configRuleList
| stats count by resourceId configuration configRuleList
| eval _raw=configRuleList | spath complianceType output=complianceType | spath configRuleArn output=configRuleArn | spath configRuleId output=configRuleId | spath configRuleName output=configRuleName 
| table resourceId compianceType configRuleArn configRuleId configRuleName

 

 

 

 Desired result would be a table that accounts for the 3 different rules and created 3 different rows for each.

Labels (1)
0 Karma

manjunathmeti
Champion

hi @morgantay96,

You need to unnest configRuleList, the mvexpand the field and again apply spath on expanded values to get desired results. Try this.

| makeresults 
| eval _raw="{\"configuration\": {\"targetResourceType\": \"AWS::EC2::Volume\", \"targetResourceId\": \"resource123\", \"configRuleList\": 
    [ {\"configRuleId\": \"config1\", \"configRuleArn\": \"removed\", \"configRuleName\": \"config1rule\", \"complianceType\": \"COMPLIANT\"}, {\"configRuleId\": \"config2\", \"configRuleArn\": \"removed\", \"configRuleName\": \"config2rule\", \"complianceType\": \"COMPLIANT\"}, {\"configRuleId\": \"config3\", \"configRuleArn\": \"removed\", \"configRuleName\": \"config3rule\", \"complianceType\": \"NON_COMPLIANT\"}], \"complianceType\": \"NON_COMPLIANT\"}, \"configurationItemStatus\": \"OK\", \"configurationStateId\": 11111111, \"configurationStateMd5Hash\": \"\", \"supplementaryConfiguration\": {}, \"resourceId\": \"AWS::EC2::Volume/resource123\", \"resourceType\": \"AWS::Config::ResourceCompliance\", \"relatedEvents\": 
    [ ], \"tags\": {}, \"relationships\": 
    [ {\"resourceType\": \"AWS::EC2::Volume\", \"name\": \"Is associated with \", \"resourceId\": \"resource123\"}], \"configurationItemVersion\": \"1.3\", \"configurationItemCaptureTime\": \"2021-01-23T06:28:07.415Z\", \"awsAccountId\": \"removed\", \"awsRegion\": \"removed\"}" 
| spath configuration.configRuleList{} output=configRuleList 
| spath 
| mvexpand configRuleList 
| spath input=configRuleList 
| table resourceId complianceType configRuleArn configRuleId configRuleName

 

 If this reply helps you, a like would be appreciated.

0 Karma
Get Updates on the Splunk Community!

See just what you’ve been missing | Observability tracks at Splunk University

Looking to sharpen your observability skills so you can better understand how to collect and analyze data from ...

Weezer at .conf25? Say it ain’t so!

Hello Splunkers, The countdown to .conf25 is on-and we've just turned up the volume! We're thrilled to ...

How SC4S Makes Suricata Logs Ingestion Simple

Network security monitoring has become increasingly critical for organizations of all sizes. Splunk has ...