topic Re: Filtering based of Nested JSON objects in Getting Data In

Filtering based of Nested JSON objects

nicovibert — Sat, 27 Nov 2021 10:24:36 GMT

Hi,

First question here - apologies if it's obvious or basic!

I am trying to parse a nested list and find specific policies that match a couple of criteria. But I can't seem to get the logic right.

If you look at the JSON below, there is a nested list of "policies". I just want to find the policies with a result of "false" and with a filename starting with "./hard" and I want to print the "print" messages in a table.

The JSON output is here:

{ "resource": { "action": "hard_failed", "meta": { "result": false, "passed": 1, "total_failed": 2, "hard_failed": 1, "soft_failed": 0, "advisory_failed": 1, "duration_ms": 0, "sentinel": { "schema_version": 1, "data": { "sentinel-policy-networking": { "can_override": false, "error": null, "policies": [ { "allowed_failure": true, "error": null, "policy": "sentinel-policy-networking/advisory-mandatory-tags", "result": false, "trace": { "description": "This policy uses the Sentinel tfplan/v2 import to require that\nspecified AWS resources have all mandatory tags", "error": null, "print": "aws_customer_gateway.customer_gateway has tags that is missing, null, or is not a map or a list. It should have had these items: [Name]\naws_vpn_connection.main has tags that is missing, null, or is not a map or a list. It should have had these items: [Name]\n", "result": false, "rules": { "main": { "desc": "Main rule", "ident": "main", "position": { "filename": "./advisory-mandatory-tags.sentinel", "offset": 1244, "line": 38, "column": 1 }, "value": false } } } }, { "allowed_failure": false, "error": null, "policy": "sentinel-policy-networking/soft-mandatory-vpn", "result": false, "trace": { "description": "This policy uses the Sentinel tfplan/v2 import to require that\nAWS VPNs only used allowed DH groups", "error": null, "print": "aws_vpn_connection.main has tunnel1_phase1_dh_group_numbers [2] with items [2] that are not in the allowed list: [19, 20, 21]\n", "result": false, "rules": { "main": { "desc": "Main rule", "ident": "main", "position": { "filename": "./soft-mandatory-vpn.sentinel", "offset": 740, "line": 23, "column": 1 }, "value": false } } } }, { "allowed_failure": false, "error": null, "policy": "sentinel-policy-networking/hard-mandatory-policy", "result": true, "trace": { "description": "This policy uses the Sentinel tfplan/v2 import to validate that no security group\nrules have the CIDR \"0.0.0.0/0\" for ingress rules. It covers both the\naws_security_group and the aws_security_group_rule resources which can both\ndefine rules.", "error": null, "print": "", "result": true, "rules": { "main": { "desc": "", "ident": "main", "position": { "filename": "./hard-mandatory-policy.sentinel", "offset": 2136, "line": 58, "column": 1 }, "value": true } } } } ], "result": false } } }, "comment": null, "run": { "id": "run-5bY1pzrxAHWMH8Qx", "message": "Update main.tf" }, "workspace": { "id": "ws-LvRrPmVrm4MSnDC9", "name": "aws-networking-sentinel-policed" } }, "type": "policy_check", "id": "polchk-i8KAHhKX7Dqb7T3A" }, "request": { "id": null }, "auth": { "impersonator_id": null, "type": "Client", "accessor_id": "user-pF6Tu2NVN7hgNa7E", "description": "gh-webhooks-nicovibert-org-yuYK0J4bQO", "organization_id": "org-b5PqUHqMpyQ2M86A" }, "timestamp": "2021-11-26T22:08:52.000Z", "version": "0", "type": "Resource", "id": "9890fc46-f913-48d9-b2f7-64f8fc1c4d0e" }

This search below isn't quite working for me. There must be an easier way to do - perhaps with spath ? - but I can't get it to work.

sourcetype="terraform_cloud" AND resource.meta.sentinel.data.sentinel-policy-networking.policies{}.trace.rules.main.position.filename = "./hard*" AND resource.meta.sentinel.data.sentinel-policy-networking.policies{}.trace.rules.main.value="false" | table auth.description, resource.meta.hard_failed, resource.meta.sentinel.data.sentinel*.policies*.trace.print

Thanks in advance for any pointers, examples or hints.

Re: Filtering based of Nested JSON objects

Gr0und_Z3r0 — Sun, 28 Nov 2021 03:07:03 GMT

hi mate, you can try something like this below....
as there are multiple nested objects and fields, you'll have to segregate them and then extract required fields in steps.

