Query: compute_ssl_policy_with_no_weak_cipher

with all_proxies as (
  select
    name,
    _ctx,
    self_link,
    split_part(kind, '#', 2) proxy_type,
    ssl_policy,
    title,
    location,
    project
  from
    gcp_compute_target_ssl_proxy
  union
  select
    name,
    _ctx,
    self_link,
    split_part(kind, '#', 2) proxy_type,
    ssl_policy,
    title,
    location,
    project
  from
    gcp_compute_target_https_proxy
),
ssl_policy_without_weak_cipher as (
  select
    self_link
  from
    gcp_compute_ssl_policy
  where
    (profile = 'MODERN' and min_tls_version = 'TLS_1_2')
    or profile = 'RESTRICTED'
    or (profile = 'CUSTOM' and not (enabled_features ?| array['TLS_RSA_WITH_AES_128_GCM_SHA256', 'TLS_RSA_WITH_AES_256_GCM_SHA384', 'TLS_RSA_WITH_AES_128_CBC_SHA', 'TLS_RSA_WITH_AES_256_CBC_SHA', 'TLS_RSA_WITH_3DES_EDE_CBC_SHA']))
)
select
  self_link resource,
  case
    when ssl_policy is null or ssl_policy in (select self_link from ssl_policy_without_weak_cipher) then 'ok'
    else 'alarm'
  end as status,
  case
    when ssl_policy is null
      then proxy_type || ' ' || title || ' has no SSL policy.'
    when ssl_policy is null or ssl_policy in (select self_link from ssl_policy_without_weak_cipher)
      then proxy_type || ' ' || title || ' SSL policy contains CIS compliant cipher.'
    else proxy_type || ' ' || title || ' SSL policy contains weak cipher.'
  end as reason
  , project as project
from all_proxies;