Nested JSON (Returns Empty)

[morgantay96]

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.

[manjunathmeti]

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.