Oracle Cloud Infrastructure Documentation

Reference for Certificates Management

This guide lists the predefined objects in OCI Resource Analytics for the Certificates Management service. You can find information about views, entity relationships, subject areas, and sample queries.

Views

This section provides information about views within OCI Resource Analytics Certificates Management and their columns, data types, keys, and the referred view and column names. The following views are available:

Certificates Management Views
Name Description
CERTIFICATES_MANAGEMENT_ASSOCIATION_DIM_V This view stores information about the association between a certificates resource and another OCI resource.
CERTIFICATES_MANAGEMENT_CA_BUNDLE_DIM_V This view stores information on CA bundle metadata excluding certificate contents.
CERTIFICATES_MANAGEMENT_CERTIFICATE_DIM_V This view stores information on certificate metadata excluding the certificate contents.
CERTIFICATES_MANAGEMENT_CERTIFICATE_AUTHORITY_DIM_V This view stores information on certificate authority metadata excluding certificate contents.
CERTIFICATES_MANAGEMENT_CERTIFICATE_ASSOCIATION_FACT_V Fact table for capturing associations between certificate resources and OCI services.
CERTIFICATES_MANAGEMENT_CERTIFICATE_AUTHORITY_FACT_V Fact table for capturing certificate authority lifecycle and issuance trends.
CERTIFICATES_MANAGEMENT_CERTIFICATE_FACT_V Fact table for capturing certificate lifecycle and configuration snapshots.

The suffixes in the view names specify the view type:

  • FACT_V: Fact
  • DIM_V: Dimension

Relationship Diagram

This section provides diagrams that define the logical relationship of a fact table with different dimension tables.

The contents of each view and their relationships are listed in the following file: Certificates Management views.

CERTIFICATES_MANAGEMENT_CERTIFICATE_ASSOCIATION_FACT_V


Relationship diagram showing CERTIFICATES_MANAGEMENT_CERTIFICATE_ASSOCIATION_FACT_V and its related dimension tables.

CERTIFICATES_MANAGEMENT_CERTIFICATE_AUTHORITY_FACT_V


Relationship diagram showing CERTIFICATES_MANAGEMENT_CERTIFICATE_AUTHORITY_FACT_V and its related dimension tables.

CERTIFICATES_MANAGEMENT_CERTIFICATE_FACT_V


Relationship diagram showing CERTIFICATES_MANAGEMENT_CERTIFICATE_FACT_V and its related dimension tables.

Relationships exist among dimensions. Dimensions can be joined directly to each other. This diagram shows the relationship between dimension views.

CERTIFICATES_MANAGEMENT_CA_BUNDLE_DIM_V


Relationship diagram showing CERTIFICATES_MANAGEMENT_CA_BUNDLE_DIM_V and its relationship to COMPARTMENT_DIM_V.

Sample Queries

Sample queries for Certificates Management.

Show the number of associations for each certificate.

SELECT
    CERTIFICATE_ID,
    COUNT(ASSOCIATION_ID) AS ASSOCIATION_COUNT
FROM OCIRA.CERTIFICATES_MANAGEMENT_CERTIFICATE_ASSOCIATION_FACT_V 
GROUP BY CERTIFICATE_ID;

Show the total issued certificate count for each certificate authority.

SELECT
    CERTIFICATE_AUTHORITY_ID,
    SUM(ISSUED_CERTIFICATE_COUNT) AS TOTAL_ISSUED_CERTIFICATE_COUNT
FROM OCIRA.CERTIFICATES_MANAGEMENT_CERTIFICATE_AUTHORITY_FACT_V F
GROUP BY CERTIFICATE_AUTHORITY_ID;

Show the total certificate count by config type.

SELECT
    CONFIG_TYPE,
    SUM(CERTIFICATE_ID) AS TOTAL_CERTIFICATE_COUNT
FROM OCIRA.CERTIFICATES_MANAGEMENT_CERTIFICATE_FACT_V F
GROUP BY CONFIG_TYPE;