ct-log-sample-queries

SELECT distinct issuer_ca_id, COUNT(*) FROM certificate_identity ci WHERE ci.NAME_TYPE = 'dNSName' AND reverse(lower(ci.NAME_VALUE)) LIKE reverse(lower('%gov.in')) GROUP BY issuer_ca_id ORDER BY COUNT(*) desc;

SELECT certificate_id,issuer_o,issuer_cn FROM ccadb_certificate WHERE certificate_id IN(SELECT distinct certificate_id FROM certificate_identity ci WHERE ci.NAME_TYPE = 'dNSName' AND reverse(lower(ci.NAME_VALUE)) LIKE reverse(lower('%appsecco.com')));

SELECT distinct  ca.name, COUNT(*) count FROM certificate_identity ci, ca ca WHERE ci.NAME_TYPE = 'dNSName' AND reverse(lower(ci.NAME_VALUE)) LIKE reverse(lower('%gov.in')) AND ca.id=ci.issuer_ca_id GROUP BY ca.name ORDER BY count desc;