RQL query for evaluating AWS security groups rule by rule

[AHershey]

I am sure someone has had this issue.  

We want to get an alert if we allow access to all IP's on all ports.  The policy is based on this example:

filter '$.X.state.name equals running and $.X.publicIpAddress exists and $.X.securityGroups[*].groupId contains $.Y.groupId and (($.Y.ipPermissions[*].ipRanges[*] contains 0.0.0.0/0 or $.Y.ipPermissions[*].ipv6Ranges[*].cidrIpv6 contains ::/0) and $.Y.ipPermissions[*].ipProtocol equals -1)

The problem we have is with false positives.  We have some EC2's with security groups that allow all external IP's to reach 443, and some rules allow only some other security groups to access all ports in another security group.  So what we are looking for is a way for the blue and red criteria to be used to evaluate each security group rule individually, as opposed to firing if any ingress rule has "0.0.0.0" on the allowed IP's and another ingress rule has "-1"  on the allowed protocols side.

We are having the discussions to dissuade people from using "-1" ever, which is really the best way to go, but for now we are just trying to focus on stopping the worst of behavior.

[AHershey · Accepted solution]

Solve my issue, here is the query that did it with a little extra:

config where api.name = 'aws-ec2-describe-instances' as X; config where api.name = 'aws-ec2-describe-security-groups' as Y; filter '$.X.state.name equals running and $.X.publicIpAddress exists and $.X.securityGroups[*].groupId contains $.Y.groupId and (($.Y.ipPermissions[?(@.ipProtocol==-1)].ipRanges[*] contains 0.0.0.0/0 or $.Y.ipPermissions[?(@.ipProtocol==-1)].ipv6Ranges[*].cidrIpv6 contains ::/0))' ; show X; addcolumn publicDnsName publicIpAddress