Table: urlscan_certificate - Query urlscan.io Certificates using SQL

urlscan.io is a service that allows you to scan and analyze web pages to identify security issues and malicious behavior. It provides detailed information about the components of the webpage, including the SSL certificates. These certificates are used to secure communication between the website and the user, and contain details such as the issuer, subject, validity period, and more.

Table Usage Guide

The urlscan_certificate table provides insights into SSL certificates associated with a scan in urlscan.io. As a security analyst, explore certificate-specific details through this table, including the issuer, subject, validity period, and associated metadata. Utilize it to uncover information about certificates, such as their validity, the organizations that issued them, and the organizations they were issued to.

Important Notes

You must specify the scan in the where clause to query this table.

Examples

List certificates found in the scan

Explore the validity of certificates identified in a specific scan. This query helps to track the expiration date of each certificate, allowing you to manage and update them before they expire.

select
  subject_name,
  issuer,
  valid_to,
  (valid_to :: date - current_date) as days_until_expiration
from
  urlscan_certificate
where
  scan = '54c78f69-5294-4a17-8ae0-a71943954e09'
order by
  valid_to;

select
  subject_name,
  issuer,
  valid_to,
  (julianday(valid_to) - julianday(date('now'))) as days_until_expiration
from
  urlscan_certificate
where
  scan = '54c78f69-5294-4a17-8ae0-a71943954e09'
order by
  valid_to;

Schema for urlscan_certificate

Name	Type	Operators	Description
_ctx	jsonb		Steampipe context in JSON form.
issuer	text		Issuer of the certificate.
scan	text	=	ID of the scan result.
sp_connection_name	text	=, !=, ~~, ~~, !~~, !~~	Steampipe connection name.
sp_ctx	jsonb		Steampipe context in JSON form.
subject_name	text		Subject (domain name) of the certificate.
valid_from	timestamp with time zone		Time the certificate is valid from.
valid_to	timestamp with time zone		Time the certificate is valid to.

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)" -- urlscan

You can pass the configuration to the command with the --config argument:

steampipe_export_urlscan --config '<your_config>' urlscan_certificate