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_statusfrom crtsh_logwhere is_activeorder by operator, name;
select id, operator, name, url, is_active, latest_update, latest_sth_timestamp, apple_inclusion_status, chrome_inclusion_statusfrom crtsh_logwhere is_activeorder 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_statusfrom crtsh_logwhere operator = 'Google'order by operator, name;
select id, operator, name, url, is_active, latest_update, apple_inclusion_status, chrome_inclusion_statusfrom crtsh_logwhere 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_statusfrom crtsh_logwhere chrome_inclusion_status = 'Usable';
select id, operator, name, url, is_active, latest_update, apple_inclusion_status, chrome_inclusion_statusfrom crtsh_logwhere 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_statusfrom crtsh_logwhere chrome_inclusion_status <> apple_inclusion_status;
select id, operator, name, url, is_active, latest_update, apple_inclusion_status, chrome_inclusion_statusfrom crtsh_logwhere 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_loggroup by chrome_inclusion_statusorder by count desc;
select chrome_inclusion_status, count(*)from crtsh_loggroup by chrome_inclusion_statusorder by count(*) desc;
Schema for crtsh_log
Name | Type | Operators | 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