Showcase: Custom report that our auditors finally approved without changes.

After years of submitting compliance and audit reports that inevitably triggered multiple rounds of revisions and clarification requests, my team has finally achieved a milestone: a single-pass approval on our quarterly PCI DSS audit. The key was moving away from LogRhythm's out-of-the-box report templates and constructing a custom, data-intensive report that directly addressed every auditor's checklist item with unambiguous evidence.

The primary failure of standard reports was their narrative-driven, log-centric format. Auditors, particularly for frameworks like PCI DSS, require a direct mapping from requirement to evidence. Our solution was to pre-process LogRhythm data into a structured fact table in our data warehouse (BigQuery), then generate a report that reads more like a verification matrix.

**Core Architecture:**
1. **LR-to-Warehouse Pipeline:** We use LogRhythm's REST API (via a scheduled Python extractor) to pull not just raw alarms, but also case data, task completion logs, and user activity events. This data is landed in BigQuery staging tables.
2. **Transformation Layer:** A series of SQL views map the raw log data to audit dimensions. For example, a view called `v_privileged_user_activity` joins authentication logs, user entity metadata, and alarm history to produce a clear timeline of admin actions.
3. **Report Generation:** The final report is not a PDF from LogRhythm Console. It's a SQL query output (exported to Sheets for sharing) that follows this exact structure:

```sql
WITH evidence_base AS (
SELECT
'PCI DSS 10.2.1' AS control_id,
'All Individual Access to Cardholder Data' AS control_description,
lr_originalLogMessage AS raw_event,
lr_entityName AS system,
lr_userId AS user_account,
lr_timestamp AS access_time,
-- Business logic to classify as compliant/non-compliant
CASE WHEN lr_eventType = 'AuthenticationSuccess' THEN 'COMPLIANT'
ELSE 'EXCEPTION_REVIEW'
END AS verification_status
FROM
`project.dataset.v_unified_access_logs`
WHERE
lr_timestamp BETWEEN @start_date AND @end_date
)
SELECT
control_id,
control_description,
COUNT(*) AS total_events,
COUNTIF(verification_status = 'COMPLIANT') AS compliant_events,
COUNTIF(verification_status = 'EXCEPTION_REVIEW') AS exceptions,
-- Provides a sample of evidence for spot-checking
ARRAY_AGG(STRUCT(system, user_account, access_time) LIMIT 5) AS sample_evidence
FROM
evidence_base
GROUP BY
1, 2
ORDER BY
control_id;
```

**Key Advantages Realized:**
* **Traceability:** Every aggregated number in the report can be drilled into via the underlying view, allowing auditors to perform sample-based testing directly against the source data if they wish.
* **Consistency:** The SQL logic is version-controlled (Git). The same logic used for last quarter's report is applied this quarter, eliminating variance from manual report building.
* **Efficiency:** The data pipeline is automated. The manual effort shifted from *assembling the report* to *maintaining and validating the data pipeline*, which is a more scalable engineering task.

The auditor's feedback was telling: "The evidentiary mapping is clear and testable." This outcome underscores a principle we've found true across SIEM platforms: their native reporting is optimized for operational, day-to-day use. For rigorous compliance frameworks, you often need to treat the SIEM as a data source within a larger, more controlled analytics ecosystem. The investment in building this pipeline has paid off not only in reduced audit friction but also in giving us far better observability into our own security control performance.

--DC