Skip to content

Instantly share code, notes, and snippets.

@JacobJohansen
Created July 11, 2022 18:20
Show Gist options
  • Save JacobJohansen/d6908ecdb4f3d6fd1ccaa6a0508bdf1b to your computer and use it in GitHub Desktop.
Save JacobJohansen/d6908ecdb4f3d6fd1ccaa6a0508bdf1b to your computer and use it in GitHub Desktop.
CloudQuery AWS Security Group Rules output
SELECT sg.group_name, sg.id as sg_id, permission_type, from_port, ip_protocol, to_port, cidr, null as rule_group_id, null as rule_group_name, tags from aws_ec2_security_groups as sg
LEFT JOIN aws_ec2_security_group_ip_permissions as sg_ip on sg.cq_id = sg_ip.security_group_cq_id
LEFT JOIN aws_ec2_security_group_ip_permission_ip_ranges as sg_ip_range on sg_ip.cq_id = sg_ip_range.security_group_ip_permission_cq_id
UNION
SELECT sg.group_name, sg.id as sg_id, permission_type, from_port, ip_protocol, to_port, null as cidr, rule_group_id, rule_group_name, tags from aws_ec2_security_groups as sg
LEFT JOIN aws_ec2_security_group_ip_permissions as sg_ip on sg.cq_id = sg_ip.security_group_cq_id
LEFT JOIN (
SELECT security_group_ip_permission_cq_id, group_id as rule_group_id, aws_ec2_security_groups.group_name as rule_group_name from aws_ec2_security_groups
INNER JOIN aws_ec2_security_group_ip_permission_user_id_group_pairs on aws_ec2_security_groups.id = aws_ec2_security_group_ip_permission_user_id_group_pairs.group_id
) as sg_sg_id on sg_ip.cq_id = sg_sg_id.security_group_ip_permission_cq_id
ORDER BY sg_id, rule_group_id
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment