steampipe plugin install netsteampipe plugin install net

Table: net_certificate - Query Net Certificates using SQL

A Net Certificate is a digital document that verifies a server's details. When a browser initiates a connection with a secure website, the web server sends its public key certificate for the browser to check. This document contains the server's public key, the certificate's validity dates, and an identifier for the certificate authority (CA) that issued the certificate.

Table Usage Guide

The net_certificate table provides insights into Net Certificates. As a Security Analyst, explore certificate-specific details through this table, including issuer, subject, validity, and associated metadata. Utilize it to uncover information about certificates, such as their validity status, issuer details, and the verification of the certificate authority.

Important Notes

  • You must specify the address column of the format address:port (e.g., steamipe.io:443) in the where clause to query this table.

Examples

Basic info

Analyze the settings to understand the security certificates associated with a specific web address, such as 'steampipe.io:443'. This can be useful for assessing the security status and identifying any potential issues or vulnerabilities.

select
*
from
net_certificate
where
address = 'steampipe.io:443';
select
*
from
net_certificate
where
address = 'steampipe.io:443';

Get time until the certificate expires

Determine the remaining validity period of a specific certificate. This query is useful in monitoring and ensuring that the certificate does not expire unexpectedly, thereby preventing potential service interruptions.

select
address,
age(not_after, current_timestamp) as time_until_expiration
from
net_certificate
where
address = 'steampipe.io:443';
select
address,
julianday(not_after) - julianday(current_timestamp) as time_until_expiration
from
net_certificate
where
address = 'steampipe.io:443';

Check if the certificate is currently valid

Explore which security certificates are currently valid by assessing their validity periods. This is particularly useful to ensure your connections to certain addresses, like 'steampipe.io:443', are secure and up-to-date.

select
address,
not_before,
not_after
from
net_certificate
where
address = 'steampipe.io:443'
and not_before < current_timestamp
and not_after > current_timestamp;
select
address,
not_before,
not_after
from
net_certificate
where
address = 'steampipe.io:443'
and not_before < datetime('now')
and not_after > datetime('now');

Check if the certificate was revoked by the CA

Determine if a specific website's security certificate has been revoked by the certificate authority. This is useful for understanding the security status of your web connections.

select
address,
not_before,
not_after
from
net_certificate
where
address = 'steampipe.io:443'
and revoked;
select
address,
not_before,
not_after
from
net_certificate
where
address = 'steampipe.io:443'
and revoked;

Check certificate revocation status with OCSP

Determine the revocation status of a certificate using Online Certificate Status Protocol (OCSP). This can help in identifying if a certificate has been revoked, and if so, when it happened, which is crucial for maintaining secure online connections.

select
address,
ocsp ->> 'status' as revocation_status,
ocsp ->> 'revoked_at' as revoked_at
from
net_certificate
where
address = 'steampipe.io:443';
select
address,
json_extract(ocsp, '$.status') as revocation_status,
json_extract(ocsp, '$.revoked_at') as revoked_at
from
net_certificate
where
address = 'steampipe.io:443';

Check if certificate using insecure algorithm (e.g., MD2, MD5, SHA1)

Explore which digital certificates are using insecure algorithms, such as MD2, MD5, or SHA1. This query is beneficial for identifying potential security risks associated with outdated or weak cryptographic algorithms.

select
address,
not_before,
not_after,
signature_algorithm
from
net_certificate
where
address = 'steampipe.io:443'
and signature_algorithm like any (array [ '%SHA1%', '%MD2%', '%MD5%' ]);
select
address,
not_before,
not_after,
signature_algorithm
from
net_certificate
where
address = 'steampipe.io:443'
and (
signature_algorithm like '%SHA1%'
or signature_algorithm like '%MD2%'
or signature_algorithm like '%MD5%'
);

Query examples

Control examples

Schema for net_certificate

NameTypeOperatorsDescription
_ctxjsonbSteampipe context in JSON form, e.g. connection_name.
addresstext=Address to connect to, as specified in https://golang.org/pkg/net/#Dial.
chainjsonbCertificate chain.
common_nametextCommon name for the certificate.
countrytextCountry for the certificate.
crl_distribution_pointsjsonbA CRL distribution point (CDP) is a location on an LDAP directory server or Web server where a CA publishes CRLs.
dns_namesjsonbDNS names for the certificate.
domaintext=[DEPRECATED] This column has been deprecated and will be removed in a future release, use address instead. Domain name the certificate represents.
email_addressesjsonbEmail addresses for the certificate.
ip_addressinetIP address associated with the domain.
ip_addressesjsonbArray of IP addresses associated with the domain.
is_cabooleanTrue if the certificate represents a certificate authority.
issuertextIssuer of the certificate.
issuer_nametextCommon name for the issuer of the certificate.
issuing_certificate_urljsonbList of URLs of the issuing certificates.
localitytextLocality of the certificate.
not_aftertimestamp with time zoneTime when the certificate expires. Also see not_before.
not_beforetimestamp with time zoneTime when the certificate is valid from. Also see not_after.
ocspjsonbDescribes OCSP revocation status of the certificate.
ocsp_serversjsonbA list of OCSP URLs that are contacted by all end entity certificates to determine revocation status.
organizationtextOrganization of the certificate.
oujsonbOrganizational Unit of the certificate.
public_key_algorithmtextPublic key algorithm used by the certificate.
public_key_lengthbigintSpecifies the size of the key.
revokedbooleanTrue if the certificate was revoked.
serial_numbertextSerial number of the certificate.
signature_algorithmtextSignature algorithm of the certificate.
statetextState of the certificate.
subjecttextSubject of the certificate.
transparentbooleanTrue if the certificate is visible in certificate transparency logs.

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

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

steampipe_export_net --config '<your_config>' net_certificate