Table: oci_adm_vulnerability_audit - Query OCI ADM Vulnerability Audits using SQL

Oracle Cloud Infrastructure's Application Deployment Manager (ADM) service provides a framework for managing and monitoring your applications. It includes a Vulnerability Audit feature, which checks for potential security vulnerabilities in your applications. This feature helps ensure that your applications are secure and comply with industry standards and best practices.

Table Usage Guide

The oci_adm_vulnerability_audit table provides insights into Vulnerability Audits within OCI's Application Deployment Manager (ADM). As a security analyst or DevOps engineer, explore audit-specific details through this table, including audit findings, severity, and associated metadata. Utilize it to uncover information about vulnerabilities, such as those with high severity, the remediation advice for vulnerabilities, and the verification of vulnerability fixes.

Examples

Basic info

Explore the extent of vulnerabilities in your system by examining the maximum observed CVSS scores and the count of vulnerable artifacts. This query offers a comprehensive view of potential security risks, helping prioritize areas for remediation and enhancing overall system security.

select
  id,
  knowledge_base_id,
  vulnerabilities,
  max_observed_cvss_v2_score,
  max_observed_cvss_v3_score,
  max_observed_cvss_v2_score_with_ignored,
  max_observed_cvss_v3_score_with_ignored,
  vulnerable_artifacts_count_with_ignored,
  vulnerable_artifacts_count,
  build_type,
  display_name,
  is_success,
  lifecycle_state as state
from
  oci_adm_vulnerability_audit;

select
  id,
  knowledge_base_id,
  vulnerabilities,
  max_observed_cvss_v2_score,
  max_observed_cvss_v3_score,
  max_observed_cvss_v2_score_with_ignored,
  max_observed_cvss_v3_score_with_ignored,
  vulnerable_artifacts_count_with_ignored,
  vulnerable_artifacts_count,
  build_type,
  display_name,
  is_success,
  lifecycle_state as state
from
  oci_adm_vulnerability_audit;

List vulnerability audits which are not active

Determine the areas in which vulnerability audits are no longer active. This query can be used to identify potential security risks and gaps in your system's defense.

select
  id,
  knowledge_base_id,
  vulnerabilities,
  max_observed_cvss_v2_score,
  max_observed_cvss_v3_score,
  max_observed_cvss_v2_score_with_ignored,
  max_observed_cvss_v3_score_with_ignored,
  vulnerable_artifacts_count_with_ignored,
  vulnerable_artifacts_count,
  build_type,
  display_name,
  is_success,
  lifecycle_state as state
from
  oci_adm_vulnerability_audit
where
  lifecycle_state <> 'ACTIVE';

select
  id,
  knowledge_base_id,
  vulnerabilities,
  max_observed_cvss_v2_score,
  max_observed_cvss_v3_score,
  max_observed_cvss_v2_score_with_ignored,
  max_observed_cvss_v3_score_with_ignored,
  vulnerable_artifacts_count_with_ignored,
  vulnerable_artifacts_count,
  build_type,
  display_name,
  is_success,
  lifecycle_state as state
from
  oci_adm_vulnerability_audit
where
  lifecycle_state != 'ACTIVE';

List vulnerability audits created in last 30 days

Discover the segments that have been audited for vulnerabilities in the past month. This is useful for identifying potential security risks and ensuring your system's defenses are up to date.

select
  id,
  knowledge_base_id,
  vulnerabilities,
  max_observed_cvss_v2_score,
  max_observed_cvss_v3_score,
  max_observed_cvss_v2_score_with_ignored,
  max_observed_cvss_v3_score_with_ignored,
  vulnerable_artifacts_count_with_ignored,
  vulnerable_artifacts_count,
  build_type,
  display_name,
  is_success,
  lifecycle_state as state
from
  oci_adm_vulnerability_audit
where
  time_created >= now() - interval '30' day;

select
  id,
  knowledge_base_id,
  vulnerabilities,
  max_observed_cvss_v2_score,
  max_observed_cvss_v3_score,
  max_observed_cvss_v2_score_with_ignored,
  max_observed_cvss_v3_score_with_ignored,
  vulnerable_artifacts_count_with_ignored,
  vulnerable_artifacts_count,
  build_type,
  display_name,
  is_success,
  lifecycle_state as state
from
  oci_adm_vulnerability_audit
where
  time_created >= datetime('now', '-30 day');

List successful vulnerability audits

Explore which vulnerability audits were successful. This can help you assess the effectiveness of your security measures and identify areas for improvement.

select
  id,
  knowledge_base_id,
  vulnerabilities,
  max_observed_cvss_v2_score,
  max_observed_cvss_v3_score,
  max_observed_cvss_v2_score_with_ignored,
  max_observed_cvss_v3_score_with_ignored,
  vulnerable_artifacts_count_with_ignored,
  vulnerable_artifacts_count,
  build_type,
  display_name,
  is_success,
  lifecycle_state as state
from
  oci_adm_vulnerability_audit
where
  is_success;

select
  id,
  knowledge_base_id,
  vulnerabilities,
  max_observed_cvss_v2_score,
  max_observed_cvss_v3_score,
  max_observed_cvss_v2_score_with_ignored,
  max_observed_cvss_v3_score_with_ignored,
  vulnerable_artifacts_count_with_ignored,
  vulnerable_artifacts_count,
  build_type,
  display_name,
  is_success,
  lifecycle_state as state
from
  oci_adm_vulnerability_audit
where
  is_success = 1;

List vulnerability audits with unknown source

Discover the segments that have vulnerability audits with an unknown source. This is useful to identify potential security risks and take appropriate measures in a timely manner.

select
  id,
  knowledge_base_id,
  vulnerabilities,
  source ->> 'type' as source_type,
  vulnerable_artifacts_count,
  build_type,
  display_name,
  is_success,
  lifecycle_state as state
from
  oci_adm_vulnerability_audit
where
  source ->> 'type' = 'UNKNOWN';

select
  id,
  knowledge_base_id,
  vulnerabilities,
  json_extract(source, '$.type') as source_type,
  vulnerable_artifacts_count,
  build_type,
  display_name,
  is_success,
  lifecycle_state as state
from
  oci_adm_vulnerability_audit
where
  json_extract(source, '$.type') = 'UNKNOWN';

List vulnerable events that occurred over the last five minutes

Identify recent events that have potential vulnerabilities. This helps in immediate detection and remediation of potential security risks.

select
  id,
  knowledge_base_id,
  vulnerabilities,
  max_observed_cvss_v2_score,
  max_observed_cvss_v3_score,
  max_observed_cvss_v2_score_with_ignored,
  max_observed_cvss_v3_score_with_ignored,
  vulnerable_artifacts_count_with_ignored,
  vulnerable_artifacts_count,
  build_type,
  display_name,
  is_success,
  lifecycle_state as state
from
  oci_adm_vulnerability_audit
where
  time_created >= now() - interval '5 minutes'
  and vulnerabilities is not null;

select
  id,
  knowledge_base_id,
  vulnerabilities,
  max_observed_cvss_v2_score,
  max_observed_cvss_v3_score,
  max_observed_cvss_v2_score_with_ignored,
  max_observed_cvss_v3_score_with_ignored,
  vulnerable_artifacts_count_with_ignored,
  vulnerable_artifacts_count,
  build_type,
  display_name,
  is_success,
  lifecycle_state as state
from
  oci_adm_vulnerability_audit
where
  time_created >= datetime('now', '-5 minutes')
  and vulnerabilities is not null;

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_adm_vulnerability_audit