Vulnerability Burndown Chart

I’m looking to build a burndown chart to show the date when a vulnerability is identified then remediated per asset. I’m able to achieve this by using the following tables:

  • dim_asset
  • fact_asset_vulnerability_finding_date
  • fact_asset_vulnerability_remediation_date

The issue I’m encountering is fact_asset_vulnerability_finding_date in our system is 50 million rows as of writing. The fact_asset_vulnerability_finding table only shows active vulnerabilities, not remediated ones. To provide a view with the asset_id, vulnerability_id, identified date, and remediated date is extremely taxing for even a single resource.

Is there a recommendation on how to manage this? Does the date of identification exist in any other table for remediated vulnerabilities?


I’m looking for the same thing. I think it comes down to getting the date field (first_found_date) from the fact_asset_vulnerability_finding_date table over to the fact_asset_vulnerability_remediation_date table. JOINing on asset_id,vulnerability_id is not good enough as there will be a match in the finding table for every day the vulnerability was “open”. I also noted inconsistencies when auth failed during a scan and vulnerabilities were “closed” then “reopened”.

For every row in the remediation_date table, there should be a matching asset/vulnerability pair at (day - INTERVAL ‘1 day’) in the finding_date table that can be used to get the first_found_date, but haven’t figured out how to do an efficient join based on that.

You’ll have to do a group by / distinct on key fields to clean it up, otherwise you will have lots of doubling. The query below is what I came up with but it’s incredibly taxing, I will most likely create a materialized view that triggers after each nightly export from Rapid7 to reduce the strain on the Postgresql server.

with remediation as (
-- Remediation Dates
	frd.asset_id, frd.vulnerability_id, "remediated_date"
	fact_asset_vulnerability_remediation_date frd),
identified as (
-- Identification Dates grouped by asset id, vuln id, and date
	fri.asset_id, fri.vulnerability_id,
	fri."date" as "identified_date"
	fact_asset_vulnerability_finding_date fri
group by
	fri.asset_id, fri.vulnerability_id, fri."date"),
assets as (
-- Assets for the host name.
	dim_asset das)
	a.asset_id, a.host_name, i.vulnerability_id,
	i.identified_date, r.remediated_date
	assets a
inner join identified i on
	i.asset_id = a.asset_id
left outer join remediation r on
	(r.asset_id = i.asset_id and r.vulnerability_id = i.vulnerability_id)
group by
	a.asset_id, a.host_name, i.vulnerability_id,
	i.identified_date, r.remediated_date;