Certificate Transparency

$ psql -h crt.sh -p 5432 -U guest certwatch

certwatch=> \o ct.txt
certwatch=> SELECT NAME_VALUE FROM certificate_identity WHERE reverse(lower(NAME_VALUE)) LIKE reverse('%.cz');

$ grep --only-matching --perl-regexp "[a-zA-Z0-9-]+\.cz" ct.txt | sort | uniq > cztld.txt

certwatch=> SELECT ci.name_value FROM certificate c JOIN certificate_identity ci ON c.id = ci.certificate_id WHERE x509_notAfter(c.CERTIFICATE) > '2017-11-10' AND reverse(lower(ci.name_value)) LIKE reverse('%.cz');

\a toggles align
SELECT ci.name_value hostname, ca.name issuer FROM certificate c JOIN certificate_identity ci ON c.id = ci.certificate_id JOIN ca ON ci.issuer_ca_id = ca.id WHERE x509_notAfter(c.CERTIFICATE) > '2018-05-11 00:00:00+02' AND reverse(lower(ci.name_value)) LIKE reverse('%.cz');