Hello,
I want to create a report that includes the following elements:
host_name,ip_address,OS Name, policy_title,rule_name,compliance_status,last_scan
Example of field values
|host_name|ip_address|OS Name|policy_title|rule_name|compliance_status|last_scan|
|servername|192.168.0.1|Microsoft Windows Server 2019 Standard Edition 1809|CIS Microsoft Windows Server 2019 Benchmark Level 1 - Domain Controller|2.2.11. (L1) Ensure ‘Change the system time’ is set to ‘Administrators, LOCAL SERVICE’|Failed|2024-09-09 04:22:44.163|
I used this query, but it’s not working; there is an issue with the joins.
SELECT
da.host_name,
da.ip_address,
dos.description as OS Name,
dp.title AS policy_title,
dpr.title AS rule_name,
CASE
WHEN fapr.compliance = TRUE THEN ‘Passed’
WHEN fapr.compliance = FALSE THEN ‘Failed’
ELSE ‘Not applicable’
END AS compliance_status,
da.last_assessed_for_vulnerabilities AS last_scan
FROM fact_asset_policy_rule fapr
JOIN dim_asset da ON fapr.asset_id = da.asset_id
JOIN dim_operating_system dos ON da.operating_system_id = dos.operating_system_id
JOIN dim_policy_rule dpr ON fapr.policy_id = dpr.policy_id AND fapr.rule_id = dpr.rule_id
JOIN dim_policy dp ON fapr.policy_id = dp.policy_id
WHERE dp.title LIKE ‘%CIS Microsoft Windows Server 2019 Level Two - Member Server v1.3.0’
AND da.last_assessed_for_vulnerabilities >= CURRENT_DATE - INTERVAL ‘30 days’;
Thank you