Table: crtsh_log - Query crt.sh Certificate Logs using SQL

crt.sh is a free Certificate Transparency Log (CTL) search engine provided by Sectigo. It allows users to search for, and retrieve, details about SSL certificates that have been issued by all Certificate Authorities participating in the CTL. This includes information about the validity of certificates, issuer details, and the domains associated with each certificate.

Table Usage Guide

The crtsh_log table provides insights into SSL certificates logged in the crt.sh Certificate Transparency Log. As a security analyst or systems administrator, explore certificate-specific details through this table, including issuer data, associated domains, and certificate validity. Utilize it to uncover information about certificates, such as those issued by specific Certificate Authorities, the domains they cover, and their validity periods.

Examples

Active log operators

Explore which log operators are currently active, helping you understand the latest updates and inclusion status in both Apple and Chrome. This can assist in identifying potential issues or changes in their status that may require attention.

select
  id,
  operator,
  name,
  url,
  is_active,
  latest_update,
  latest_sth_timestamp,
  apple_inclusion_status,
  chrome_inclusion_status
from
  crtsh_log
where
  is_active
order by
  operator,
  name;

select
  id,
  operator,
  name,
  url,
  is_active,
  latest_update,
  latest_sth_timestamp,
  apple_inclusion_status,
  chrome_inclusion_status
from
  crtsh_log
where
  is_active
order by
  operator,
  name;

Logs run by Google

Explore the logs operated by Google to gain insights into their activity status, update frequency, and inclusion status in Apple and Chrome. This helps in monitoring and assessing the performance and reach of these logs.

select
  id,
  operator,
  name,
  url,
  is_active,
  latest_update,
  apple_inclusion_status,
  chrome_inclusion_status
from
  crtsh_log
where
  operator = 'Google'
order by
  operator,
  name;

select
  id,
  operator,
  name,
  url,
  is_active,
  latest_update,
  apple_inclusion_status,
  chrome_inclusion_status
from
  crtsh_log
where
  operator = 'Google'
order by
  operator,
  name;

Log operators included in Chrome

Explore which log operators are currently usable in Chrome to ensure you're working with updated and active resources. This can be particularly useful in maintaining secure and efficient operations.

select
  id,
  operator,
  name,
  url,
  is_active,
  latest_update,
  apple_inclusion_status,
  chrome_inclusion_status
from
  crtsh_log
where
  chrome_inclusion_status = 'Usable';

select
  id,
  operator,
  name,
  url,
  is_active,
  latest_update,
  apple_inclusion_status,
  chrome_inclusion_status
from
  crtsh_log
where
  chrome_inclusion_status = 'Usable';

Log operators with a different inclusion status in Apple and Chrome

Analyze the settings to understand discrepancies between the inclusion statuses of log operators in Apple and Chrome. This query is useful for identifying inconsistencies in the status of the same operator across different platforms.

select
  id,
  operator,
  name,
  url,
  is_active,
  latest_update,
  apple_inclusion_status,
  chrome_inclusion_status
from
  crtsh_log
where
  chrome_inclusion_status <> apple_inclusion_status;

select
  id,
  operator,
  name,
  url,
  is_active,
  latest_update,
  apple_inclusion_status,
  chrome_inclusion_status
from
  crtsh_log
where
  chrome_inclusion_status <> apple_inclusion_status;

Log operators by Chrome inclusion status

Assess the distribution of log operators based on their inclusion status in Chrome. This is useful for understanding the prevalence of different inclusion statuses within your logs, which can inform security and compliance efforts.

select
  chrome_inclusion_status,
  count(*)
from
  crtsh_log
group by
  chrome_inclusion_status
order by
  count desc;

select
  chrome_inclusion_status,
  count(*)
from
  crtsh_log
group by
  chrome_inclusion_status
order by
  count(*) desc;

Schema for crtsh_log

Name	Type	Description
_ctx	jsonb	Steampipe context in JSON form, e.g. connection_name.
apple_inclusion_status	text	Status of this log with Apple.
apple_last_status_change	timestamp with time zone	Timestamp when the status of this log last changed with Apple.
batch_size	bigint	Batch size of the log.
chrome_disqualified_at	timestamp with time zone	Timestamp when Google Chrome disqualified the log.
chrome_final_tree_size	bigint	Final tree size of the log according to Google Chrome.
chrome_inclusion_status	text	Status of this log in Google Chrome.
chrome_issue_number	bigint	Issue number discussing inclusion of the log in Google Chrome.
chrome_version_added	bigint	Version when the log was included in Google Chrome, if any.
chunk_size	bigint	Chunk size of the log.
google_uptime	text	Uptime percentage of the log according to Google.
id	bigint	ID of the log.
is_active	boolean	True if the log is active.
latest_sth_timestamp	timestamp with time zone	Latest Signed Tree Head (STH) timestamp of the log.
latest_update	timestamp with time zone	Latest time when the log was contacted by crt.sh.
mmd_in_seconds	bigint	Maximum Merge Delay of the log.
name	text	Name of the log.
operator	text	Operator of the log.
public_key	text	Public key of the log.
tree_size	bigint	Tree size is the total number of nodes in the merkle tree for the log.
url	text	URL of the log.

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_log