Query: securitycenter_azure_defender_on_for_database

with defender_list as (
  select
    json_object_agg(name, pricing_tier) as data,
    subscription_id
  from
    azure_security_center_subscription_pricing
  where
    name = any(
      ARRAY [ 'CosmosDbs',
      'OpenSourceRelationalDatabases',
      'SqlServerVirtualMachines',
      'SqlServers' ]
    )
  group by
    subscription_id
)
select
  sub.id as resource,
  case
    when data ->> 'CosmosDbs' = 'Standard'
    and data ->> 'OpenSourceRelationalDatabases' = 'Standard'
    and data ->> 'SqlServerVirtualMachines' = 'Standard'
    and data ->> 'SqlServers' = 'Standard' then 'ok'
    else 'alarm'
  end as status,
  case
    when data ->> 'CosmosDbs' = 'Standard'
    and data ->> 'OpenSourceRelationalDatabases' = 'Standard'
    and data ->> 'SqlServerVirtualMachines' = 'Standard'
    and data ->> 'SqlServers' = 'Standard' then 'Azure Defender on for Databases.'
    else 'Azure Defender off for Databases.'
  end as reason,
  sub.display_name as subscription
from
  azure_subscription as sub
  left join defender_list as l on l.subscription_id = sub.subscription_id;