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