Control: SQL server threat detection should be enabled for all

powerpipe control run azure_compliance.control.sql_server_threat_detection_all_enabled

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

with threat_detection_disabled as (
  select
    distinct id
  from
    azure_sql_server s,
    jsonb_array_elements(server_security_alert_policy) p
  where
    not (p -> 'properties' -> 'disabledAlerts' = '[""]')
)
select
  s.id as resource,
  case
    when t.id is null then 'ok'
    else 'alarm'
  end as status,
  case
    when t.id is null then name || ' threat detection enabled for all.'
    else  name || ' threat detection not enabled for all.'
  end as reason
  
  , s.resource_group as resource_group
  , sub.display_name as subscription
from
  azure_sql_server s
  left join threat_detection_disabled as t on t.id = s.id,
  azure_subscription sub
where
  sub.subscription_id = s.subscription_id;