Query: ecs_instance_latest_os_patches_applied

with instances_with_unfixed_vulns as (
  select distinct
    instance_id,
    instance_name,
    region,
    account_id,
    count(*) as unfixed_vulnerability_count
  from
    alicloud_security_center_vulnerability
  where
    status = 0  -- 0 = unfixed
    and instance_id is not null
    and instance_id != ''
  group by
    instance_id,
    instance_name,
    region,
    account_id
)
select
  arn as resource,
  case
    when i.status != 'Running' then 'skip'
    when iv.unfixed_vulnerability_count > 0 then 'alarm'
    else 'ok'
  end as status,
  case
    when i.status != 'Running' then i.title || ' is not in running state.'
    when iv.unfixed_vulnerability_count > 0 then i.title || ' has ' || iv.unfixed_vulnerability_count || ' unfixed vulnerabilities.'
    else i.title || ' has all OS patches applied - no unfixed vulnerabilities found.'
  end as reason
  
  , i.account_id as account_id, i.region as region
from
  alicloud_ecs_instance i
  left join instances_with_unfixed_vulns iv on i.instance_id = iv.instance_id and i.region = iv.region and i.account_id = iv.account_id