Skip to content
AWS for Query.Farm

aws.acm.list_certificates

Example SQL Queries

SELECT * FROM
aws.acm.list_certificates;

Description

Retrieves a list of certificate ARNs and domain names. By default, the API returns RSA_2048 certificates. To return all certificates in the account, include the keyType filter with the values [RSA_1024, RSA_2048, RSA_3072, RSA_4096, EC_prime256v1, EC_secp384r1, EC_secp521r1].

In addition to keyType, you can also filter by the CertificateStatuses, keyUsage, and extendedKeyUsage attributes on the certificate. For more information, see Filters.

Table Definition

Column NameColumn Data Type
certificate_statuses Input Column

Filter the certificate list by status value.

VARCHAR[]
Show child fields
certificate_statuses[]
includes Input Column

Filter the certificate list. For more information, see the Filters structure.

STRUCT(
    "extended_key_usage" VARCHAR[],
    "key_usage" VARCHAR[],
    "key_types" VARCHAR[]
)
Show child fields
includes.extended_key_usage[]
includes.key_types[]
includes.key_usage[]
sort_by Input Column

Specifies the field to sort results by. If you specify SortBy, you must also specify SortOrder.

VARCHAR
sort_order Input Column

Specifies the order of sorted results. If you specify SortOrder, you must also specify SortBy.

VARCHAR
_aws_profile Input Column

The AWS profile defines the AWS identity used. It can be defined via credentials or by assuming a IAM role.

STRUCT(
    "type" VARCHAR,
    "name" VARCHAR,
    "account_id" VARCHAR,
    "via_profile_name" VARCHAR,
    "assumed_role_arn" VARCHAR,
    "organization" STRUCT(
        "account_name" VARCHAR,
        "id" VARCHAR,
        "tags" STRUCT(
            "key" VARCHAR,
            "value" VARCHAR
        )[],
        "master_account" STRUCT(
            "id" VARCHAR,
            "email" VARCHAR
        ),
        "parents" STRUCT(
            "type" VARCHAR,
            "id" VARCHAR,
            "name" VARCHAR,
            "tags" STRUCT(
                "key" VARCHAR,
                "value" VARCHAR
            )[]
        )[]
    )
)
Show child fields
_aws_profile.account_id

The AWS account id

_aws_profile.assumed_role_arn

The ARN of the assumed role

_aws_profile.name

The unique name of the profile.

_aws_profile.organization

Information about this profile's membership in the AWS organization.

Show child fields
_aws_profile.organization.account_name

The name of account speciifed by the organization

_aws_profile.organization.id

The organization id

_aws_profile.organization.master_account
Show child fields
_aws_profile.organization.master_account.email

The organization master account email address

_aws_profile.organization.master_account.id

The organization master account id

_aws_profile.organization.parents[]
Show child fields
_aws_profile.organization.parents[].id

The id of the parent

_aws_profile.organization.parents[].name

The name of the parent

_aws_profile.organization.parents[].tags[]
Show child fields
_aws_profile.organization.parents[].tags[].key
_aws_profile.organization.parents[].tags[].value
_aws_profile.organization.parents[].type

The type of parent can be an organization unit or a root

_aws_profile.organization.tags[]
Show child fields
_aws_profile.organization.tags[].key
_aws_profile.organization.tags[].value
_aws_profile.type

The type of profile, either 'credentials' or 'assumed_role'

_aws_profile.via_profile_name

This IAM role for this profile is assumed by first utilizing another profile with this name to obtain credentials.

_aws_region Input Column

The AWS region to use.

VARCHAR
certificate_arn

Amazon Resource Name (ARN) of the certificate. This is of the form:

arn:aws:acm:region:123456789012:certificate/12345678-1234-1234-1234-123456789012

For more information about ARNs, see Amazon Resource Names (ARNs).

VARCHAR
created_at

The time at which the certificate was requested.

TIMESTAMP_S
domain_name

Fully qualified domain name (FQDN), such as www.example.com or example.com, for the certificate.

VARCHAR
exported

Indicates whether the certificate has been exported. This value exists only when the certificate type is PRIVATE.

BOOLEAN
extended_key_usages

Contains a list of Extended Key Usage X.509 v3 extension objects. Each object specifies a purpose for which the certificate public key can be used and consists of a name and an object identifier (OID). 

VARCHAR[]
Show child fields
extended_key_usages[]
has_additional_subject_alternative_names

When called by ListCertificates, indicates whether the full list of subject alternative names has been included in the response. If false, the response includes all of the subject alternative names included in the certificate. If true, the response only includes the first 100 subject alternative names included in the certificate. To display the full list of subject alternative names, use DescribeCertificate.

BOOLEAN
imported_at

The date and time when the certificate was imported. This value exists only when the certificate type is IMPORTED. 

TIMESTAMP_S
in_use

Indicates whether the certificate is currently in use by any Amazon Web Services resources.

BOOLEAN
issued_at

The time at which the certificate was issued. This value exists only when the certificate type is AMAZON_ISSUED. 

TIMESTAMP_S
key_algorithm

The algorithm that was used to generate the public-private key pair.

VARCHAR
key_usages

A list of Key Usage X.509 v3 extension objects. Each object is a string value that identifies the purpose of the public key contained in the certificate. Possible extension values include DIGITAL_SIGNATURE, KEY_ENCHIPHERMENT, NON_REPUDIATION, and more.

VARCHAR[]
Show child fields
key_usages[]
not_after

The time after which the certificate is not valid.

TIMESTAMP_S
not_before

The time before which the certificate is not valid.

TIMESTAMP_S
renewal_eligibility

Specifies whether the certificate is eligible for renewal. At this time, only exported private certificates can be renewed with the RenewCertificate command.

VARCHAR
revoked_at

The time at which the certificate was revoked. This value exists only when the certificate status is REVOKED. 

TIMESTAMP_S
status

The status of the certificate.

A certificate enters status PENDING_VALIDATION upon being requested, unless it fails for any of the reasons given in the troubleshooting topic Certificate request fails. ACM makes repeated attempts to validate a certificate for 72 hours and then times out. If a certificate shows status FAILED or VALIDATION_TIMED_OUT, delete the request, correct the issue with DNS validation or Email validation, and try again. If validation succeeds, the certificate enters status ISSUED. 

VARCHAR
subject_alternative_name_summaries

One 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.

When called by ListCertificates, this parameter will only return the first 100 subject alternative names included in the certificate. To display the full list of subject alternative names, use DescribeCertificate.

VARCHAR[]
Show child fields
subject_alternative_name_summaries[]
type

The source of the certificate. For certificates provided by ACM, this value is AMAZON_ISSUED. For certificates that you imported with ImportCertificate, this value is IMPORTED. ACM does not provide managed renewal for imported certificates. For more information about the differences between certificates that you import and those that ACM provides, see Importing Certificates in the Certificate Manager User Guide. 

VARCHAR