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	Operators	Description
_ctx	jsonb		Steampipe context in JSON form.
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.
sp_connection_name	text	=, !=, ~~, ~~, !~~, !~~	Steampipe connection name.
sp_ctx	jsonb		Steampipe context in JSON form.
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