Control: SQL databases should have vulnerability findings resolved

powerpipe control run azure_compliance.control.sql_database_vulnerability_findings_resolved

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

with vulnerability_findings as (
  select
    db.id as database_id,
    scan ->> 'endTime' latest_scan_end_time,
    scan ->> 'numberOfFailedSecurityChecks' no_of_failed_sec_checks
  from
    azure_sql_database as db,
    jsonb_array_elements(vulnerability_assessment_scan_records) as scan
  where
    (scan ->> 'numberOfFailedSecurityChecks')::int = 0
  order by scan ->> 'endTime' desc nulls last
  limit 1
)
select
  distinct a.id as resource,
  case
    when s.database_id is not null then 'ok'
    else 'alarm'
  end as status,
  case
    when s.database_id is not null then a.name || ' vulnerability findings resolved.'
    else a.title || ' vulnerability findings not resolved.'
  end as reason
  
  , a.resource_group as resource_group
  , sub.display_name as subscription
from
  azure_sql_database as a
  left join vulnerability_findings as s on a.id = s.database_id,
  azure_subscription as sub
where
  a.name <> 'master'
  and sub.subscription_id = a.subscription_id;