steampipe plugin install aws

Table: aws_acm_certificate - Query AWS Certificate Manager certificates using SQL

The AWS Certificate Manager (ACM) is a service that lets you easily provision, manage, and deploy public and private Secure Sockets Layer/Transport Layer Security (SSL/TLS) certificates for use with AWS services and your internal connected resources. SSL/TLS certificates are used to secure network communications and establish the identity of websites over the Internet as well as resources on private networks. AWS Certificate Manager removes the time-consuming manual process of purchasing, uploading, and renewing SSL/TLS certificates.

Table Usage Guide

The aws_acm_certificate table in Steampipe provides you with information about certificates within AWS Certificate Manager (ACM). This table allows you, as a DevOps engineer, to query certificate-specific details, including domain name, status, issuer, and expiration data. You can utilize this table to gather insights on certificates, such as certificate status, verification of issuer, and more. The schema outlines the various attributes of the ACM certificate for you, including the certificate ARN, creation date, domain name, and associated tags.

Examples

Basic info

Analyze the settings to understand the status and usage of your AWS Certificate Manager (ACM) certificates. This can help identify any issues with certificates, such as failure reasons, and see which domains they're associated with, aiding in efficient resource management and troubleshooting.

select
certificate_arn,
domain_name,
failure_reason,
in_use_by,
status,
key_algorithm
from
aws_acm_certificate;
select
certificate_arn,
domain_name,
failure_reason,
in_use_by,
status,
key_algorithm
from
aws_acm_certificate;

List of expired certificates

Identify instances where your AWS certificates have expired. This allows you to maintain security by promptly replacing or renewing these certificates.

select
certificate_arn,
domain_name,
status
from
aws_acm_certificate
where
status = 'EXPIRED';
select
certificate_arn,
domain_name,
status
from
aws_acm_certificate
where
status = 'EXPIRED';

List certificates for which transparency logging is disabled

Discover the segments with disabled transparency logging in certificate settings to enhance security and compliance efforts. This allows for proactive mitigation of potential risks associated with non-transparent logging.

select
certificate_arn,
domain_name,
status
from
aws_acm_certificate
where
certificate_transparency_logging_preference <> 'ENABLED';
select
certificate_arn,
domain_name,
status
from
aws_acm_certificate
where
certificate_transparency_logging_preference != 'ENABLED';

List certificates without application tag key

Identify the certificates that are missing an application tag key. This can help in pinpointing areas where tagging conventions may not have been followed, aiding in better resource management.

select
certificate_arn,
tags
from
aws_acm_certificate
where
not tags :: JSONB ? 'application';
select
certificate_arn,
tags
from
aws_acm_certificate
where
json_extract(tags, '$.application') is null;

Schema for aws_acm_certificate

NameTypeOperatorsDescription
_ctxjsonbSteampipe context in JSON form.
account_idtext=, !=, ~~, ~~*, !~~, !~~*The AWS Account ID in which the resource is located.
akasjsonbArray of globally unique identifier strings (also known as) for the resource.
certificatetextThe ACM-issued certificate corresponding to the ARN specified as input
certificate_arntext=Amazon Resource Name (ARN) of the certificate. This is of the form: arn:aws:acm:region:123456789012:certificate/12345678-1234-1234-1234-123456789012
certificate_chaintextThe ACM-issued certificate corresponding to the ARN specified as input
certificate_transparency_logging_preferencetextIndicates whether to opt in to or out of certificate transparency logging. Certificates that are not logged typically generate a browser error. Transparency makes it possible for you to detect SSL/TLS certificates that have been mistakenly or maliciously issued for your domain.
created_attimestamp with time zoneThe time at which the certificate was requested. This value exists only when the certificate type is AMAZON_ISSUED
domain_nametextFully qualified domain name (FQDN), such as www.example.com or example.com, for the certificate
domain_validation_optionsjsonbContains information about the initial validation of each domain name that occurs as a result of the RequestCertificate request. This field exists only when the certificate type is AMAZON_ISSUED
extended_key_usagesjsonbSpecify one or more ExtendedKeyUsage extension values.
failure_reasontextThe reason the certificate request failed. This value exists only when the certificate status is FAILED
imported_attimestamp with time zoneThe name of the certificate authority that issued and signed the certificate
in_use_byjsonbA list of ARNs for the AWS resources that are using the certificate
issued_attimestamp with time zoneA list of ARNs for the AWS resources that are using the certificate. A certificate can be used by multiple AWS resources
issuertextThe name of the certificate authority that issued and signed the certificate
key_algorithmtext=The algorithm that was used to generate the public-private key pair
not_aftertimestamp with time zoneThe time after which the certificate is not valid
not_beforetimestamp with time zoneThe time before which the certificate is not valid
partitiontextThe AWS partition in which the resource is located (aws, aws-cn, or aws-us-gov).
regiontextThe AWS Region in which the resource is located.
renewal_eligibilitytextSpecifies whether the certificate is eligible for renewal.
revocation_reasontextThe reason the certificate was revoked. This value exists only when the certificate status is REVOKED
revoked_attimestamp with time zoneThe time at which the certificate was revoked. This value exists only when the certificate status is REVOKED
serialtextThe serial number of the certificate
signature_algorithmtextThe algorithm that was used to sign the certificate
sp_connection_nametext=, !=, ~~, ~~*, !~~, !~~*Steampipe connection name.
sp_ctxjsonbSteampipe context in JSON form.
statustext=The status of the certificate
subjecttextThe name of the entity that is associated with the public key contained in the certificate
subject_alternative_namesjsonbOne or more domain names (subject alternative names) included in the certificate. This list contains the domain names that are bound to the public key that is contained in the certificate. The subject alternative names include the canonical domain name (CN) of the certificate and additional domain names that can be used to connect to the website
tagsjsonbA map of tags for the resource.
tags_srcjsonbA list of tags associated with certificate
titletextTitle of the resource.
typetextThe source of the certificate. For certificates provided by ACM, this value is AMAZON_ISSUED.

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

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

steampipe_export_aws --config '<your_config>' aws_acm_certificate