Wanted to Query the database to list all remediated vulnerabilities and time it took to remediate

biju_vaderiyattil · February 22, 2023, 5:23pm

I Wanted to Query the database to list all remediated vulnerabilities and time it took to remediate from discovery date for a specified time frame for example last 90 Days.

john_hartman · February 22, 2023, 9:04pm

Currently there’s no way to do this through the console reports however there are some tables in the Data Warehouse that make it possible if you have that set up. Unfortunately if you do not already have a data warehouse setup then when you run those queries you can only go as far back as when you first set up the warehouse. Also this would be dependent on setting up daily exports to the data warehouse.

biju_vaderiyattil · February 22, 2023, 11:01pm

We are setting up a data warehouse. Hopefully it will be up and running next few days. any guidance on how to write this query is appreciated.

john_hartman · February 23, 2023, 4:56pm

We actually have an example query in the public repo that might help:

github.com

rapid7/insightvm-sql-queries/blob/master/data-warehouse-sql-queries/Vulnerabilities-Remediated-Within-SLA.sql

-- Data Warehouse
-- Presents the number of days to remediate asset vulnerabilities and whether or not they sit inside or outside a 30 day (modifiable) remediation SLA.  
-- This query only assumes remediation in that the vulnerability was no longer located on the asset and wrote the vulnerability no longer found out to the 'fact_asset_vulnerability_remediation_date' table
-- Copy the SQL query below

select vfd.asset_id, vfd.vulnerability_id, vfd.critical_vulnerabilities,
vfd.severe_vulnerabilities, extract (day from rd.day - vfd.date) as sla_days,
case when (select extract (day from rd.day - vfd.date)) <= 30 then 'yes'
else 'no'
end sla_met
from fact_asset_vulnerability_finding_date vfd
join fact_asset_vulnerability_remediation_date rd using (vulnerability_id)
where vfd.critical_vulnerabilities = '1' or
vfd.severe_vulnerabilities = '1'

Lee · August 30, 2023, 3:42pm

John Hartman:

-- Data Warehouse
-- Presents the number of days to remediate asset vulnerabilities and whether or not they sit inside or outside a 30 day (modifiable) remediation SLA.  
-- This query only assumes remediation in that the vulnerability was no longer located on the asset and wrote the vulnerability no longer found out to the 'fact_asset_vulnerability_remediation_date' table
-- Copy the SQL query below

select vfd.asset_id, vfd.vulnerability_id, vfd.critical_vulnerabilities,
vfd.severe_vulnerabilities, extract (day from rd.day - vfd.date) as sla_days,
case when (select extract (day from rd.day - vfd.date)) <= 30 then 'yes'
else 'no'
end sla_met
from fact_asset_vulnerability_finding_date vfd
join fact_asset_vulnerability_remediation_date rd using (vulnerability_id)
where vfd.critical_vulnerabilities = '1' or
vfd.severe_vulnerabilities = '1'

When I try to validate this query in the SQL report, I get this error:

Error: relation “fact_asset_vulnerability_finding_date” does not exist
Character: 252

Lee · August 30, 2023, 3:47pm

My issue might be with not having a data warehouse and trying to run this on my R7 Security Console reporting module. The data warehouse options never came up with our implementation and R7 professional services. I’ll inquire with support.