| makeresults | eval jsonData ="{\"resource\":{\"action\":\"hard_failed\",\"meta\":{\"result\":false,\"passed\":1,\"total_failed\":2,\"hard_failed\":1,\"soft_failed\":0,\"advisory_failed\":1,\"duration_ms\":0,\"sentinel\":{\"schema_version\":1,\"data\":{\"sentinel-policy-networking\":{\"can_override\":false,\"error\":null,\"policies\":[{\"allowed_failure\":true,\"error\":null,\"policy\":\"sentinel-policy-networking/advisory-mandatory-tags\",\"result\":false,\"trace\":{\"description\":\"This policy uses the Sentinel tfplan/v2importtorequirethat\\nspecifiedAWSresourceshaveallmandatorytags\",\"error\":null,\"print\":\"aws_customer_gateway.customer_gatewayhastagsthatismissing,null,orisnotamaporalist.Itshouldhavehadtheseitems:[Name]\\naws_vpn_connection.mainhastagsthatismissing,null,orisnotamaporalist.Itshouldhavehadtheseitems:[Name]\\n\",\"result\":false,\"rules\":{\"main\":{\"desc\":\"Mainrule\",\"ident\":\"main\",\"position\":{\"filename\":\"./advisory-mandatory-tags.sentinel\",\"offset\":1244,\"line\":38,\"column\":1},\"value\":false}}}},{\"allowed_failure\":false,\"error\":null,\"policy\":\"sentinel-policy-networking/soft-mandatory-vpn\",\"result\":false,\"trace\":{\"description\":\"ThispolicyusestheSentineltfplan/v2importtorequirethat\\nAWSVPNsonlyusedallowedDHgroups\",\"error\":null,\"print\":\"aws_vpn_connection.mainhastunnel1_phase1_dh_group_numbers[2]withitems[2]thatarenotintheallowedlist:[19,20,21]\\n\",\"result\":false,\"rules\":{\"main\":{\"desc\":\"Mainrule\",\"ident\":\"main\",\"position\":{\"filename\":\"./soft-mandatory-vpn.sentinel\",\"offset\":740,\"line\":23,\"column\":1},\"value\":false}}}},{\"allowed_failure\":false,\"error\":null,\"policy\":\"sentinel-policy-networking/hard-mandatory-policy\",\"result\":true,\"trace\":{\"description\":\"ThispolicyusestheSentineltfplan/v2importtovalidatethatnosecuritygroup\\nruleshavetheCIDR\\\"0.0.0.0/0\\\"foringressrules.Itcoversboththe\\naws_security_groupandtheaws_security_group_ruleresourceswhichcanboth\\ndefinerules.\",\"error\":null,\"print\":\"\",\"result\":true,\"rules\":{\"main\":{\"desc\":\"\",\"ident\":\"main\",\"position\":{\"filename\":\"./hard-mandatory-policy.sentinel\",\"offset\":2136,\"line\":58,\"column\":1},\"value\":true}}}}],\"result\":false}}},\"comment\":null,\"run\":{\"id\":\"run-5bY1pzrxAHWMH8Qx\",\"message\":\"Updatemain.tf\"},\"workspace\":{\"id\":\"ws-LvRrPmVrm4MSnDC9\",\"name\":\"aws-networking-sentinel-policed\"}},\"type\":\"policy_check\",\"id\":\"polchk-i8KAHhKX7Dqb7T3A\"},\"request\":{\"id\":null},\"auth\":{\"impersonator_id\":null,\"type\":\"Client\",\"accessor_id\":\"user-pF6Tu2NVN7hgNa7E\",\"description\":\"gh-webhooks-nicovibert-org-yuYK0J4bQO\",\"organization_id\":\"org-b5PqUHqMpyQ2M86A\"},\"timestamp\":\"2021-11-26T22:08:52.000Z\",\"version\":\"0\",\"type\":\"Resource\",\"id\":\"9890fc46-f913-48d9-b2f7-64f8fc1c4d0e\"}" | spath input=jsonData path=resource.meta.sentinel.data. output=data | spath input=data path=sentinel-policy-networking.policies{} output=policies | stats count as count by policies | spath input=policies path=result output=result | spath input=policies path=trace{}.rules.main.position.filename output=filename | spath input=policies path=trace{}.print output=print | fields - jsonData data policies count

And add the where clause as you like

| where like(filename,"%hard%")

Please vote up if it helps!

Re: Filtering based of Nested JSON objects

ITWhisperer — Sun, 28 Nov 2021 07:58:58 GMT

I assume by not working you mean the example event is not getting filtered out when you had expected it to? If this is not the case, please can you explain what is not working for you?

