Query: compute_target_https_uses_latest_tls_version

with all_proxies as (
  select
    name,
    _ctx,
    self_link,
    split_part(kind, '#', 2) proxy_type,
    ssl_policy,
    title,
    location,
    project
  from
    gcp_compute_target_https_proxy
),ssl_policy_with_no_latest_tls as (
  select
    self_link
  from
    gcp_compute_ssl_policy
  where
    (profile = 'MODERN' or profile = 'CUSTOM')
    and min_tls_version = 'TLS_1_2'
)
select
  self_link resource,
  case
    when ssl_policy = '' or ssl_policy in (select self_link from ssl_policy_with_no_latest_tls) then 'ok'
    else 'alarm'
  end as status,
  case
    when  ssl_policy = '' then  title || ' has no SSL policy.'
    when ssl_policy in (select self_link from ssl_policy_with_no_latest_tls) then title || ' uses latest TLS version.'
    else title || ' not uses letest TLS version.'
  end as reason
  , project as project
from
  gcp_compute_target_https_proxy;