turbot/aws_insights

Query: ecs_clusters_for_ec2_instance

Usage

powerpipe query aws_insights.query.ecs_clusters_for_ec2_instance

SQL

with aws_ec2_instances as (
select
instance_id,
account_id,
region
from
aws_ec2_instance
where
account_id = split_part($1, ':', 5)
and region = split_part($1, ':', 4)
and arn = $1
),
aws_ecs_container_instances as (
select
ec2_instance_id,
cluster_arn,
account_id,
region
from
aws_ecs_container_instance
where
account_id = split_part($1, ':', 5)
and region = split_part($1, ':', 4)
),
aws_ecs_clusters as (
select
cluster_arn,
account_id,
region
from
aws_ecs_cluster
where
account_id = split_part($1, ':', 5)
and region = split_part($1, ':', 4)
)
select
distinct cluster.cluster_arn as cluster_arn
from
aws_ec2_instances as i,
aws_ecs_container_instances as ci,
aws_ecs_clusters as cluster
where
ci.ec2_instance_id = i.instance_id
and ci.cluster_arn = cluster.cluster_arn;