turbot/azure_tags

Control: CosmosDB mongo databases should have appropriate tag values

Description

Check if CosmosDB mongo databases have appropriate tag values.

Usage

Run the control in your terminal:

powerpipe control run azure_tags.control.cosmosdb_mongo_database_expected_tag_values

Snapshot and share results via Turbot Pipes:

powerpipe login
powerpipe control run azure_tags.control.cosmosdb_mongo_database_expected_tag_values --share

Steampipe Tables

Params

ArgsNameDefaultDescriptionVariable
$1expected_tag_values
{"Environment":["Dev","Staging","Prod"]}

SQL

with raw_data as
(
select
id,
title,
tags,
row_to_json(json_each($1)) as expected_tag_values,
_ctx,
resource_group,
subscription_id,
region
from
azure_cosmosdb_mongo_database
where
tags::text <> '{}'
or tags is not null
),
exploded_expected_tag_values as
(
select
id,
title,
expected_tag_values ->> 'key' as tag_key,
jsonb_array_elements_text((expected_tag_values ->> 'value')::jsonb) as expected_values,
tags ->> (expected_tag_values ->> 'key') as current_value,
_ctx,
tags,
resource_group,
subscription_id,
region
from
raw_data
),
analysis as
(
select
id,
title,
current_value like expected_values as has_appropriate_value,
case
when current_value is null then true
else false
end as has_no_matching_tags,
tag_key,
current_value,
tags,
_ctx,
resource_group,
subscription_id,
region
from
exploded_expected_tag_values
),
status_by_tag as
(
select
id,
title,
bool_or(has_appropriate_value) as status,
tag_key,
case
when bool_or(has_appropriate_value) then ''
else tag_key
end as reason,
bool_or(has_no_matching_tags) as can_skip,
current_value,
_ctx,
tags,
resource_group,
subscription_id,
region
from
analysis
group by
id,
title,
tag_key,
current_value,
_ctx,
resource_group,
subscription_id,
region,
tags
)
select
id as resource,
case
when bool_and(can_skip) then 'skip'
when bool_and(status) then 'ok'
else 'alarm'
end as status,
case
when bool_and(can_skip) then title || ' has no matching tag keys.'
when bool_and(status) then title || ' has expected tag values for tags: ' || array_to_string(array_agg(tag_key) filter(where status), ', ') || '.'
else title || ' has unexpected tag values for tags: ' || array_to_string(array_agg(tag_key) filter(where not status), ', ') || '.'
end as reason
, subscription_id as subscription_id
from
status_by_tag
group by
id,
title,
_ctx,
resource_group,
subscription_id,
region,
tags
union all
select
id as resource,
'skip' as status,
title || ' has no tags.' as reason
, subscription_id as subscription_id
from
azure_cosmosdb_mongo_database
where
tags::text = '{}'
or tags is null
union all
select
id as resource,
'skip' as status,
title || ' has tags but no expected tag values are set.' as reason
, subscription_id as subscription_id
from
azure_cosmosdb_mongo_database
where
$1::text = '{}'
and tags::text <> '{}'
or tags is not null;