Having recently completed a significant compliance audit (SOC 2 Type II), our team was tasked with demonstrating the efficacy of our cloud security controls over a six-month period. While Check Point CloudGuard provides excellent posture management and threat prevention, its native reporting, while functional, lacked the granularity and bespoke formatting required by our internal governance team. This post details the automated pipeline we built to transform CloudGuard findings into a dynamic, auditor-ready Power BI dashboard.
The core challenge was aggregating data from multiple CloudGuard components—primarily Posture Management (CSPM) and Workload Protection (CWPP)—into a single, timestamped fact table. We needed to track the lifecycle of a security finding (e.g., "S3 bucket is publicly accessible") from detection to remediation, correlating it with our internal Jira ticketing system. The workflow can be summarized as follows:
1. **Data Extraction:** We leveraged CloudGuard's APIs (`/web/api/v1.0/posture-management` and `/web/api/v1.0/workload-protection`) to pull findings on a scheduled basis. The JSON payloads are rich but require significant flattening.
2. **Orchestration & Transformation:** An Apache Airflow DAG (running in a container on AWS ECS) executes daily. It calls the APIs, performs necessary normalization (e.g., mapping CloudGuard's unique rule IDs to our internal control framework like CIS AWS Foundations), and stages the data in a Snowflake RAW schema.
3. **Data Modeling:** Within Snowflake, we apply incremental SCD Type 2 logic to maintain history. A key view exposes the current state of each finding, with columns for:
* `finding_id`, `resource_id`, `cloud_account`
* `severity_score`, `status` (`new`, `in_progress`, `resolved`, `suppressed`)
* `detection_timestamp`, `last_observed_timestamp`
* `related_jira_ticket`
4. **Visualization:** Power BI connects directly to Snowflake via a service principal. The dataset is refreshed daily post-ETL completion.
A simplified version of the critical transformation SQL for the findings fact table:
```sql
-- Incremental merge for posture findings
MERGE INTO prod.fact_cloudguard_findings t
USING (
SELECT
f.id::VARCHAR as finding_key,
f.rule.id::VARCHAR as rule_id,
f.asset.id::VARCHAR as resource_id,
f.severity,
f.status,
f.metadata->>'description' as description,
f.createdTime::TIMESTAMP_NTZ as detection_time,
CURRENT_TIMESTAMP() as observed_time,
COALESCE(f.remediationStatus, 'open') as remediation_status
FROM raw.posture_api_data f
WHERE f.ingestion_date = CURRENT_DATE()
) s ON t.finding_key = s.finding_key AND t.detection_time = s.detection_time
WHEN NOT MATCHED THEN
INSERT (finding_key, rule_id, resource_id, severity, status, description, detection_time, observed_time, remediation_status)
VALUES (s.finding_key, s.rule_id, s.resource_id, s.severity, s.status, s.description, s.detection_time, s.observed_time, s.remediation_status);
```
**Key Metrics & Outputs:**
The resulting dashboard provides real-time visibility into:
* **Mean Time to Remediation (MTTR):** Broken down by cloud service (EC2, S3, IAM) and severity.
* **Compliance Coverage:** Percentage of CIS controls actively monitored by CloudGuard rules, highlighting any gaps.
* **Resource Hygiene Trend:** A rolling 30-day view of net-new vs. resolved findings, proving continuous improvement.
* **Exception Management:** A detailed log of justified suppressions with mandatory approval tickets attached.
**Pitfalls & Lessons Learned:**
* **API Rate Limiting:** Initial naive polling triggered throttling. Implementing exponential backoff and caching of static lookup data (like rule definitions) was crucial.
* **State Management:** CloudGuard's `status` field doesn't always align with a resource actually being fixed; it may just indicate a scan no longer sees the issue. We had to correlate with AWS Config data to confirm remediation.
* **Cost:** While the API calls are included, the compute for processing large volumes of findings (especially in multi-account setups) and storing full history in Snowflake incurred non-trivial costs that must be budgeted.
This integration has moved our compliance reporting from a quarterly, manual, and error-prone exercise to a continuous, evidence-based process. The ability to provide auditors with direct, filtered access to the underlying Power BI dataset significantly reduced their questioning cycle. I'm interested to hear if others have built similar orchestration layers and how you've handled mapping findings to frameworks like NIST or PCI-DSS.
-- elliot
Data first, decisions later.
The API flattening step is often the most underestimated part of this. The nested JSON structure for a single finding can include asset metadata, rule details, and remediation steps all in one object. We found using a dedicated transformation layer (we used dbt with a custom Python script) was necessary to reliably produce a star schema.
Did you also encounter the challenge of incremental extraction? CloudGuard's API doesn't provide a clean 'changed_since' parameter for all endpoints, so we had to implement a stateful watermark based on the finding `created_time` and `status_update_time`. Without it, you're processing the entire dataset repeatedly.
Data is the only truth.
Great point about the nested JSON being a headache. We ended up using `json_normalize` from Pandas in our transformation layer to flatten those deeply nested objects, but it still needed a lot of manual column mapping.
The incremental extraction was the real blocker for us too. We used a similar timestamp watermark, but had to add logic to also track deletions or archived findings. Otherwise, our "total open findings" metric would be permanently skewed. Did you run into that?
Clean code, happy life