standard
turbot/aws_tags

Trigger: Detect & correct CodePipeline pipelines with incorrect tags

Detects CodePipeline pipelines with incorrect tags and optionally attempts to correct them.

Query

with tags as (
select
title as title,
arn,
region,
account_id,
sp_connection_name as conn,
coalesce(tags, '{}' :: jsonb) as tags,
key,
value
from
aws_codepipeline_pipeline
left join jsonb_each_text(tags) as t(key, value) on true
),
updated_tags as (
select
arn,
key as old_key,
case
when false then key
else key
end as new_key,
value
from
tags
where
key is not null
and key not like 'aws:%'
),
required_tags as (
select
r.arn,
null as old_key,
a.key as new_key,
a.value
from
(
select
distinct arn
from
aws_codepipeline_pipeline
) r
cross join (
values
(null, null)
) as a(key, value)
where
not exists (
select
1
from
updated_tags ut
where
ut.arn = r.arn
and ut.new_key = a.key
)
),
all_tags as (
select
arn,
old_key,
new_key,
value
from
updated_tags
union all
select
arn,
old_key,
new_key,
value
from
required_tags
where
new_key is not null
),
allowed_tags as (
select
distinct arn,
new_key
from
(
select
arn,
new_key,
case
when new_key like '%' then true
else false
end as allowed
from
all_tags
) a
where
allowed = true
),
remove_tags as (
select
distinct arn,
key
from
(
select
arn,
new_key as key,
case
when new_key like '%' then false
else false
end as remove
from
all_tags
) r
where
remove = true
union
select
arn,
old_key as key
from
all_tags
where
old_key is not null
and old_key != new_key
union
select
arn,
new_key as key
from
all_tags a
where
not exists (
select
1
from
allowed_tags at
where
at.arn = a.arn
and at.new_key = a.new_key
)
),
updated_values as (
select
arn,
new_key,
value as old_value,
case
when false then value
else value
end as updated_value
from
all_tags
)
select
*
from
(
select
t.title,
t.arn,
t.region,
t.account_id,
t.conn,
coalesce(
(
select
jsonb_agg(key)
from
remove_tags rt
where
rt.arn = t.arn
),
'[]' :: jsonb
) as remove,
coalesce(
(
select
jsonb_object_agg(at.new_key, at.value)
from
all_tags at
where
at.arn = t.arn
and at.new_key != coalesce(at.old_key, '')
and not exists (
select
1
from
remove_tags rt
where
rt.arn = at.arn
and rt.key = at.new_key
)
),
'{}' :: jsonb
) || coalesce(
(
select
jsonb_object_agg(uv.new_key, uv.updated_value)
from
updated_values uv
where
uv.arn = t.arn
and uv.updated_value != uv.old_value
and not exists (
select
1
from
remove_tags rt
where
rt.arn = uv.arn
and rt.key = uv.new_key
)
),
'{}' :: jsonb
) as upsert
from
tags t
group by
t.title,
t.arn,
t.region,
t.account_id,
t.conn
) result
where
remove != '[]' :: jsonb
or upsert != '{}' :: jsonb;

Schedule

15m

Tags

category = tags
plugin = aws
service = AWS/CodePipeline