steampipe plugin install oci

Table: oci_certificates_management_certificate_authority_version - Query OCI Certificates Management Certificate Authority Versions using SQL

A Certificate Authority Version in OCI Certificates Management is a specific version of a certificate authority (CA). It contains the information about the CA, such as its public key and the associated private key. This CA version can be used to sign certificates or certificate revocation lists (CRLs).

Table Usage Guide

The oci_certificates_management_certificate_authority_version table provides insights into the versions of certificate authorities within OCI Certificates Management. As a security administrator, you can explore CA version-specific details through this table, including the public key, private key, and other associated metadata. Utilize it to uncover information about CA versions, such as their signing status, the associated CAs, and the validity of each version.

Examples

Basic info

Explore the versions of certificate authorities to understand their revocation status, validity, and deletion times. This can be useful for maintaining security compliance and tracking changes in your certificate authorities.

select
certificate_authority_id,
version_number,
stages,
serial_number,
issuer_ca_version_number,
version_name,
subject_alternative_names,
time_of_deletion,
validity,
revocation_status
from
oci_certificates_management_certificate_authority_version;
select
certificate_authority_id,
version_number,
stages,
serial_number,
issuer_ca_version_number,
version_name,
subject_alternative_names,
time_of_deletion,
validity,
revocation_status
from
oci_certificates_management_certificate_authority_version;

Get all certificate authority versions

Determine the versions of all certificate authorities to assess their validity, revocation status, and other key details. This is useful for maintaining the security of your network by ensuring all certificates are up-to-date and valid.

select
cmcav.certificate_authority_id,
cmcav.version_number,
cmcav.stages,
cmcav.serial_number,
cmcav.issuer_ca_version_number,
cmcav.version_name,
cmcav.subject_alternative_names,
cmcav.time_of_deletion,
cmcav.validity,
cmcav.revocation_status
from
oci_certificates_management_certificate_authority_version cmcav
inner join oci_certificates_management_certificate_authority cmca on cmca.id = cmcav.certificate_authority_id;
select
cmcav.certificate_authority_id,
cmcav.version_number,
cmcav.stages,
cmcav.serial_number,
cmcav.issuer_ca_version_number,
cmcav.version_name,
cmcav.subject_alternative_names,
cmcav.time_of_deletion,
cmcav.validity,
cmcav.revocation_status
from
oci_certificates_management_certificate_authority_version cmcav
inner join oci_certificates_management_certificate_authority cmca on cmca.id = cmcav.certificate_authority_id;

Count certificate versions by certificate authority

Analyze the distribution of certificate versions across different certificate authorities. This can be useful in identifying authorities that have a high number of versions, potentially indicating frequent changes or updates.

select
certificate_authority_id,
count(version_number)
from
oci_certificates_management_certificate_authority_version
group by
certificate_authority_id;
select
certificate_authority_id,
count(version_number)
from
oci_certificates_management_certificate_authority_version
group by
certificate_authority_id;

List certificate versions created in the last 30 days

Identify recent certificate versions made within the past month. This can be useful for tracking the creation and management of certificate authorities over time.

select
certificate_authority_id,
version_number,
serial_number,
issuer_ca_version_number,
version_name
from
oci_certificates_management_certificate_authority_version
where
time_created >= now() - interval '30' day;
select
certificate_authority_id,
version_number,
serial_number,
issuer_ca_version_number,
version_name
from
oci_certificates_management_certificate_authority_version
where
time_created >= datetime('now', '-30 day');

Schema for oci_certificates_management_certificate_authority_version

NameTypeOperatorsDescription
_ctxjsonbSteampipe context in JSON form.
certificate_authority_idtext=The OCID of the CA.
issuer_ca_version_numberbigintThe version number of the issuing CA.
revocation_statusjsonbRevocation details for the CA.
serial_numbertextA unique certificate identifier used in certificate revocation tracking, formatted as octets.
sp_connection_nametext=, !=, ~~, ~~*, !~~, !~~*Steampipe connection name.
sp_ctxjsonbSteampipe context in JSON form.
stagesjsonbA list of rotation states for this CA version.
subject_alternative_namesjsonbA list of subject alternative names. A subject alternative name specifies the domain names, including subdomains, and IP addresses covered by the certificates issued by this CA.
tenant_idtext=, !=, ~~, ~~*, !~~, !~~*The OCID of the Tenant in which the resource is located.
time_createdtimestamp with time zoneTime that the Certificate Authority Version was created.
time_of_deletiontimestamp with time zoneAn optional property indicating when to delete the CA version.
titletextTitle of the resource.
validityjsonbCertificate Authority validity details.
version_nametextThe name of the CA version. When the value is not null, a name is unique across versions for a given CA.
version_numberbigint=The version number of this CA.

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

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

steampipe_export_oci --config '<your_config>' oci_certificates_management_certificate_authority_version