Query: sql_server_vulnerability_assessment_enabled

with sql_server_va as (
  select
    distinct id
  from
    azure_sql_server as s,
    jsonb_array_elements(server_vulnerability_assessment) as va
  where
    va -> 'properties' -> 'recurringScans' ->> 'isEnabled' = 'true'
)
select
  'Vulnerability Assessment' as label,
  case when v.id is not null then 'Enabled' else 'Disabled' end as value,
  case when v.id is not null then 'ok' else 'alert' end as type
from
  azure_sql_server as s left join sql_server_va as v on lower(s.id) = lower(v.id)
  where lower(s.id) = $1
  and s.subscription_id = split_part($1, '/', 3);