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 loginpowerpipe control run gcp_labels.control.bigquery_table_expected_label_values --share
Steampipe Tables
Params
Args | Name | Default | Description | Variable |
---|---|---|---|---|
$1 | expected_label_values |
|
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, projectfrom status_by_labelgroup by self_link, title, location, projectunion allselect self_link as resource, 'skip' as status, title || ' has no labels.' as reason, location, projectfrom gcp_bigquery_tablewhere labels is nullunion allselect self_link as resource, 'skip' as status, title || ' has labels but no expected label values are set.' as reason, location, projectfrom gcp_bigquery_tablewhere $1::text = '{}' and labels is not null