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
select
frd.asset_id, frd.vulnerability_id,
frd.day "remediated_date"
from
fact_asset_vulnerability_remediation_date frd),
identified as (
-- Identification Dates grouped by asset id, vuln id, and date
select
fri.asset_id, fri.vulnerability_id,
fri."date" as "identified_date"
from
fact_asset_vulnerability_finding_date fri
group by
fri.asset_id, fri.vulnerability_id, fri."date"),
assets as (
-- Assets for the host name.
select
das.asset_id,
das.host_name
from
dim_asset das)
select
a.asset_id, a.host_name, i.vulnerability_id,
i.identified_date, r.remediated_date
from
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;