Query: vpc_security_group_assoc

-- EC2 instances
select
  title as "Title",
  'aws_ec2_instance' as "Type",
  arn as "ARN",
  '/aws_insights.dashboard.ec2_instance_detail?input.instance_arn=' || arn as link
from
  aws_ec2_instance,
  jsonb_array_elements(security_groups) as sg
where
  sg ->> 'GroupId' = $1

-- Lambda functions
union all select
  title as "Title",
  'aws_lambda_function' as "Type",
  arn as "ARN",
  '/aws_insights.dashboard.lambda_function_detail?input.lambda_arn=' || arn as link
from
  aws_lambda_function,
  jsonb_array_elements_text(vpc_security_group_ids) as sg
where
  sg = $1

-- ECS services
union all select
  title as "Title",
  'aws_ecs_service' as "Type",
  arn as "ARN",
  '/aws_insights.dashboard.ecs_service_detail?input.service_arn=' || arn as link
from
  aws_ecs_service,
  jsonb_array_elements_text(network_configuration['AwsvpcConfiguration']['SecurityGroups']) as sg
where
  sg = $1

-- ECS tasks
union all select
  tasks."group" as "Title",
  'aws_ecs_task' as "Type",
  tasks.task_definition_arn as "ARN",
  '/aws_insights.dashboard.ecs_task_definition_detail?input.task_definition_arn=' || tasks.task_definition_arn as link
from
  (
    select
      network_interface_id,
      jsonb_array_elements(groups)->>'GroupId' as security_group
    from
      aws_ec2_network_interface
  ) as interfaces
join
  (
    select
      task_definition_arn,
      "group",
      details->>'Value' as network_interface_id
    from
      aws_ecs_task,
      jsonb_array_elements(attachments->0->'Details') as details
    where
      details->>'Name' = 'networkInterfaceId'
  ) as tasks
  on
    interfaces.network_interface_id = tasks.network_interface_id
where
  security_group = $1
group by "ARN", "Title"

-- Amazon MQ brokers
union all select
  title as "Title",
  'aws_mq_broker' as "Type",
  arn as "ARN",
  NULL as link
from
  aws_mq_broker,
  jsonb_array_elements_text(security_groups) as sg
where
  sg = $1

-- attached ELBs
union all select
  title as "Title",
  'aws_ec2_classic_load_balancer' as "Type",
  arn as "ARN",
  null as link
from
  aws_ec2_classic_load_balancer,
  jsonb_array_elements_text(security_groups) as sg
where
  sg = $1

-- attached ALBs
union all select
  title as "Title",
  'aws_ec2_application_load_balancer' as "Type",
  arn as "ARN",
  null as link
from
  aws_ec2_application_load_balancer,
  jsonb_array_elements_text(security_groups) as sg
where
  sg = $1

-- attached NLBs
union all select
  title as "Title",
  'aws_ec2_network_load_balancer' as "Type",
  arn as "ARN",
  null as link
from
  aws_ec2_network_load_balancer,
  jsonb_array_elements_text(security_groups) as sg
where
  sg = $1


-- attached GWLBs
union all select
    title as "Title",
    'aws_ec2_gateway_load_balancer' as "Type",
    arn as "ARN",
    null as link
  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 as "Title",
    'aws_ec2_launch_configuration' as "Type",
    launch_configuration_arn as "ARN",
    null as link
  from
    aws_ec2_launch_configuration,
    jsonb_array_elements_text(security_groups) as sg
  where
    sg = $1


-- attached DAX Cluster
union all select
    title as "Title",
    'aws_dax_cluster' as "Type",
    arn as "ARN",
    null as link
  from
    aws_dax_cluster,
    jsonb_array_elements(security_groups) as sg
  where
    sg ->> 'SecurityGroupIdentifier' = $1

-- attached aws_dms_replication_instance
union all select
    title as "Title",
    'aws_dms_replication_instance' as "Type",
    arn as "ARN",
    null as link
  from
    aws_dms_replication_instance,
    jsonb_array_elements(vpc_security_groups) as sg
  where
    sg ->> 'VpcSecurityGroupId' = $1

-- attached aws_efs_mount_target
union all select
    title as "Title",
    'aws_efs_mount_target' as "Type",
    mount_target_id as "ARN",
    null as link
  from
    aws_efs_mount_target,
    jsonb_array_elements_text(security_groups) as sg
  where
    sg = $1

-- attached aws_elasticache_cluster
union all select
    title as "Title",
    'aws_elasticache_cluster' as "Type",
    arn as "ARN",
    null as link
  from
    aws_elasticache_cluster,
    jsonb_array_elements(security_groups) as sg
  where
    sg ->> 'SecurityGroupId' = $1


-- attached aws_rds_db_cluster
union all select
    title as "Title",
    'aws_rds_db_cluster' as "Type",
    arn as "ARN",
    null as link
  from
    aws_rds_db_cluster,
    jsonb_array_elements(vpc_security_groups) as sg
  where
    sg ->> 'SecurityGroupId' = $1

-- attached aws_rds_db_instance
union all select
    title as "Title",
    'aws_rds_db_instance' as "Type",
    arn as "ARN",
  '/aws_insights.dashboard.rds_db_instance_detail?input.db_instance_arn=' || arn as link
  from
    aws_rds_db_instance,
    jsonb_array_elements(vpc_security_groups) as sg
  where
    sg ->> 'VpcSecurityGroupId' = $1


-- attached aws_redshift_cluster
union all select
    title as "Title",
    'aws_redshift_cluster' as "Type",
    arn as "ARN",
  '/aws_insights.dashboard.redshift_cluster_detail?input.cluster_arn=' || arn as link
  from
    aws_redshift_cluster,
    jsonb_array_elements(vpc_security_groups) as sg
  where
    sg ->> 'VpcSecurityGroupId' = $1


-- attached aws_sagemaker_notebook_instance
union all select
    title as "Title",
    'aws_sagemaker_notebook_instance' as "Type",
    arn as "ARN",
    null as link
  from
    aws_sagemaker_notebook_instance,
    jsonb_array_elements_text(security_groups) as sg
  where
    sg = $1