Control: Ensure that Storage Account access keys are periodically regenerated

powerpipe control run azure_compliance.control.storage_account_access_keys_periodically_regenerated

powerpipe login
powerpipe control run azure_compliance.control.storage_account_access_keys_periodically_regenerated --share

with storage_account_key_status as (
  select
    sa.id,
    sa.name as storage_account_name,
    sa.subscription_id,
    sa._ctx,
    sa.region,
    sa.resource_group,
    sa.tags,
    key ->> 'KeyName'  as key_name,
    (key ->> 'CreationTime')::timestamptz as last_rotated,
    extract(
      epoch
      from (now() - (key ->> 'CreationTime')::timestamptz)
    ) / 86400    as days_since_rotation
  from
    azure_storage_account as sa
    left join lateral jsonb_array_elements(sa.access_keys) as key on true
)
select
  saks.id as resource,
  case
    when saks.key_name is null then 'skip'
    when saks.days_since_rotation > 90 then 'alarm'
    else 'ok'
  end as status,
  case
    when saks.key_name is null then saks.storage_account_name || ' has no access keys available.'
    else saks.storage_account_name || ' ' || saks.key_name || ' last rotated ' || floor(saks.days_since_rotation)::int || ' days ago.'
  end as reason
  
  , saks.resource_group as resource_group
  , sub.display_name as subscription
from
  storage_account_key_status saks
  left join azure_subscription sub on sub.subscription_id = saks.subscription_id;