Interesting question. I have tried to figure this out. without creating a report that specifies one scan
at a time I am not sure how to do this effectively.
Try this SQL, but you will need to scope it to a site, tag, or asset group. If you scope it to a scan, then it will clip the first scanned date to the scan date.
SELECT da.ip_address,
da.host_name,
da.sites,
dos.description AS operating_system,
dv.title AS vulnerability_title,
trunc(dv.riskscore::numeric, 2) AS risk_score,
trunc(dv.cvss_score::numeric, 2) AS cvss_score,
dscan.finished AS scan_date,
dsol.summary AS solution_summary,
htmlToText(favi.proof) as proof,
favi.port,
dp.name as protocol,
trunc(fava.age_in_days::numeric, 2) AS age_in_days,
fava.first_discovered,
fava.most_recently_discovered
FROM fact_asset_vulnerability_finding favf
JOIN dim_scan dscan USING (scan_id)
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_asset_vulnerability_best_solution davbs USING (asset_id, vulnerability_id)
JOIN dim_solution dsol on davbs.solution_id = dsol.solution_id
JOIN fact_asset fa USING (asset_id)
JOIN fact_asset_vulnerability_instance favi
on favf.asset_id = favi.asset_id and fa.last_scan_id = favi.scan_id and
favi.vulnerability_id = favf.vulnerability_id
JOIN dim_protocol dp USING (protocol_id)
JOIN fact_asset_vulnerability_age fava
on da.asset_id = fava.asset_id and favf.vulnerability_id = fava.vulnerability_id
ORDER BY da.ip_address ASC,
dv.title ASC