Query: sql_database_vulnerability_assessment_status

with vulnerability_assessment_enabled as (
  select
    distinct id
  from
    azure_sql_database as s,
    jsonb_array_elements(vulnerability_assessments) as va
  where
    va -> 'properties' -> 'recurringScans' ->> 'isEnabled' = 'true'
),
vulnerability_assessment_status as (
  select
    case
      when va.id is not null then 'enabled'
      else 'disabled'
    end as vulnerability_assessment_status
  from
    azure_sql_database as s
    left join vulnerability_assessment_enabled as va on s.id = va.id
)
select
  vulnerability_assessment_status,
  count(*)
from
  vulnerability_assessment_status
group by
  vulnerability_assessment_status;