turbot/gcp_labels

Control: BigQuery tables should have appropriate label values

Description

Check if BigQuery tables have appropriate label values.

Usage

Run the control in your terminal:

powerpipe control run gcp_labels.control.bigquery_table_expected_label_values

Snapshot and share results via Turbot Pipes:

powerpipe login
powerpipe control run gcp_labels.control.bigquery_table_expected_label_values --share

Steampipe Tables

Params

ArgsNameDefaultDescriptionVariable
$1expected_label_values
{"environment":["dev","staging","prod"]}

SQL

with raw_data as
(
select
self_link,
title,
labels,
row_to_json(json_each($1)) as expected_label_values,
location, project
from
gcp_bigquery_table
where
labels is not null
),
exploded_expected_label_values as
(
select
self_link,
title,
expected_label_values ->> 'key' as label_key,
jsonb_array_elements_text((expected_label_values ->> 'value')::jsonb) as expected_values,
labels ->> (expected_label_values ->> 'key') as current_value,
location, project
from
raw_data
),
analysis as
(
select
self_link,
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_labels,
label_key,
current_value,
location, project
from
exploded_expected_label_values
),
status_by_label as
(
select
self_link,
title,
bool_or(has_appropriate_value) as status,
label_key,
case
when bool_or(has_appropriate_value) then ''
else label_key
end as reason,
bool_or(has_no_matching_labels) as can_skip,
current_value,
location, project
from
analysis
group by
self_link,
title,
label_key,
current_value,
location, project
)
select
self_link 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 label keys.'
when bool_and(status) then title || ' has expected label values for labels: ' || array_to_string(array_agg(label_key) filter(where status), ', ') || '.'
else title || ' has unexpected label values for labels: ' || array_to_string(array_agg(label_key) filter(where not status), ', ') || '.'
end as reason,
location, project
from
status_by_label
group by
self_link,
title,
location, project
union all
select
self_link as resource,
'skip' as status,
title || ' has no labels.' as reason,
location, project
from
gcp_bigquery_table
where
labels is null
union all
select
self_link as resource,
'skip' as status,
title || ' has labels but no expected label values are set.' as reason,
location, project
from
gcp_bigquery_table
where
$1::text = '{}'
and labels is not null