Query: vpc_security_group_egress_rule_sankey

with associations as (

  -- attached ec2 instances
  select
      title,
      arn,
      'aws_ec2_instance' as category,
      sg ->> 'GroupId' as group_id
    from
      aws_ec2_instance,
      jsonb_array_elements(security_groups) as sg
  where
    sg ->> 'GroupId' = $1


  -- attached lambda functions
  union all select
      title,
      arn,
      'aws_lambda_function' as category,
      sg
    from
      aws_lambda_function,
      jsonb_array_elements_text(vpc_security_group_ids) as sg
    where
      sg = $1

  -- attached Classic ELBs
  union all select
      title,
      arn,
      'aws_ec2_classic_load_balancer' as category,
      sg
    from
      aws_ec2_classic_load_balancer,
      jsonb_array_elements_text(security_groups) as sg
    where
      sg = $1

  -- attached ALBs
  union all select
      title,
      arn,
      'aws_ec2_application_load_balancer' as category,
      sg
    from
      aws_ec2_application_load_balancer,
      jsonb_array_elements_text(security_groups) as sg
    where
      sg = $1

  -- attached NLBs
  union all select
      title,
      arn,
      'aws_ec2_network_load_balancer' as category,
      sg
    from
      aws_ec2_network_load_balancer,
      jsonb_array_elements_text(security_groups) as sg
    where
      sg = $1


  -- attached GWLBs
  union all select
      title,
      arn,
      'aws_ec2_gateway_load_balancer' as category,
      sg
    from
      aws_ec2_gateway_load_balancer,
      jsonb_array_elements_text(security_groups) as sg
    where
      sg = $1


  -- attached aws_ec2_launch_configuration
  union all select
      title,
      launch_configuration_arn,
      'aws_ec2_launch_configuration' as category,
      sg
    from
      aws_ec2_launch_configuration,
      jsonb_array_elements_text(security_groups) as sg
    where
      sg = $1



  -- attached DAX Cluster
  union all select
      title,
      arn,
      'aws_dax_cluster' as category,
      sg
    from
      aws_dax_cluster,
      jsonb_array_elements_text(security_groups) as sg
    where
      sg = $1

  -- attached aws_dms_replication_instance
  union all select
      title,
      arn,
      'aws_dms_replication_instance' as category,
      sg
    from
      aws_dms_replication_instance,
      jsonb_array_elements_text(vpc_security_groups) as sg
    where
      sg = $1

  -- attached aws_efs_mount_target
  union all select
      title,
      mount_target_id,
      'aws_efs_mount_target' as category,
      sg
    from
      aws_efs_mount_target,
      jsonb_array_elements_text(security_groups) as sg
    where
      sg = $1

  -- attached aws_elasticache_cluster
  union all select
      title,
      arn,
      'aws_elasticache_cluster' as category,
      sg
    from
      aws_elasticache_cluster,
      jsonb_array_elements_text(security_groups) as sg
    where
      sg = $1


  -- attached aws_rds_db_cluster
  union all select
      title,
      arn,
      'aws_rds_db_cluster' as category,
      sg
    from
      aws_rds_db_cluster,
      jsonb_array_elements_text(vpc_security_groups) as sg
    where
      sg = $1

  -- attached aws_rds_db_instance
  union all select
      title,
      arn,
      'aws_rds_db_instance' as category,
      sg
    from
      aws_rds_db_instance,
      jsonb_array_elements_text(vpc_security_groups) as sg
    where
      sg = $1


  -- attached aws_redshift_cluster
  union all select
      title,
      arn,
      'aws_redshift_cluster' as category,
      sg
    from
      aws_redshift_cluster,
      jsonb_array_elements_text(vpc_security_groups) as sg
    where
      sg = $1


  -- attached aws_sagemaker_notebook_instance
  union all select
      title,
      arn,
      'aws_sagemaker_notebook_instance' as category,
      sg
    from
      aws_sagemaker_notebook_instance,
      jsonb_array_elements_text(security_groups) as sg
    where
      sg = $1

  ),
  rules as (
    select
      concat(text(cidr_ipv4), text(cidr_ipv6), referenced_group_id, referenced_vpc_id,prefix_list_id) as source,
      security_group_rule_id,
      case
        when ip_protocol = '-1' then 'All Traffic'
        when ip_protocol = 'icmp' then 'All ICMP'
        when from_port is not null
        and to_port is not null
        and from_port = to_port then concat(from_port, '/', ip_protocol)
        else concat(
          from_port,
          '-',
          to_port,
          '/',
          ip_protocol
        )
      end as port_proto,
      type,
      case
        when ( cidr_ipv4 = '0.0.0.0/0' or cidr_ipv6 = '::/0')
            and ip_protocol <> 'icmp'
            and (
              from_port = -1
              or (from_port = 0 and to_port = 65535)
            ) then 'alert'
        else 'ok'
      end as category,
      group_id
    from
      aws_vpc_security_group_rule
    where
      group_id = $1
      and type = 'egress'
      )

  -- Nodes  ---------

  select
    distinct concat('src_',source) as id,
    source as title,
    3 as depth,
    'source' as category,
    null as from_id,
    null as to_id
  from
    rules

  union
  select
    distinct port_proto as id,
    port_proto as title,
    2 as depth,
    'port_proto' as category,
    null as from_id,
    null as to_id
  from
    rules

  union
  select
    distinct sg.group_id as id,
    sg.group_name as title,
    1 as depth,
    'security_group' as category,
    null as from_id,
    null as to_id
  from
    aws_vpc_security_group sg
    inner join rules sgr on sg.group_id = sgr.group_id

  union
  select
      distinct arn as id,
      title || '(' || category || ')' as title, -- TODO: Should this be arn instead?
      0 as depth,
      category,
      group_id as from_id,
      null as to_id
    from
      associations

  -- Edges  ---------
  union select
    null as id,
    null as title,
    null as depth,
    category,
    concat('src_',source) as from_id,
    port_proto as to_id
  from
    rules

  union select
    null as id,
    null as title,
    null as depth,
    category,
    port_proto as from_id,
    group_id as to_id
  from
    rules