SELECT ds.name AS site, da.ip_address, da.host_name, da.mac_address, dv.title AS vulnerability_title, dv.severity, dos.description AS operating_system, dv.date_published, dv.date_added,
round(dv.cvss_score::numeric, 2) AS cvss_score, round(dv.riskscore::numeric, 0) AS risk, round (age_in_days::numeric, 0) as age_in_days
FROM fact_asset_vulnerability_finding favf
JOIN dim_asset da USING (asset_id)
JOIN dim_operating_system dos USING (operating_system_id)
JOIN dim_vulnerability dv USING (vulnerability_id)
JOIN dim_site_asset dsa USING (asset_id)
JOIN dim_site ds USING (site_id)
join fact_asset_vulnerability_age fava using (asset_id, vulnerability_id)
where dv.date_published < current_date - interval '90 days' and dv.cvss_score >= 7 and dos.description LIKE '%Microsoft Windows%' is true
ORDER BY da.ip_address ASC, dv.title ASC
for a set of assets with a specific tag, I ran the above SQL query. Also, I tried running the below query using the query builder and noticed that the results are different.
