steampipe plugin install oci

Table: oci_certificates_management_certificate - Query OCI Certificates Management Certificates using SQL

Oracle Cloud Infrastructure (OCI) Certificates Management is a service that helps in managing and controlling SSL/TLS certificates. It allows users to create, import, and manage SSL/TLS certificates for their cloud-based applications and resources. This service ensures that the certificates are valid, trusted, and can be used for secure connections.

Table Usage Guide

The oci_certificates_management_certificate table provides insights into the SSL/TLS certificates managed by OCI Certificates Management. As a security analyst, you can delve into certificate-specific details through this table, including certificate type, validity, and associated metadata. Utilize it to uncover information about certificates, such as their expiration dates, the resources they're associated with, and their overall status within your OCI environment.

Examples

Basic info

Discover the details of your certificates, including their lifecycle state and associated metadata. This is useful to understand the status and configuration of your certificates for better management and security compliance.

select
id,
name,
config_type,
issuer_certificate_authority_id,
description,
certificate_rules,
time_of_deletion,
lifecycle_details,
current_version,
subject,
certificate_revocation_list_details,
key_algorithm,
signature_algorithm,
certificate_profile_type,
lifecycle_state as state
from
oci_certificates_management_certificate;
select
id,
name,
config_type,
issuer_certificate_authority_id,
description,
certificate_rules,
time_of_deletion,
lifecycle_details,
current_version,
subject,
certificate_revocation_list_details,
key_algorithm,
signature_algorithm,
certificate_profile_type,
lifecycle_state as state
from
oci_certificates_management_certificate;

List imported certificates

Explore imported certificates to understand their lifecycle state, rules, and signature algorithm. This can be helpful in managing and understanding the security and validity of certificates in your environment.

select
name,
id,
certificate_rules,
lifecycle_state,
config_type,
signature_algorithm
from
oci_certificates_management_certificate
where
config_type = 'IMPORTED';
select
name,
id,
certificate_rules,
lifecycle_state,
config_type,
signature_algorithm
from
oci_certificates_management_certificate
where
config_type = 'IMPORTED';

List failed certificates

Discover the segments that have failed certificates to gain insights into potential security risks or issues in your system. This is useful for quickly identifying and addressing problematic areas, enhancing the overall security of your system.

select
name,
id,
current_version,
subject,
certificate_revocation_list_details,
key_algorithm
from
oci_certificates_management_certificate
where
lifecycle_state = 'FAILED';
select
name,
id,
current_version,
subject,
certificate_revocation_list_details,
key_algorithm
from
oci_certificates_management_certificate
where
lifecycle_state = 'FAILED';

List certificates created in the last 30 days

Discover the segments that have been certified within the last month. This is useful for tracking recent changes and ensuring system security.

select
name,
id,
current_version,
key_algorithm,
time_created,
time_of_deletion
from
oci_certificates_management_certificate
where
time_created >= now() - interval '30' day;
select
name,
id,
current_version,
key_algorithm,
time_created,
time_of_deletion
from
oci_certificates_management_certificate
where
time_created >= datetime('now', '-30 day');

Get the current version details of each certificate

Explore which certificates are currently in use and their respective details. This is useful for tracking certificate versions and their revocation status, which aids in maintaining secure connections.

select
name,
id,
current_version ->> 'certificateId' as certificate_id,
current_version ->> 'issuerCaVersionNumber' as issuer_ca_version_number,
current_version ->> 'revocationStatus' as revocation_status,
current_version ->> 'serialNumber' as serial_number,
current_version ->> 'stages' as stages
from
oci_certificates_management_certificate;
select
name,
id,
json_extract(current_version, '$.certificateId') as certificate_id,
json_extract(current_version, '$.issuerCaVersionNumber') as issuer_ca_version_number,
json_extract(current_version, '$.revocationStatus') as revocation_status,
json_extract(current_version, '$.serialNumber') as serial_number,
json_extract(current_version, '$.stages') as stages
from
oci_certificates_management_certificate;

Schema for oci_certificates_management_certificate

NameTypeOperatorsDescription
_ctxjsonbSteampipe context in JSON form.
certificate_profile_typetextThe name of the profile used to create the certificate.
certificate_revocation_list_detailsjsonbCertificate revocation details.
certificate_rulesjsonbA list of rules that control how the certificate is used and managed.
compartment_idtext=The OCID of the compartment in Tenant in which the resource is located.
config_typetextThe origin of the certificate.
current_versionjsonbDetails about the current version of the certificate.
defined_tagsjsonbDefined tags for this resource.
descriptiontextA brief description of the certificate.
freeform_tagsjsonbFree-form tags for this resource.
idtext=The OCID of the certificate.
issuer_certificate_authority_idtext=The OCID of the certificate authority (CA) that issued the certificate.
key_algorithmtextThe algorithm used to create key pairs.
lifecycle_detailstextAdditional information about the current lifecycle state of the certificate.
lifecycle_statetext=The current lifecycle state of the certificate.
nametext=A user-friendly name for the certificate.
signature_algorithmtextThe algorithm used to sign the public key certificate.
sp_connection_nametext=, !=, ~~, ~~*, !~~, !~~*Steampipe connection name.
sp_ctxjsonbSteampipe context in JSON form.
subjectjsonbCertificate subject informnation.
tagsjsonbA map of tags for the resource.
tenant_idtext=, !=, ~~, ~~*, !~~, !~~*The OCID of the Tenant in which the resource is located.
time_createdtimestamp with time zoneTime that the Certificate was created.
time_of_deletiontimestamp with time zoneAn optional property indicating when to delete the certificate version.
titletextTitle of the resource.

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