standard
turbot/azure_tags

Trigger: Detect & correct Network watcher flow logs with incorrect tags

Detects Network watcher flow logs with incorrect tags and optionally attempts to correct them.

Query

with tags as (
select
coalesce(name, title) as title,
id,
region,
subscription_id,
sp_connection_name as conn,
coalesce(tags, '{}' :: jsonb) as tags,
t.key,
t.value
from
azure_network_watcher_flow_log
left join jsonb_each_text(tags) as t(key, value) on true
),
updated_tags as (
select
id,
key as old_key,
case
when false then key
else key
end as new_key,
value
from
tags
where
key is not null
),
required_tags as (
select
r.id,
null as old_key,
a.key as new_key,
a.value
from
(
select
distinct id
from
azure_network_watcher_flow_log
) r
cross join (
values
(null, null)
) as a(key, value)
where
not exists (
select
1
from
updated_tags ut
where
ut.id = r.id
and ut.new_key = a.key
)
),
all_tags as (
select
id,
old_key,
new_key,
value
from
updated_tags
union all
select
id,
old_key,
new_key,
value
from
required_tags
where
new_key is not null
),
allowed_tags as (
select
distinct id,
new_key
from
(
select
id,
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 id,
key
from
(
select
id,
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
id,
new_key as key
from
all_tags a
where
not exists (
select
1
from
allowed_tags at
where
at.id = a.id
and at.new_key = a.new_key
)
),
updated_values as (
select
id,
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.id,
t.region,
t.subscription_id,
t.conn,
t.tags as old_tags,
jsonb_object_agg(uv.new_key, uv.updated_value) as new_tags
from
tags t
join updated_values uv on t.id = uv.id
where
not exists (
select
1
from
remove_tags rt
where
rt.id = uv.id
and rt.key = uv.new_key
)
group by
t.title,
t.id,
t.region,
t.subscription_id,
t.conn,
t.tags
) result
where
old_tags != new_tags;

Schedule

15m

Tags

category = tags
plugin = azure
service = Azure/Network