Query: objectstorage_bucket_write_logging_enabled

with bucket_logs as (
  select
    configuration -> 'source' ->> 'resource' as bucket_identifier,
    configuration -> 'source' ->> 'category' as category,
    lifecycle_state,
    title as log_name,
    log_group_id,
    is_enabled,
    retention_duration,
    region,
    compartment_id as log_compartment_id
  from
    oci_logging_log
  where
    configuration -> 'source' ->> 'service' = 'objectstorage'
    and configuration -> 'source' ->> 'category' ilike '%write%'
)
select
  b.id as resource,
  case
    when bl.is_enabled and lower(coalesce(bl.lifecycle_state, '')) = 'active' then 'ok'
    else 'alarm'
  end as status,
  case
    when bl.is_enabled and lower(coalesce(bl.lifecycle_state, '')) = 'active' then
      b.title || ' write access logging enabled in log group ' ||
      coalesce(g.display_name, bl.log_group_id, 'unknown') ||
      ' (log: ' || coalesce(bl.log_name, 'unknown') || ').'
    when bl.log_group_id is null then
      b.title || ' write access logging disabled (no write log configured).'
    when not coalesce(bl.is_enabled, false) then
      b.title || ' write access logging log exists but disabled.'
    else
      b.title || ' write access logging log lifecycle state ' ||
      coalesce(bl.lifecycle_state, 'unknown') || '.'
  end as reason
  
  , b.region as region, b.tenant_name as tenant
  , coalesce(c.name, 'root') as compartment
from
  oci_objectstorage_bucket b
  left join bucket_logs bl on bl.bucket_identifier in (
    b.id,
    b.name,
    format('%s/%s', b.namespace, b.name),
    format('%s_write', b.name)
  )
  left join oci_logging_log_group g on g.id = bl.log_group_id
  left join oci_identity_compartment c on c.id = b.compartment_id;