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 ewhat 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
Character:175
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.
Update:
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)