steampipe plugin install crtsh

Table: crtsh_log_entry - Query crt.sh Log Entries using SQL

crt.sh is a free public Certificate Transparency Log (CT Log) search engine provided by Sectigo. It allows users to search for SSL/TLS certificates by various criteria, including domain name, organization name, and many others. The service helps to enhance transparency and security in the use of SSL/TLS certificates.

Table Usage Guide

The crtsh_log_entry table provides insights into the Certificate Transparency Log (CT Log) entries in crt.sh. As a security analyst, explore entry-specific details through this table, including certificate details, log operator, and associated metadata. Utilize it to uncover information about the certificates, such as those issued by specific organizations, the CT logs they are included in, and the verification of the certificates' transparency.

Examples

Log entries for a particular certificate

Determine the log entries associated with a specific certificate to analyze its activity and troubleshoot potential issues.

select
*
from
crtsh_log_entry
where
certificate_id = 6760944046;
select
*
from
crtsh_log_entry
where
certificate_id = 6760944046;

Most recent entries for a given log

Analyze the most recent entries in a given log to monitor changes or unusual activity over the past hour. This can be particularly useful for identifying potential security issues or troubleshooting ongoing problems.

select
*
from
crtsh_log_entry
where
certificate_id = 6760944046
and ct_log_id = 91
and entry_timestamp > now() - interval '1 hr';
select
*
from
crtsh_log_entry
where
certificate_id = 6760944046
and ct_log_id = 91
and entry_timestamp > datetime('now', '-1 hour');

Schema for crtsh_log_entry

NameTypeOperatorsDescription
_ctxjsonbSteampipe context in JSON form, e.g. connection_name.
certificate_idbigint>, >=, =, <, <=, !=Certificate the entry represents.
ct_log_idbigint>, >=, =, <, <=, !=The log this entry is defined in.
entry_idbigint>, >=, =, <, <=, !=Unique ID of the entry.
entry_timestamptimestamp with time zone>, >=, =, <, <=, !=Timestamp of the entry.

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_entry