Theres alot going on here, but you can pick it apart and use what you need. Hopefully its useful.
WITH
assets AS ( --get assets and vulnerability metadata ##scoping must be done by tags in console
SELECT dta.asset_id, favi.vulnerability_id, htmlToText(favi.proof, false) "proof",
CASE WHEN favi.port < 0 THEN NULL ELSE favi.port END AS "port", ds.name "service", dp.name "protocol"
FROM dim_scope_tag
JOIN dim_tag_asset dta USING(tag_id)
JOIN fact_asset_vulnerability_instance favi USING(asset_id)
JOIN dim_service ds USING(service_id)
JOIN dim_protocol dp USING(protocol_id)
),
solutions AS ( --get the superceded solutions for each id and age of vulns
SELECT a.*, htmlToText(ds.fix) "fix", ds.summary, trunc(fava.age_in_days) AS "orig_vulnerability_age", first_discovered
FROM assets a
JOIN dim_asset_vulnerability_best_solution USING(asset_id, vulnerability_id)
JOIN dim_solution ds USING(solution_id)
JOIN fact_asset_vulnerability_age fava USING(asset_id, vulnerability_id)
),
r7 AS (
SELECT vulnerability_id, category_name
FROM dim_vulnerability_category
WHERE category_name = 'Rapid7 Critical'
),
vulns AS ( --get more info about the vulnerabilities and add severity
SELECT s.*, da.host_name, da.ip_address, da.last_assessed_for_vulnerabilities AS last_scan_date,
dv.nexpose_id, dv.title, htmlToText(dv.description) "description",
CASE
WHEN riskscore >= 900 OR dv.nexpose_id = 'tlsv1_0-enabled' OR (r7.category_name = 'Rapid7 Critical' AND dv.nexpose_id NOT LIKE '%sweet32%') THEN 'Critical'
WHEN riskscore >= 700 AND riskscore < 900 THEN 'High'
WHEN riskscore >= 400 AND riskscore < 700 THEN 'Medium'
ELSE 'Low'
END AS "dlx_risk_based_severity",
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_cvss_based_severity"
FROM solutions s
JOIN dim_vulnerability dv USING(vulnerability_id)
LEFT JOIN r7 USING(vulnerability_id)
JOIN dim_asset da USING(asset_id)
),
age_adjustment AS ( --fix for vulnerabilities that have an increased risk based severity being reported as past SLA
SELECT v.*,
CASE
WHEN first_discovered < '06-01-2021' THEN
CASE
WHEN dlx_risk_based_severity IN ('Critical','High','Medium') AND dlx_cvss_based_severity IN ('Low') THEN current_date - DATE '2021-06-01'
WHEN dlx_risk_based_severity IN ('Critical','High') AND dlx_cvss_based_severity IN ('Medium') THEN current_date - DATE '2021-06-01'
WHEN dlx_risk_based_severity IN ('Critical') AND dlx_cvss_based_severity IN ('High') THEN current_date - DATE '2021-06-01'
ELSE v.orig_vulnerability_age
END
ELSE v.orig_vulnerability_age
END AS "vulnerability_age"
FROM vulns v
),
sla AS ( -- add internal sla targets
SELECT v.*, dos.system, dos.version,
CASE
WHEN dlx_risk_based_severity = 'Medium' AND dos.system = 'CentOS Linux' THEN 120
WHEN dlx_risk_based_severity = 'High' AND dos.system = 'CentOS Linux' THEN 90
WHEN dlx_risk_based_severity = 'Critical' AND dos.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"
FROM age_adjustment v
JOIN dim_asset USING(asset_id)
JOIN dim_operating_system dos USING(operating_system_id)
)
--final formatting and sla
SELECT host_name "Asset Hostname", ip_address "Asset IP Address", system "Asset OS System", version "Asset OS Version",
nexpose_id "Vulnerability ID", title "Vulnerability Title", description "Vulnerability Description", dlx_risk_based_severity "DLX Severity",
CASE
WHEN vulnerability_age <= sla_target THEN 'Within SLA'
WHEN vulnerability_age > sla_target THEN 'Past SLA'
ELSE 'No SLA'
END AS "DLX SLA",
vulnerability_age "Vulnerability Age", proof "Vulnerability Proof", summary "Solution Summary", fix "Solution",
port "Port", protocol "Protocol", service "Service", to_char(last_scan_date, 'mm/dd/yyyy') "Last Scan Date"
FROM sla