Query: sql_server_auditing_status

with auditing_enabled as (
  select
    distinct id
  from
    azure_sql_server as s,
    jsonb_array_elements(server_audit_policy) as audit
  where
    audit -> 'properties' ->> 'state' = 'Enabled'
),
auditing_enabled_status as (
  select
    case
      when a.id is not null then 'enabled'
      else 'disabled' end as auditing_enabled_status
  from
    azure_sql_server as s
    left join auditing_enabled as a on s.id = a.id
)
select
  auditing_enabled_status,
  count(*)
from
  auditing_enabled_status
group by
  auditing_enabled_status;