Control: EC2 instances high level findings should not be there in inspector scans

powerpipe control run aws_compliance.control.ec2_instance_no_high_level_finding_in_inspector_scan

powerpipe login
powerpipe control run aws_compliance.control.ec2_instance_no_high_level_finding_in_inspector_scan --share

with severity_list as (
  select
    distinct title ,
    a ->> 'Value' as instance_id
  from
    aws_inspector_finding,
    jsonb_array_elements(attributes) as a
  where
    severity = 'High'
    and asset_type = 'ec2-instance'
    and a ->> 'Key' = 'INSTANCE_ID'
  group by
    a ->> 'Value',
    title
), ec2_istance_list as (
  select
    distinct instance_id
  from
    severity_list
)
select
  arn as resource,
  case
    when l.instance_id is null then 'ok'
    else 'alarm'
  end as status,
  case
    when l.instance_id is null then i.title || ' has no high level finding in inspector scans.'
    else i.title || ' has ' || (select count(*) from severity_list where instance_id = i.instance_id) || ' high level findings in inspector scans.'
  end as reason
  
  , i.region, i.account_id
from
  aws_ec2_instance as i
  left join ec2_istance_list as l on i.instance_id = l.instance_id;