Table: crtsh_ca - Query crt.sh Certificate Authorities using SQL

crt.sh is a service provided by Sectigo that monitors the issuance of SSL/TLS certificates by various Certificate Authorities. It allows users to search for certificates by several criteria, including domain name, Subject Public Key Info, and Certificate Authority. It provides a comprehensive view of the SSL/TLS certificate ecosystem, helping identify misissued certificates and potential security risks.

Table Usage Guide

The crtsh_ca table provides insights into Certificate Authorities within crt.sh. As a security analyst, explore details about each Certificate Authority through this table, including their name, key, and associated metadata. Utilize it to uncover information about Certificate Authorities, such as their key details, the issuance of certificates, and the verification of their status.

Examples

Top 10 CA's by number of certificates issued

Determine the areas in which Certificate Authorities have issued the most certificates. This can assist in identifying which Certificate Authorities are the most active or popular, providing valuable insights into the digital certificate landscape.

select
  *
from
  crtsh_ca
order by
  num_certs_issued desc nulls last
limit
  10;

select
  *
from
  crtsh_ca
order by
  num_certs_issued desc
limit
  10;

Details of top 10 CA's by number of certificates issued

Explore which Certificate Authorities have issued the most certificates, providing a ranking of the top 10 based on the number of current, non-expired certificates they've issued. This helps in understanding the distribution and influence of various Certificate Authorities in the industry.

select
  id,
  name,
  num_certs_issued - num_certs_expired as num_certs_current,
  num_certs_issued
from
  crtsh_ca
order by
  num_certs_current desc nulls last
limit
  10;

select
  id,
  name,
  num_certs_issued - num_certs_expired as num_certs_current,
  num_certs_issued
from
  crtsh_ca
order by
  (
    case
      when num_certs_current is null then 1
      else 0
    end
  ),
  num_certs_current desc
limit
  10;

CA's based in Australia

Explore which Certificate Authorities are based in Australia. This can be beneficial when you want to identify and assess the elements within the Australian digital security landscape.

select
  *
from
  crtsh_ca
where
  name ilike 'C=AU%'
order by
  name;

select
  *
from
  crtsh_ca
where
  name like 'C=AU%'
order by
  name;

Schema for crtsh_ca

Name	Type	Operators	Description
_ctx	jsonb		Steampipe context in JSON form.
id	bigint	>, >=, =, <, <=, !=	Unique identifier of the CA.
linting_applies	boolean	=, !=	True if linting is applied to the certificate issued by the CA.
name	text	>, >=, =, <, <=, !=	Name of the CA.
num_certs_expired	bigint	>, >=, =, <, <=, !=	Number of certificates from the CA that have expired.
num_certs_issued	bigint	>, >=, =, <, <=, !=	Number of certificates issued by the CA.
num_precerts_expired	bigint	>, >=, =, <, <=, !=	Number of pre-certificates from the CA that have expired.
num_precerts_issued	bigint	>, >=, =, <, <=, !=	Number of pre-certificates issued by the CA.
sp_connection_name	text	=, !=, ~~, ~~, !~~, !~~	Steampipe connection name.
sp_ctx	jsonb		Steampipe context in JSON form.

Export

This table is available as a standalone Exporter CLI. Steampipe exporters are stand-alone binaries that allow you to extract data using Steampipe plugins without a database.

You can download the tarball for your platform from the Releases page, but it is simplest to install them with the steampipe_export_installer.sh script:

/bin/sh -c "$(curl -fsSL https://steampipe.io/install/export.sh)" -- crtsh

You can pass the configuration to the command with the --config argument:

steampipe_export_crtsh --config '<your_config>' crtsh_ca