Query: storage_account_blob_service_logging_enabled

with blob_logs as (
  select
    a.id,
    a.name,
    log ->> 'category' as category,
    (log ->> 'enabled')::boolean as enabled
  from
    azure_storage_account a,
    jsonb_array_elements(a.default_blob_diagnostic_settings) as b,
    jsonb_array_elements(b -> 'properties' -> 'logs') as log
  where
    log ->> 'category' in ('StorageRead', 'StorageWrite', 'StorageDelete')
),
log_status as (
  select
    id,
    name,
    max(case when category = 'StorageRead' then (enabled::int) else 0 end) = 1 as read_enabled,
    max(case when category = 'StorageWrite' then (enabled::int) else 0 end) = 1 as write_enabled,
    max(case when category = 'StorageDelete' then (enabled::int) else 0 end) = 1 as delete_enabled
  from blob_logs
  group by id, name
)
select
  sa.id as resource,
  case
    when lower(sku_tier) = 'premium' and kind in ('StorageV2', 'FileStorage') then 'skip'
    when default_blob_diagnostic_settings is null then 'alarm'
    when ls.read_enabled and ls.write_enabled and ls.delete_enabled then 'ok'
    else 'alarm'
  end as status,
  case
    when lower(sku_tier) = 'premium' and kind in ('StorageV2', 'FileStorage') then sa.name || ' is premium ' || kind || ' storage account.'
    when default_blob_diagnostic_settings is null then sa.name || ' blob service logging disabled for read, write, delete requests.'
    when ls.read_enabled and ls.write_enabled and ls.delete_enabled then sa.name || ' blob service logging enabled for read, write, delete requests.'
    else sa.name || ' blob service logging missing for: ' ||
      trim(both ', ' from
        case when not ls.read_enabled then 'read, ' else '' end ||
        case when not ls.write_enabled then 'write, ' else '' end ||
        case when not ls.delete_enabled then 'delete, ' else '' end
      ) || ' requests.'
  end as reason
  
  , sa.resource_group as resource_group
  , sub.display_name as subscription
from
  azure_storage_account sa
  left join log_status as ls on ls.id = sa.id
  left join azure_subscription sub on sub.subscription_id = sa.subscription_id