Trying to figure out the best way of getting the first_found_date of a vulnerability into the fact_asset_vulnerability_remediation_date table. I have a work-around for measuring vulnerability_age by using either the published_date or modified_date from the dim_vulnerability table, but would really like to use the date field from the fact_asset_vulnerability_finding table. I think the issue comes down to the many-to-many relationship between the fact_finding and fact_remediation tables. Is there anyway to reduce the fact_asset_vulnerability_finding table to a single row for each vulnerability/asset pair?
This is one approach I took, however it is very slow, especially when trying to include data from more than a few months. Joining the 2 fact tables on asset_id and vulnerability_id keys is much faster, but introduces many duplicates.
WITH a AS( SELECT day, dat.asset_id, favf.vulnerability_id, favf.date "first_found_date", CONCAT(dat.asset_id, '-', favf.vulnerability_id) AS "unique_key" FROM dim_asset_tag dat JOIN fact_asset_vulnerability_finding_date favf USING(asset_id) WHERE tag_id IN (1447) AND day > '1-1-21' ), b AS ( SELECT favrd.day AS remediation_date, favrd.asset_id, favrd.vulnerability_id, CONCAT(dat.asset_id, '-', favrd.vulnerability_id) AS "unique_key" FROM dim_asset_tag dat JOIN fact_asset_vulnerability_remediation_date favrd USING(asset_id) WHERE tag_id IN (1447) AND day > '1-1-21' ), c AS ( -- cannot use age function on hist tables, use DATE_PART SELECT b.*, a.first_found_date, DATE_PART('day', remediation_date::timestamp - first_found_date::timestamp) AS "vulnerability_age" FROM b b LEFT JOIN a a USING(unique_key) ), d AS ( SELECT c.*, da.os_system, dv.risk_score, dv.nexpose_id, dv.title, dv.cvss_score, dv.cvss_v3_score, CASE WHEN dv.cvss_v3_score IS NOT NULL THEN CASE WHEN cvss_v3_score >= 9 OR dv.nexpose_id = 'tlsv1_0-enabled' THEN 'Critical' WHEN cvss_v3_score >= 7 AND cvss_v3_score < 9 THEN 'High' WHEN cvss_v3_score >= 4 AND cvss_v3_score < 7 THEN 'Medium' ELSE 'Low' END WHEN dv.cvss_v3_score IS NULL THEN CASE WHEN cvss_score >= 9 OR dv.nexpose_id = 'tlsv1_0-enabled' THEN 'Critical' WHEN cvss_score >= 7 AND cvss_score < 9 THEN 'High' WHEN cvss_score >= 4 AND cvss_score < 7 THEN 'Medium' ELSE 'Low' END END as "dlx_severity", CASE WHEN dv.risk_score >= 900 OR dv.nexpose_id = 'tlsv1_0-enabled' OR (dvc.category_name = 'Rapid7 Critical' AND dv.nexpose_id NOT LIKE '%sweet32%') THEN 'Critical' WHEN dv.risk_score >= 700 AND dv.risk_score < 900 THEN 'High' WHEN dv.risk_score >= 400 AND dv.risk_score < 700 THEN 'Medium' ELSE 'Low' END AS "dlx_risk_based_severity" FROM c c JOIN dim_vulnerability dv USING(vulnerability_id) JOIN dim_vulnerability_category dvc USING(vulnerability_id) JOIN dim_asset da USING(asset_id) ), e AS ( SELECT d.*, CASE WHEN dlx_severity = 'Medium' AND os_system = 'CentOS Linux' THEN 120 WHEN dlx_severity = 'High' AND os_system = 'CentOS Linux' THEN 90 WHEN dlx_severity = 'Critical' AND os_system = 'CentOS Linux' THEN 60 WHEN dlx_severity = 'Medium' THEN 90 WHEN dlx_severity = 'High' THEN 60 WHEN dlx_severity = 'Critical' THEN 30 ELSE NULL END as "sla_target", CASE WHEN dlx_risk_based_severity = 'Medium' AND os_system = 'CentOS Linux' THEN 120 WHEN dlx_risk_based_severity = 'High' AND os_system = 'CentOS Linux' THEN 90 WHEN dlx_risk_based_severity = 'Critical' AND os_system = 'CentOS Linux' THEN 60 WHEN dlx_risk_based_severity = 'Medium' THEN 90 WHEN dlx_risk_based_severity = 'High' THEN 60 WHEN dlx_risk_based_severity = 'Critical' THEN 30 ELSE NULL END as "sla_target_risk_based" FROM d d ) SELECT DISTINCT unique_key, remediation_date, asset_id, vulnerability_id, first_found_date, vulnerability_age, os_system, nexpose_id, title, risk_score, cvss_score, cvss_v3_score, dlx_severity, dlx_risk_based_severity, sla_target, sla_target_risk_based, CASE WHEN vulnerability_age <= sla_target THEN 'Within SLA' WHEN vulnerability_age > sla_target THEN 'Past SLA' ELSE 'No SLA' END AS "dlx_sla", CASE WHEN vulnerability_age <= sla_target_risk_based THEN 'Within SLA' WHEN vulnerability_age > sla_target_risk_based THEN 'Past SLA' ELSE 'No SLA' END AS "dlx_risk_based_sla" FROM e
what is a “dim_asset_tag” ?
I am on the same boat. Tried using this, but it resulted an error. See below.
Error:relation “dim_asset_tag” does not exist
This is a query for the data warehouse, I probably should have mentioned that. dim_asset_tag is a table that provides the link between a tag_id and an asset_id. Based on your error, you are trying to run this query in the console, which will not work. The table that is similar to the warehouse dim_asset_tag is dim_tag_asset in the console.
Thanks for the info Trevor. Yes, I was trying to run this on console>report>sqlqueryexport and it didn’t work.
I think I’m close, just not sure if the 2nd CTE actually returns a single row for each asset/vulnerability pair. I’m also not sure how/if an authentication failure during a scan that marks vulnerabilities as “remediated” and a subsequent scan then “re-opens” the vulnerabilities is being handled appropriately.
WITH remediation AS ( SELECT * FROM fact_asset_vulnerability_remediation_date WHERE day > '5-1-21' ), findings AS ( --return a single row for each asset/vulnerability pair? SELECT DISTINCT ON (asset_id, vulnerability_id) asset_id, vulnerability_id, MAX(day) "day", date FROM fact_asset_vulnerability_finding_date WHERE day > '5-1-21' GROUP BY asset_id, vulnerability_id, date ) SELECT r.asset_id, r.vulnerability_id, r.day "remediation_date", f.date "first_found_date", DATE_PART('days', r.day - f.date) "age" FROM remediation r JOIN findings f USING(asset_id, vulnerability_id)