SQL Query for vulnerabilities over X days since discovery

Hi everybody. I’m looking to create a report that would show me systems with vulnerabilities

  1. That are over 45 days since discovery
  2. Have a cvss score of 7 or higher.

Ideally it would be nice if it could be formatted in such a way that it would show the hostname and ip address of each system and then list the vulnerabilities on the system that meet the criteria above

I’m being asked to provide a report on this weekly and there really isn’t a good way to do this with the built in reports. Anyone have any experience doing something similar to this? Thanks

Here’s a query that pulls vulnerabilities with the criteria you mentioned, along with the IP + hostname of the affected assets. I think that date filter should work.

SELECT da.ip_address, da.host_name, dv.nexpose_id, dv.title AS "Vulnerabilty Title",
       dv.cvss_score, fava.first_discovered
FROM dim_asset da
JOIN fact_asset_vulnerability_finding favf
ON da.asset_id = favf.asset_id
JOIN fact_asset_vulnerability_age fava
ON favf.vulnerability_id = fava.vulnerability_id
JOIN dim_vulnerability dv
ON favf.vulnerability_id = dv.vulnerability_id
WHERE dv.cvss_score >= 7
AND NOW() - INTERVAL '45 Days' > fava.first_discovered
GROUP BY da.ip_address, da.host_name, dv.nexpose_id, dv.title, dv.cvss_score, 
         fava.first_discovered
ORDER BY first_discovered

The fact_asset_vulnerability_age table is where we’re getting the date of first discovery so we can pull vulns that were found 45+ days ago. I’m not sure which vulnerability details you’re looking to have in the report, but I added title, ID, and CVSS for now.

Holly,

Thanks for that bit of SQL. One other thing I’m being asked if we can add to that report is the vulnerability proof? Is that doable?

The vulnerability proof is stored in the fact_asset_vulnerability_instance table, so we can pull that in. I updated the query to add that:

SELECT da.ip_address, da.host_name, dv.nexpose_id, dv.title AS "Vulnerabilty Title",
       dv.cvss_score, fava.first_discovered, htmltotext(favi.proof) AS "Proof"
FROM dim_asset da
JOIN fact_asset_vulnerability_instance favi
ON da.asset_id = favi.asset_id
JOIN fact_asset_vulnerability_age fava
ON favi.vulnerability_id = fava.vulnerability_id
JOIN dim_vulnerability dv
ON favi.vulnerability_id = dv.vulnerability_id
WHERE dv.cvss_score >= 7
AND NOW() - INTERVAL '45 Days' > fava.first_discovered
GROUP BY da.ip_address, da.host_name, dv.nexpose_id, dv.title, dv.cvss_score,
         fava.first_discovered, favi.proof
ORDER BY fava.first_discovered

For reference, I used this doc to figure out which table to pull the proof from:

https://docs.rapid7.com/insightvm/understanding-the-reporting-data-model-facts