SQL example - detailed policy report

This example query returns all Pass/Fails across all policies/benchmarks accessed during a scan.

sql
1
select da.ip_address, da.host_name, dos.name as OS, dos.version as OS_Version, dp.title as Policy_Title, dpr.title as Rule_Name, dpr.description as Rule_Description, dprs.description as Complaince_Status
2
from fact_asset_policy_rule as fpr
3
join dim_asset as da on fpr.asset_id = da.asset_id
4
join dim_operating_system as dos using (operating_system_id)
5
join dim_policy as dp on fpr.policy_id = dp.policy_id
6
join dim_policy_rule as dpr on fpr.rule_id = dpr.rule_id
7
join dim_policy_result_status as dprs on fpr.status_id = dprs.status_id

Alternative query

sql
1
select da.ip_address, da.host_name, dos.name as OS, dos.version as OS_Version, dp.title as Policy_Title, dpr.title as Rule_Name, dpr.description as Rule_Description, dprs.description as Complaince_Status
2
from fact_asset_policy_rule as fpr
3
join dim_asset as da on fpr.asset_id = da.asset_id
4
join dim_operating_system as dos using (operating_system_id)
5
join dim_policy as dp on fpr.policy_id = dp.policy_id
6
join dim_policy_rule as dpr on fpr.rule_id = dpr.rule_id
7
join dim_policy_result_status as dprs on fpr.status_id = dprs.status_id
8
WHERE dprs.description != 'Not applicable'
9
ORDER BY da.ip_address, dp.title