turbot/prismacloud
steampipe plugin install prismacloud

Table: prismacloud_compliance_breakdown_statistic - Query Prisma Cloud compliance breakdown statistics using SQL

The Prisma Cloud compliance breakdown statistic table in Steampipe provides you with detailed information about the compliance status of resources within Prisma Cloud. This table allows security engineers and cloud administrators to query compliance-specific details, including the number of resources that have passed or failed compliance checks, categorized by severity levels. The schema outlines various attributes, such as account information, cloud type, compliance standard, and the number of resources with different compliance statuses.

Table Usage Guide

The prismacloud_compliance_breakdown_statistic table in Steampipe provides information about the compliance status of resources within Prisma Cloud. This table allows you to query details such as the number of resources that have passed or failed compliance checks, categorized by severity levels. This helps in managing and monitoring the compliance status of your cloud resources effectively.

Important Notes

  • For improved performance, it is recommended to use the optional qualifiers (quals) to limit the result set.
  • Queries with optional qualifiers are optimized to use filters. The following columns support optional qualifiers:
    • account_name
    • cloud_type
    • cloud_region
    • policy_compliance_standard_name
    • policy_compliance_requirement_name
    • policy_compliance_section_id

Examples

Basic info

Retrieve a basic summary of compliance breakdown, including the number of failed and passed resources.

select
account_name,
cloud_type,
failed_resources,
passed_resources,
total_resources
from
prismacloud_compliance_breakdown_statistic;
select
account_name,
cloud_type,
failed_resources,
passed_resources,
total_resources
from
prismacloud_compliance_breakdown_statistic;

List high severity failed resources

Retrieve the breakdown of high severity failed resources and order by the number of high severity failed resources. This helps in identifying the areas with the most critical compliance issues.

select
account_name,
cloud_type,
high_severity_failed_resources
from
prismacloud_compliance_breakdown_statistic
order by
high_severity_failed_resources desc;
select
account_name,
cloud_type,
high_severity_failed_resources
from
prismacloud_compliance_breakdown_statistic
order by
high_severity_failed_resources desc;

List breakdown statistics group by account and cloud type

Retrieve a summary of compliance breakdown grouped by account name and cloud type. This query helps you to understand the compliance status of resources across different accounts and cloud environments.

select
account_name,
cloud_type,
sum(critical_severity_failed_resources) as critical_failed_resources,
sum(high_severity_failed_resources) as high_failed_resources,
sum(medium_severity_failed_resources) as medium_failed_resources,
sum(low_severity_failed_resources) as low_failed_resources,
sum(informational_severity_failed_resources) as informational_failed_resources,
sum(passed_resources) as passed_resources,
sum(total_resources) as total_resources
from
prismacloud_compliance_breakdown_statistic
group by
account_name,
cloud_type;
select
account_name,
cloud_type,
sum(critical_severity_failed_resources) as critical_failed_resources,
sum(high_severity_failed_resources) as high_failed_resources,
sum(medium_severity_failed_resources) as medium_failed_resources,
sum(low_severity_failed_resources) as low_failed_resources,
sum(informational_severity_failed_resources) as informational_failed_resources,
sum(passed_resources) as passed_resources,
sum(total_resources) as total_resources
from
prismacloud_compliance_breakdown_statistic
group by
account_name,
cloud_type;

Get breakdown statistics for compliance standard and requirement

Retrieve detailed compliance breakdown statistics by joining with the compliance standard and requirement tables. This query helps you to get a comprehensive view of the compliance status of resources, including the associated compliance standards and requirements.

select
r.compliance_id as standard_id,
r.name as requirement_name,
b.account_name,
b.cloud_type,
b.failed_resources,
b.passed_resources,
b.total_resources
from
prismacloud_compliance_breakdown_statistic as b
join prismacloud_compliance_requirement as r on b.policy_compliance_requirement_name = r.name;
select
r.compliance_id as standard_id,
r.name as requirement_name,
b.account_name,
b.cloud_type,
b.failed_resources,
b.passed_resources,
b.total_resources
from
prismacloud_compliance_breakdown_statistic as b
join prismacloud_compliance_requirement as r on b.policy_compliance_requirement_name = r.name;

Recently updated compliance statistics

Retrieve compliance breakdown statistics that were updated within the last 30 days. This query helps in tracking recent changes and understanding the current compliance status.

select
account_name,
cloud_type,
timestamp,
failed_resources,
passed_resources,
total_resources
from
prismacloud_compliance_breakdown_statistic
where
timestamp > now() - interval '30 days';
select
account_name,
cloud_type,
timestamp,
failed_resources,
passed_resources,
total_resources
from
prismacloud_compliance_breakdown_statistic
where
timestamp > datetime('now', '-30 days');

Schema for prismacloud_compliance_breakdown_statistic

NameTypeOperatorsDescription
_ctxjsonbSteampipe context in JSON form.
account_idtextThe unique identifier for the account.
account_nametext=The unique identifier for the account.
assigned_policiesbigintNumber of policies assigned to the Compliance Standard/Requirement/Section.
cloud_regiontext=The region of the cloud where the resource is located.
cloud_typetext=The type of cloud (e.g., AWS, Azure, GCP).
critical_severity_failed_resourcesbigintNumber of Compliance Standard/Requirement/Section scanned resources failing critical severity policies.
defaultbooleanIndicates if it is a system default.
descriptiontextDescription of the Compliance Standard/Requirement/Section.
emailtext=, !=, ~~, ~~*, !~~, !~~*Email address of the current session user.
failed_resourcesbigintNumber of failing Compliance Standard/Requirement/Section scanned resources.
high_severity_failed_resourcesbigintNumber of Compliance Standard/Requirement/Section scanned resources failing high severity policies.
idtextID of the Compliance Standard/Requirement/Section.
informational_severity_failed_resourcesbigintNumber of Compliance Standard/Requirement/Section scanned resources failing informational severity policies.
low_severity_failed_resourcesbigintNumber of Compliance Standard/Requirement/Section scanned resources failing low severity policies.
medium_severity_failed_resourcesbigintNumber of Compliance Standard/Requirement/Section scanned resources failing medium severity policies.
nametextName of the Compliance Standard/Requirement/Section.
passed_resourcesbigintNumber of passing Compliance Standard/Requirement/Section scanned resources.
policy_compliance_requirement_nametext=The name of the compliance requirement associated with the policy.
policy_compliance_section_idtext=The ID of the compliance section associated with the policy.
policy_compliance_standard_nametext=The name of the compliance standard associated with the policy.
sp_connection_nametext=, !=, ~~, ~~*, !~~, !~~*Steampipe connection name.
sp_ctxjsonbSteampipe context in JSON form.
titletextTitle of the compliance posture.
total_resourcesbigintTotal number of Compliance Standard/Requirement/Section scanned resources.

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

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

steampipe_export_prismacloud --config '<your_config>' prismacloud_compliance_breakdown_statistic