Control: Logic app workflows should have appropriate tag values

Usage

Run the control in your terminal:

steampipe check azure_tags.control.logic_app_workflow_expected_tag_values

Snapshot and share results via Steampipe Cloud:

steampipe login
steampipe check --share azure_tags.control.logic_app_workflow_expected_tag_values

azure_logic_app_workflow

Params

{"Environment":["Dev","Staging","Prod"]}

SQL

with raw_data as (
  select
    id,
    title,
    tags,
    row_to_json(json_each($1)) as expected_tag_values,
    resource_group,
    subscription_id
  from
    azure_logic_app_workflow
  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,
    resource_group,
    subscription_id
  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,
    resource_group,
    subscription_id
  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,
    resource_group,
    subscription_id
  from
    analysis
  group by
    id,
    title,
    tag_key,
    current_value,
    resource_group,
    subscription_id
)
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,
  resource_group,
  subscription_id
from
  status_by_tag
group by
  id,
  title,
  resource_group,
  subscription_id
union all
select
  id as resource,
  'skip' as status,
  title || ' has no tags.' as reason,
  resource_group,
  subscription_id
from
  azure_logic_app_workflow
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,
  resource_group,
  subscription_id
from
  azure_logic_app_workflow
where
  $1 :: text = '{}'
  and tags :: text <> '{}'
  or tags is not null;