Policy - rule compliance

InsightVM SQL Queries
1

Hi,

I would like to have a report with the minimum information concerning the conformity of the policies with my assets.

with this request, I have just one problem. it displays several results on rule compliance.

Could you please help me ?

SELECT
ds.name,
dp.title,
da.ip_address,
da.host_name,
dos.description,
dp.category,
fapr.date_tested,
fap.rule_compliance
FROM fact_asset_policy_rule fapr
JOIN dim_policy dp on dp.policy_id = fapr.policy_id
JOIN fact_asset_policy fap on fap.asset_id = fapr.asset_id
JOIN dim_asset da on da.asset_id = fapr.asset_id
JOIN dim_site_asset dsa ON dsa.asset_id = da.asset_id
JOIN dim_site ds ON ds.site_id = dsa.site_id
JOIN dim_operating_system dos ON dos.operating_system_id = da.operating_system_id
GROUP BY ds.name, dp.title, da.ip_address, da.host_name, dos.description, dp.category, fapr.date_tested, fap.rule_compliance;

result example
|name|title|ip_address|host_name|description|category|date_tested|rule_compliance|
|_BR - Windows NonProd|CIS Microsoft Windows Server 2016 RTM (Release 1607) Level One - Member Server v1.2.0|ip_address|host_name|Microsoft Windows Server 2016 Standard Edition 1607|CIS|2020-10-26 00:41:04.444|0,173611111|
|_BR - Windows NonProd|CIS Microsoft Windows Server 2016 RTM (Release 1607) Level One - Member Server v1.2.0|ip_address|host_name|Microsoft Windows Server 2016 Standard Edition 1607|CIS|2020-10-26 00:41:04.444|0,186851211|
|_BR - Windows UAT-I|CIS Microsoft Windows Server 2016 RTM (Release 1607) Level One - Member Server v1.2.0|ip_address|host_name|Microsoft Windows Server 2016 Standard Edition 1607|CIS|2020-10-26 00:41:04.444|0,173611111|
|_BR - Windows UAT-I|CIS Microsoft Windows Server 2016 RTM (Release 1607) Level One - Member Server v1.2.0|ip_address|host_name|Microsoft Windows Server 2016 Standard Edition 1607|CIS|2020-10-26 00:41:04.444|0,186851211|
end

2

Hi @cyber_security,

Since it seems like you are just looking for overall policy compliance and not individual rule compliance (there’s no rule name listed), you should use the fact_asset_scan_policy table instead of the fact_asset_policy_rule table. This table provides the overall compliance for a rule and the date of the test. Something like this:

SELECT ds.name,
       dp.title,
       da.ip_address,
       da.host_name,
       dos.description,
       dp.category,
       fasp.date_tested,
       fasp.rule_compliance
FROM fact_asset_scan_policy fasp
         JOIN dim_policy dp ON dp.policy_id = fasp.policy_id
         JOIN dim_asset da ON da.asset_id = fasp.asset_id
         JOIN dim_site_asset dsa ON dsa.asset_id = da.asset_id
         JOIN dim_site ds ON ds.site_id = dsa.site_id
         JOIN dim_operating_system dos ON dos.operating_system_id = da.operating_system_id
GROUP BY ds.name, dp.title, da.ip_address, da.host_name, dos.description, dp.category, fasp.date_tested,
         fasp.rule_compliance

Hope that helps

3

Perfect ! it works very well
as usual you are the best :wink:

1 Like