We are trying to pull automated reports each week of what was remediated. Has anyone had any luck with an SQL query of the sort? I’m not seeing a built-in report and currently I’m having to export from a catch-all remediation project which isn’t very intuitive.
Thanks in advance!
Remediation data and reporting has been lacking in Rapid7 VM for years.
It has been requested by our company to provision this detailed data.
I know Rapid7 is working on provisioning and improving this type of data and reporting for performance and compliance.
As intermediate solution we utilize the dashboard cards ‘Remediated Vulnerability’, ‘New vs. Remediated Vulnerabilities’ and ‘New vs. Remediated Vulnerability Comparison over Time’.
That last card is useful for high level trend reporting.
As you mention the remediation project is another alternative way for tracking progress.
We build a Query that looks at the new and remediated vulnerabilities, between de last scan and previous scan. In this case it works because we do weekly scanning.
WITH scans AS (
SELECT
da.asset_id,
previousScan(da.asset_id) AS baseline_scan,
lastScan(da.asset_id) AS current_scan
FROM dim_asset AS da
),
baseline AS (
SELECT DISTINCT f.asset_id, f.vulnerability_id
FROM fact_asset_scan_vulnerability_instance AS f
JOIN scans AS s ON s.asset_id = f.asset_id
WHERE f.scan_id = s.baseline_scan
),
current AS (
SELECT DISTINCT f.asset_id, f.vulnerability_id
FROM fact_asset_scan_vulnerability_instance AS f
JOIN scans AS s ON s.asset_id = f.asset_id
WHERE f.scan_id = s.current_scan
),
remediated AS (
SELECT b.asset_id, b.vulnerability_id, 'Remediated' AS status
FROM baseline AS b
LEFT JOIN current AS c
ON c.asset_id = b.asset_id
AND c.vulnerability_id = b.vulnerability_id
WHERE c.vulnerability_id IS NULL
),
newly_found AS (
SELECT c.asset_id, c.vulnerability_id, 'New' AS status
FROM current AS c
LEFT JOIN baseline AS b
ON b.asset_id = c.asset_id
AND b.vulnerability_id = c.vulnerability_id
WHERE b.vulnerability_id IS NULL
),
combined AS (
SELECT * FROM remediated
UNION ALL
SELECT * FROM newly_found
),
refs AS (
SELECT
dvr.vulnerability_id,
dvr.source,
dvr.reference
FROM dim_vulnerability_reference dvr
WHERE dvr.reference ~* '^CVE-[0-9]{4}-[0-9]+'
)
SELECT
da.ip_address AS "IP Address",
COALESCE(da.host_name, '') AS "Hostname",
COALESCE(string_agg(DISTINCT refs.reference, ', ' ORDER BY refs.reference), '') AS "CVE",
dv.title AS "Title",
dv.severity AS "Severity",
ROUND(dv.cvss_v2_score::numeric, 1) AS "CVSSv2",
ROUND(dv.cvss_v3_score::numeric, 1) AS "CVSSv3",
TO_CHAR(ds_latest.finished, 'YYYY-MM-DD HH24:MI') AS "Date",
da.sites AS "Sites",
combined.status AS "Status"
FROM combined
JOIN dim_asset da ON da.asset_id = combined.asset_id
JOIN scans s ON s.asset_id = combined.asset_id
JOIN dim_scan ds_latest ON ds_latest.scan_id = s.current_scan
JOIN dim_vulnerability dv ON dv.vulnerability_id = combined.vulnerability_id
LEFT JOIN refs ON refs.vulnerability_id = dv.vulnerability_id
GROUP BY
da.ip_address,
da.host_name,
dv.vulnerability_id,
dv.title,
dv.severity,
dv.cvss_v2_score,
dv.cvss_v3_score,
ds_latest.finished,
da.sites,
combined.status
ORDER BY
combined.status,
da.ip_address,
dv.title;
I know for remediation that you can adjust the query in a way that it also could use an older scan as baseline, with something like the query below:
WITH ranked_scans AS (
SELECT
f.asset_id,
f.scan_id,
ROW_NUMBER() OVER (
PARTITION BY f.asset_id
ORDER BY ds.finished DESC
) AS rn
FROM fact_asset_scan_vulnerability_instance f
JOIN dim_scan ds ON ds.scan_id = f.scan_id
),
scans AS (
SELECT
rs.asset_id,
MAX(CASE WHEN rs.rn = 4 THEN rs.scan_id END) AS baseline_scan,
MAX(CASE WHEN rs.rn = 1 THEN rs.scan_id END) AS current_scan
FROM ranked_scans rs
WHERE rs.rn IN (1, 3)
GROUP BY rs.asset_id
Hopefully this information could help you.