Getting Data In

Filtering based of Nested JSON objects

nicovibert
Engager

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.

Labels (2)
0 Karma
1 Solution

ITWhisperer
SplunkTrust
SplunkTrust

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

View solution in original post

nicovibert
Engager

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!

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

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

Gr0und_Z3r0
Contributor

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

 

Gr0und_Z3r0_0-1638068660227.png

 

And add the where clause as you like 

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

 

Please vote up if it helps!

Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...