Query: keyvault_with_non_rbac_secret_expiration_set

with non_rbac_vault as (
  select
    name
  from
    azure_key_vault
  where not enable_rbac_authorization
)
select
  kvs.id as resource,
  case
    when v.name is null then 'skip'
    when enabled and expires_at is null then 'alarm'
    else 'ok'
  end as status,
  vault_name || ' key ' || kvs.name ||
  case
    when v.name is null then ' RBAC enabled vault.'
    when enabled and expires_at is null then ' expiration date not set.'
    when not enabled then ' disabled.'
    else ' expiration date set to ' || to_char(expires_at, 'DD-Mon-YYYY') || '.'
  end as reason
  
  , kvs.resource_group as resource_group
  , sub.display_name as subscription
from
  azure_key_vault_secret kvs
  left join non_rbac_vault as v on v.name = kvs.vault_name,
  azure_subscription sub
where
  sub.subscription_id = kvs.subscription_id;