Useful scripts for crt.sh path hacking

corbin-dallas-multipath.sql

SELECT DISTINCT
  ca.ID,
  (coalesce(ca.NUM_ISSUED[1],0) - coalesce(ca.NUM_EXPIRED[1], 0)) as unexpired,
  COUNT(DISTINCT c.ISSUER_CA_ID) as paths,
  ca.NAME
FROM
  ca
    INNER JOIN ca_certificate AS ca_c
    ON ca.ID = ca_c.CA_ID
  INNER JOIN certificate AS c
    ON ca_c.CERTIFICATE_ID = c.ID
  INNER JOIN ca_trust_purpose AS ca_t_p
    ON ca.ID = ca_t_p.CA_ID
WHERE
  -- Only unexpired certs
  x509_notAfter(c.CERTIFICATE) > NOW()
  -- Where the CA is trusted by Mozilla, for TLS
  AND ca_t_p.TRUST_CONTEXT_ID = 5
  AND ca_t_p.TRUST_PURPOSE_ID = 1
  -- And excluding those revoked via CRLs (which CRLSet is a proxy for)
  -- Notably, this intentionally includes CAs that have been revoked by
  -- Mozilla's OneCRL.
  AND NOT ca_t_p.ALL_CHAINS_REVOKED_VIA_CRLSET
GROUP BY
  ca.ID
HAVING
  -- With at least two different issuers
  COUNT(DISTINCT c.ISSUER_CA_ID) >= 2
ORDER BY
  unexpired DESC;