If it is the case, then you need to separate out the policies collection into separate events. One way to do this whilst retaining the original event for further extractions, is to use spath and mvexpand.

| makeresults | eval _raw="{ \"resource\": { \"action\": \"hard_failed\", \"meta\": { \"result\": false, \"passed\": 1, \"total_failed\": 2, \"hard_failed\": 1, \"soft_failed\": 0, \"advisory_failed\": 1, \"duration_ms\": 0, \"sentinel\": { \"schema_version\": 1, \"data\": { \"sentinel-policy-networking\": { \"can_override\": false, \"error\": null, \"policies\": [ { \"allowed_failure\": true, \"error\": null, \"policy\": \"sentinel-policy-networking/advisory-mandatory-tags\", \"result\": false, \"trace\": { \"description\": \"This policy uses the Sentinel tfplan/v2 import to require that\nspecified AWS resources have all mandatory tags\", \"error\": null, \"print\": \"aws_customer_gateway.customer_gateway has tags that is missing, null, or is not a map or a list. It should have had these items: [Name]\naws_vpn_connection.main has tags that is missing, null, or is not a map or a list. It should have had these items: [Name]\\n\", \"result\": false, \"rules\": { \"main\": { \"desc\": \"Main rule\", \"ident\": \"main\", \"position\": { \"filename\": \"./advisory-mandatory-tags.sentinel\", \"offset\": 1244, \"line\": 38, \"column\": 1 }, \"value\": false } } } }, { \"allowed_failure\": false, \"error\": null, \"policy\": \"sentinel-policy-networking/soft-mandatory-vpn\", \"result\": false, \"trace\": { \"description\": \"This policy uses the Sentinel tfplan/v2 import to require that\nAWS VPNs only used allowed DH groups\", \"error\": null, \"print\": \"aws_vpn_connection.main has tunnel1_phase1_dh_group_numbers [2] with items [2] that are not in the allowed list: [19, 20, 21]\\n\", \"result\": false, \"rules\": { \"main\": { \"desc\": \"Main rule\", \"ident\": \"main\", \"position\": { \"filename\": \"./soft-mandatory-vpn.sentinel\", \"offset\": 740, \"line\": 23, \"column\": 1 }, \"value\": false } } } }, { \"allowed_failure\": false, \"error\": null, \"policy\": \"sentinel-policy-networking/hard-mandatory-policy\", \"result\": true, \"trace\": { \"description\": \"This policy uses the Sentinel tfplan/v2 import to validate that no security group\nrules have the CIDR \\\"0.0.0.0/0\\\" for ingress rules. It covers both the\naws_security_group and the aws_security_group_rule resources which can both\\ndefine rules.\", \"error\": null, \"print\": \"\", \"result\": true, \"rules\": { \"main\": { \"desc\": \"\", \"ident\": \"main\", \"position\": { \"filename\": \"./hard-mandatory-policy.sentinel\", \"offset\": 2136, \"line\": 58, \"column\": 1 }, \"value\": true } } } } ], \"result\": false } } }, \"comment\": null, \"run\": { \"id\": \"run-5bY1pzrxAHWMH8Qx\", \"message\": \"Update main.tf\" }, \"workspace\": { \"id\": \"ws-LvRrPmVrm4MSnDC9\", \"name\": \"aws-networking-sentinel-policed\" } }, \"type\": \"policy_check\", \"id\": \"polchk-i8KAHhKX7Dqb7T3A\" }, \"request\": { \"id\": null }, \"auth\": { \"impersonator_id\": null, \"type\": \"Client\", \"accessor_id\": \"user-pF6Tu2NVN7hgNa7E\", \"description\": \"gh-webhooks-nicovibert-org-yuYK0J4bQO\", \"organization_id\": \"org-b5PqUHqMpyQ2M86A\" }, \"timestamp\": \"2021-11-26T22:08:52.000Z\", \"version\": \"0\", \"type\": \"Resource\", \"id\": \"9890fc46-f913-48d9-b2f7-64f8fc1c4d0e\" }" | spath resource.meta.sentinel.data.sentinel-policy-networking.policies{} output=policies | mvexpand policies | spath input=policies trace.rules.main.position.filename output=filename | spath input=policies trace.rules.main.value output=value | where value="false" AND match(filename,"^\..hard")

Re: Filtering based of Nested JSON objects

nicovibert — Sun, 28 Nov 2021 22:23:30 GMT

Thanks folks for supporting. Really impressed by how quickly people came to the rescue. I hadn't understood how mvexpand could be used - this makes sense now.

Thanks again!