Query: databricks_workspace_diagnostic_log_delivery_configured

with diagnostic_settings_analysis as (
  select
    distinct id,
    (select count(*) from jsonb_array_elements(diagnostic_settings) as ds where ds -> 'properties' -> 'logs' @> '[{"category": "clusters", "enabled": true}]') as clusters_logs_enabled,
    (select count(*) from jsonb_array_elements(diagnostic_settings) as ds where ds -> 'properties' -> 'logs' @> '[{"category": "accounts", "enabled": true}]') as accounts_logs_enabled,
    (select count(*) from jsonb_array_elements(diagnostic_settings) as ds where ds -> 'properties' -> 'logs' @> '[{"category": "jobs", "enabled": true}]') as jobs_logs_enabled,
    (select count(*) from jsonb_array_elements(diagnostic_settings) as ds where ds -> 'properties' -> 'logs' @> '[{"category": "notebook", "enabled": true}]') as notebook_logs_enabled,
    (select count(*) from jsonb_array_elements(diagnostic_settings) as ds where ds -> 'properties' -> 'logs' @> '[{"category": "workspace", "enabled": true}]') as workspace_logs_enabled,
    (select count(*) from jsonb_array_elements(diagnostic_settings) as ds where ds -> 'properties' -> 'logs' @> '[{"category": "sqlPermissions", "enabled": true}]') as sqlpermissions_logs_enabled
  from
    azure_databricks_workspace
  where
    diagnostic_settings is not null and jsonb_array_length(diagnostic_settings) > 0
)
select
  w.id as resource,
  case
    when w.sku ->> 'name' in ('standard', 'trial') then 'skip'
    when w.diagnostic_settings is null or jsonb_array_length(w.diagnostic_settings) = 0  then 'alarm'
    when dsa.clusters_logs_enabled = 0 then 'alarm'
    when dsa.accounts_logs_enabled = 0 then 'alarm'
    when dsa.jobs_logs_enabled = 0 then 'alarm'
    when dsa.notebook_logs_enabled = 0 then 'alarm'
    when dsa.workspace_logs_enabled = 0 then 'alarm'
    when dsa.sqlpermissions_logs_enabled = 0 then 'alarm'
    else 'ok'
  end as status,
  case
    when w.sku ->> 'name' in ('standard', 'trial') then w.name || ' is on the ' || (w.sku -> 'name') || ' pricing tier.'
    when w.diagnostic_settings is null or jsonb_array_length(w.diagnostic_settings) = 0 then w.name || ' has no diagnostic settings configured.'
    when dsa.clusters_logs_enabled = 0 then w.name || ' diagnostic logging is missing required category: clusters.'
    when dsa.accounts_logs_enabled = 0 then w.name || ' diagnostic logging is missing required category: accounts.'
    when dsa.jobs_logs_enabled = 0 then w.name || ' diagnostic logging is missing required category: jobs.'
    when dsa.notebook_logs_enabled = 0 then w.name || ' diagnostic logging is missing required category: notebook.'
    when dsa.workspace_logs_enabled = 0 then w.name || ' diagnostic logging is missing required category: workspace.'
    when dsa.sqlpermissions_logs_enabled = 0 then w.name || ' diagnostic logging is missing required category: sqlPermissions.'
    else w.name || ' diagnostic logging is properly configured with required categories and destinations.'
  end as reason
  
  , w.resource_group as resource_group
  , sub.display_name as subscription
from
  azure_databricks_workspace as w
  left join diagnostic_settings_analysis as dsa on dsa.id = w.id
  left join azure_subscription as sub on sub.subscription_id = w.subscription_id;