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

Name	Type	Operators	Description
_ctx	jsonb		Steampipe context in JSON form, e.g. connection_name.
certificate_id	bigint	>, >=, =, <, <=, !=	Certificate the entry represents.
ct_log_id	bigint	>, >=, =, <, <=, !=	The log this entry is defined in.
entry_id	bigint	>, >=, =, <, <=, !=	Unique ID of the entry.
entry_timestamp	timestamp 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