Query: keyvault_key_automatic_rotation_enabled

with key_rotation_policy as (
  select
    id
  from
    azure_key_vault_key,
    jsonb_array_elements(rotation_policy -> 'lifetimeActions') as lifetimeActions
  where
    lifetimeActions -> 'action' ->> 'type' = 'Rotate'
    and lifetimeActions -> 'trigger' -> 'timeAfterCreate' is not null
)
select
  kvk.id as resource,
  case
    when p.id is not null then 'ok'
    else 'alarm'
  end as status,
  case
    when p.id is not null then vault_name || ' key ' || kvk.name || ' automatic rotation enabled.'
    else vault_name || ' key ' || kvk.name || ' automatic rotation disabled.'
  end as reason
  
  , kvk.resource_group as resource_group
  , sub.display_name as subscription
from
  azure_key_vault_key kvk
  left join key_rotation_policy as p on p.id = kvk.id
  left join  azure_subscription sub on sub.subscription_id = kvk.subscription_id;