Control: SQL database instances should have appropriate label values

powerpipe control run gcp_labels.control.sql_database_instance_expected_label_values

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

with raw_data as
(
  select
    self_link,
    title,
    labels,
    row_to_json(json_each($1)) as expected_label_values,
    location, project
  from
    gcp_sql_database_instance
  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_sql_database_instance
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_sql_database_instance
where
  $1::text = '{}'
  and labels is not null