Report via SQL Query to view all asset policy scan result with history

InsightVM SQL Queries
1

I want to generate a report listing the policy scan history of all my machines, but with the query below I only have the last scan. I need help

SELECT ds.name,
dp.title,
da.ip_address,
da.host_name,
dos.description,
dp.category,
fasp.date_tested,
REPLACE(TO_CHAR(fasp.rule_compliance * 100, ‘FM99999990D00’), ‘.’, ‘,’) AS rule_compliance_percentage
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
WHERE dp.title = ‘BR - CIS Microsoft Windows 10 MWP 20H2 Level One (L1) + BitLocker (BL) v1.10.1’
AND da.host_name = ‘test’
AND fasp.date_tested >= current_date - interval ‘1 month’
GROUP BY ds.name, dp.title, da.ip_address, da.host_name, dos.description, dp.category, fasp.date_tested, fasp.rule_compliance
ORDER BY fasp.date_tested ASC;

Output

name title ip_address host_name description category date_tested rule_compliance_percentage
_BR - WSR99 BR - CIS Microsoft Windows 10 MWP 20H2 Level One (L1) + BitLocker (BL) v1.10.1 IP test Microsoft Windows 10 22H2 Custom Policies 2023-06-17 12:41:57.529 0,00
2

You would want to add in fact_asset_policy_date to the query

3

Thank you very much.

But is it possible to modify my query with your comment please ? I don’t understand where I have to add the fact_asset_policy_date.

thank you