Query: ssl_server_cbc_cipher_count

with domain_list as (
  select $1 as domain, $1 || ':443' as address
),
cbc_cipher_count as (
  select
    address,
    count(address) as cipher_count
  from
    net_tls_connection
  where
    address in (select address from domain_list)
    and cipher_suite_name in ('TLS_RSA_WITH_AES_128_CBC_SHA', 'TLS_RSA_WITH_AES_256_CBC_SHA', 'TLS_ECDHE_ECDSA_WITH_AES_128_CBC_SHA', 'TLS_ECDHE_ECDSA_WITH_AES_256_CBC_SHA', 'TLS_ECDHE_RSA_WITH_AES_128_CBC_SHA', 'TLS_ECDHE_RSA_WITH_AES_256_CBC_SHA', 'TLS_RSA_WITH_3DES_EDE_CBC_SHA', 'TLS_RSA_WITH_AES_128_CBC_SHA256', 'TLS_ECDHE_RSA_WITH_3DES_EDE_CBC_SHA', 'TLS_ECDHE_ECDSA_WITH_AES_128_CBC_SHA256', 'TLS_ECDHE_RSA_WITH_AES_128_CBC_SHA256')
    and handshake_completed
  group by
    address
)
select
  'CBC Cipher Suites' as label,
  case
    when i.cipher_count is null then 0
    else i.cipher_count
  end as value,
  case
    when i.cipher_count is null then 'ok'
    when i.cipher_count < 1 then 'ok'
    else 'alert'
  end as type
from
  domain_list as d
  left join cbc_cipher_count as i on d.address = i.address