# Remediated Vulnerabilities Report?

**URL:** <https://discuss.rapid7.com/t/remediated-vulnerabilities-report/43558>\
**Category:** InsightVM\
**Tags:** InsightVM\
**Created:** [October 11, 2024, 5:06pm UTC](https://discuss.rapid7.com/t/remediated-vulnerabilities-report/43558 "2024-10-11T17:06:31Z")\
**Posts on this page:** 17\
**Page:** 1

<div class="post-metadata">

**Author:** ![Lee](https://discuss.rapid7.com/user_avatar/discuss.rapid7.com/lee/32/2108_2.png) [@Lee](https://discuss.rapid7.com/u/Lee)\
**Post date:** [October 11, 2024, 5:06pm UTC](https://discuss.rapid7.com/t/remediated-vulnerabilities-report/43558/1 "2024-10-11T17:06:31Z")

</div>

I have auditors asking me for a simple CSV report that shows all the vulnerabilities found and remediated for given date range, in this case it is 9/1/23 to 9/30/24.  
The report MUST have the following columns:  
“Vulnerability title | First found date | Scan Date when remediated | Remediated Asset count”

I tried dashboard cards (first found date not constrained in chosen date range), query builder (does not provide a query for remediated data), console reports (incl. SQL report, does not provided needed basic data), and cloud reports…

WHY am I not able to find a way to do this??

---

<div class="post-metadata">

**Author:** ![mmur\_gt4e](https://discuss.rapid7.com/user_avatar/discuss.rapid7.com/mmur_gt4e/32/1985_2.png) [@mmur\_gt4e](https://discuss.rapid7.com/u/mmur_gt4e)\
**Post date:** [October 14, 2024, 8:31am UTC](https://discuss.rapid7.com/t/remediated-vulnerabilities-report/43558/2 "2024-10-14T08:31:01Z")

</div>

Hey @Lee , have you tried the SQL to get CSV and build the report you want…?

[insightvm-sql-queries/sql-query-export/Track-Remediaton.sql at master · rapid7/insightvm-sql-queries · GitHub](https://github.com/rapid7/insightvm-sql-queries/blob/master/sql-query-export/Track-Remediaton.sql)

[insightvm-sql-queries/sql-query-export/Top-25-Remediation-Report.sql at master · rapid7/insightvm-sql-queries · GitHub](https://github.com/rapid7/insightvm-sql-queries/blob/master/sql-query-export/Top-25-Remediation-Report.sql)

[insightvm-sql-queries/sql-query-export/New-and-Remediated-Vulns-with-Vuln-details.sql at master · rapid7/insightvm-sql-queries · GitHub](https://github.com/rapid7/insightvm-sql-queries/blob/master/sql-query-export/New-and-Remediated-Vulns-with-Vuln-details.sql)

Of course you can grab the fields you need…

---

<div class="post-metadata">

**Author:** ![Lee](https://discuss.rapid7.com/user_avatar/discuss.rapid7.com/lee/32/2108_2.png) [@Lee](https://discuss.rapid7.com/u/Lee)\
**Post date:** [October 14, 2024, 1:15pm UTC](https://discuss.rapid7.com/t/remediated-vulnerabilities-report/43558/3 "2024-10-14T13:15:42Z")

</div>

@mmur_gt4e yes I have (updated my above post to reflect), unfortunately they do not provide the basic needed data columns/tables I mentioned above for the date range.  
I edited my post to also note the shortcomings with each.

These SQL export reports were not helpful for this need:

- Track-Remediaton.sql at master - THIS IS NOT FOR REMEDIATED VULNERABILITIES
- Top-25-Remediation-Report.sql at master - [THIS NOT FOR REMEDIATED VULNERABILITIES]
- New-and-Remediated-Vulns-with-Vuln-details.sql at master · [YOU CANNOT SPECIFY A DATE RANGE TO CONSTRAIN THE DATA AND THERE IS NO REMEDIATED SCAN DATE]

---

<div class="post-metadata">

**Author:** ![mmur\_gt4e](https://discuss.rapid7.com/user_avatar/discuss.rapid7.com/mmur_gt4e/32/1985_2.png) [@mmur\_gt4e](https://discuss.rapid7.com/u/mmur_gt4e)\
**Post date:** [October 14, 2024, 1:41pm UTC](https://discuss.rapid7.com/t/remediated-vulnerabilities-report/43558/4 "2024-10-14T13:41:08Z")

</div>

So if they just do not specify the date range you might just need to ask them to update the model…  
I guess the table you have used was the following…

![forum1](https://forum-uploads-hub-prod-1-us-east-1-rapid7-com.s3.dualstack.us-east-1.amazonaws.com/original/2X/4/4945db7e28f6921dc10888c0eba99c296707fe86.png)

---

<div class="post-metadata">

**Author:** ![bradpjaxx](https://discuss.rapid7.com/user_avatar/discuss.rapid7.com/bradpjaxx/32/2411_2.png) [@bradpjaxx](https://discuss.rapid7.com/u/bradpjaxx)\
**Post date:** [October 15, 2024, 8:13pm UTC](https://discuss.rapid7.com/t/remediated-vulnerabilities-report/43558/5 "2024-10-15T20:13:14Z")

</div>

just add a WHERE clause in the baseline\_scan\_date, current\_scan\_date, and final SELECT queries to filter by a date range (e.g., BETWEEN ‘2024-01-01’ AND ‘2024-12-31’). Adjust this date range as needed in the script sections below, that should allow you to set your ranges…

```auto
WITH assets_vulns AS (
	SELECT
		fasv.asset_id,
		fasv.vulnerability_id,
		baselineComparison(fasv.scan_id, current_scan) AS baseline,
		s.baseline_scan,
		s.current_scan
	FROM
		fact_asset_scan_vulnerability_instance fasv
		JOIN (
			SELECT
				asset_id,
				previousScan(asset_id) AS baseline_scan,
				lastScan(asset_id) AS current_scan
			FROM
				dim_asset
		) s ON s.asset_id = fasv.asset_id
		AND (
			fasv.scan_id = s.baseline_scan
			OR fasv.scan_id = s.current_scan
		)
	GROUP BY
		fasv.asset_id,
		fasv.vulnerability_id,
		s.baseline_scan,
		s.current_scan
	HAVING
		(
			baselineComparison(fasv.scan_id, current_scan) = 'Same'
		)
		OR (
			baselineComparison(fasv.scan_id, current_scan) = 'New'
		)
		OR (
			baselineComparison(fasv.scan_id, current_scan) = 'Old'
		)
),
baseline_scan_date AS (
	SELECT
		av.asset_id,
		finished
	FROM
		assets_vulns av
		LEFT JOIN dim_scan ds ON ds.scan_id = av.baseline_scan
	WHERE
		ds.finished BETWEEN '2024-01-01' AND '2024-12-31' -- Modify the date range here
	GROUP BY
		av.asset_id,
		finished
),
current_scan_date AS (
	SELECT
		av.asset_id,
		finished
	FROM
		assets_vulns av
		LEFT JOIN dim_scan ds ON ds.scan_id = av.current_scan
	WHERE
		ds.finished BETWEEN '2024-01-01' AND '2024-12-31' -- Modify the date range here
	GROUP BY
		av.asset_id,
		finished
),
new_vulns AS (
	SELECT
		av.asset_id,
		av.vulnerability_id,
		COUNT(av.vulnerability_id) AS new_vulns
	FROM
		assets_vulns AS av
	WHERE
		av.baseline = 'New'
	GROUP BY
		av.asset_id,
		av.vulnerability_id
),
remediated_vulns AS (
	SELECT
		av.asset_id,
		av.vulnerability_id,
		COUNT(av.vulnerability_id) AS remediated_vulns
	FROM
		assets_vulns AS av
	WHERE
		av.baseline = 'Old'
	GROUP BY
		av.asset_id,
		av.vulnerability_id
),
vuln_exploit_count AS (
	SELECT
		CASE
			WHEN ec1.vulnerability_id IS NOT NULL THEN ec1.vulnerability_id
			ELSE ec2.vulnerability_id
		END AS vulnerability_id,
		metasploit,
		exploitdb
	FROM
		(
			SELECT
				av.vulnerability_id,
				COUNT(dve.source) AS metasploit
			FROM
				assets_vulns av
				JOIN dim_vulnerability_exploit dve ON av.vulnerability_id = dve.vulnerability_id
			WHERE
				dve.source = 'Metasploit'
			GROUP BY
				av.vulnerability_id
		) ec1 FULL
		JOIN (
			SELECT
				av.vulnerability_id,
				COUNT(dve.source) AS exploitdb
			FROM
				assets_vulns av
				JOIN dim_vulnerability_exploit dve ON av.vulnerability_id = dve.vulnerability_id
			WHERE
				dve.source = 'Exploit DB'
			GROUP BY
				av.vulnerability_id
		) ec2 ON ec2.vulnerability_id = ec1.vulnerability_id
)
SELECT
	'Remediated' AS status,
	da1.ip_address AS ip_address,
	da1.host_name AS hostname,
	bsd.finished AS baseline_scan_datetime,
	csd.finished AS current_scan_datetime,
	dv1.vulnerability_id,
	dv1.title,
	CAST(dv1.cvss_score AS decimal(10, 2)) AS cvss_score,
	CAST(dv1.riskscore AS decimal(10, 0)) AS riskscore,
	dv1.malware_kits,
	CASE
		WHEN vec.metasploit IS NULL THEN 0
		ELSE vec.metasploit
	END AS metasploit,
	CASE
		WHEN vec.exploitdb IS NULL THEN 0
		ELSE vec.exploitdb
	END AS exploitdb
FROM
	remediated_vulns rv
	JOIN dim_asset da1 ON da1.asset_id = rv.asset_id
	LEFT JOIN baseline_scan_date bsd ON bsd.asset_id = da1.asset_id
	LEFT JOIN current_scan_date csd ON csd.asset_id = da1.asset_id
	JOIN dim_vulnerability dv1 ON dv1.vulnerability_id = rv.vulnerability_id
	LEFT JOIN vuln_exploit_count vec ON vec.vulnerability_id = rv.vulnerability_id
UNION ALL
SELECT
	'New' AS status,
	da2.ip_address AS ip_address,
	da2.host_name AS hostname,
	bsd.finished AS baseline_scan_datetime,
	csd.finished AS current_scan_datetime,
	dv2.vulnerability_id,
	dv2.title,
	CAST(dv2.cvss_score AS decimal(10, 2)) AS cvss_score,
	CAST(dv2.riskscore AS decimal(10, 0)) AS riskscore,
	dv2.malware_kits,
	CASE
		WHEN vec.metasploit IS NULL THEN 0
		ELSE vec.metasploit
	END AS metasploit,
	CASE
		WHEN vec.exploitdb IS NULL THEN 0
		ELSE vec.exploitdb
	END AS exploitdb
FROM
	new_vulns nv
	JOIN dim_asset AS da2 ON da2.asset_id = nv.asset_id
	LEFT JOIN baseline_scan_date bsd ON bsd.asset_id = da2.asset_id
	LEFT JOIN current_scan_date csd ON csd.asset_id = da2.asset_id
	JOIN dim_vulnerability dv2 ON dv2.vulnerability_id = nv.vulnerability_id
	LEFT JOIN vuln_exploit_count vec ON vec.vulnerability_id = nv.vulnerability_id
WHERE
	(bsd.finished BETWEEN '2024-01-01' AND '2024-12-31' OR csd.finished BETWEEN '2024-01-01' AND '2024-12-31') -- Modify the date range here
ORDER BY
	status DESC,
	ip_address,
	hostname,
	title;

```

---

<div class="post-metadata">

**Author:** ![Lee](https://discuss.rapid7.com/user_avatar/discuss.rapid7.com/lee/32/2108_2.png) [@Lee](https://discuss.rapid7.com/u/Lee)\
**Post date:** [October 17, 2024, 1:46pm UTC](https://discuss.rapid7.com/t/remediated-vulnerabilities-report/43558/6 "2024-10-17T13:46:41Z")

</div>

> [@Lee](#):
>
> New-and-Remediated-Vulns-with-Vuln-details.sql

@bradpjaxx I am assuming you are referring to editing the SQL report ‘New-and-Remediated-Vulns-with-Vuln-details.sql’ with the WHERE clause you noted above?  
I will give this a shot…

---

<div class="post-metadata">

**Author:** ![bradpjaxx](https://discuss.rapid7.com/user_avatar/discuss.rapid7.com/bradpjaxx/32/2411_2.png) [@bradpjaxx](https://discuss.rapid7.com/u/bradpjaxx)\
**Post date:** [October 17, 2024, 2:02pm UTC](https://discuss.rapid7.com/t/remediated-vulnerabilities-report/43558/7 "2024-10-17T14:02:51Z")

</div>

yea, i pasted in new query for you, and in there just modify your dates… try that and see if thats what you need.

---

<div class="post-metadata">

**Author:** ![Lee](https://discuss.rapid7.com/user_avatar/discuss.rapid7.com/lee/32/2108_2.png) [@Lee](https://discuss.rapid7.com/u/Lee)\
**Post date:** [October 22, 2024, 7:19pm UTC](https://discuss.rapid7.com/t/remediated-vulnerabilities-report/43558/8 "2024-10-22T19:19:50Z")

</div>

@bradpjaxx I get this error when I try to set my dates and validate query:  
 ![image](https://forum-uploads-hub-prod-1-us-east-1-rapid7-com.s3.dualstack.us-east-1.amazonaws.com/original/2X/7/7114ff97f6784054ab8fa3bf58a367590d83754b.png)

---

<div class="post-metadata">

**Author:** ![bradpjaxx](https://discuss.rapid7.com/user_avatar/discuss.rapid7.com/bradpjaxx/32/2411_2.png) [@bradpjaxx](https://discuss.rapid7.com/u/bradpjaxx)\
**Post date:** [October 22, 2024, 7:32pm UTC](https://discuss.rapid7.com/t/remediated-vulnerabilities-report/43558/9 "2024-10-22T19:32:32Z")

</div>

make sure your formatting is the same in all data ranges. Looks like your missing the “0” in 09

‘2023-09-01’ AND ‘2024-09-31’

---

<div class="post-metadata">

**Author:** ![Lee](https://discuss.rapid7.com/user_avatar/discuss.rapid7.com/lee/32/2108_2.png) [@Lee](https://discuss.rapid7.com/u/Lee)\
**Post date:** [October 29, 2024, 9:12pm UTC](https://discuss.rapid7.com/t/remediated-vulnerabilities-report/43558/10 "2024-10-29T21:12:05Z")

</div>

Good catch…

I adjusted the dates to ‘2023-09-01’ AND ‘2024-09-30’ and ran the report with only one scope option set, which is for a single site (has scan data for last two years).

The report is not showing complete or accurate data.

When you filter the data for ‘baseline\_scan\_datetime’ or ‘current\_scan\_datetime’, the only two months that show are Sept and Oct. We have a one-year retention period on scan data in InsightVM, so why are all the other months missing?

When you filter the data for ‘baseline\_scan\_datetime’ or ‘current\_scan\_datetime’, the only two months that show are Sept and Oct.

![image](https://forum-uploads-hub-prod-1-us-east-1-rapid7-com.s3.dualstack.us-east-1.amazonaws.com/original/2X/5/5b668d60c6456e68620c252e12094284ccb552f2.png)

---

<div class="post-metadata">

**Author:** ![Lee](https://discuss.rapid7.com/user_avatar/discuss.rapid7.com/lee/32/2108_2.png) [@Lee](https://discuss.rapid7.com/u/Lee)\
**Post date:** [October 29, 2024, 10:13pm UTC](https://discuss.rapid7.com/t/remediated-vulnerabilities-report/43558/11 "2024-10-29T22:13:36Z")

</div>

I just do not understand why this is so hard and/or not possible…

In summary what I’ve tried so far:

- Dashboard card ‘Remediated Vulnerabilities’: Shortcoming is the card only allows for up to last month and not beyond.
- Query Builder/Explorer query: There is no vulnerability query for ‘remediated’.
- Console reports: No built-in CSV or PDF, tired SQL query noted above with added date range and the CSV results only show last two months. Searched other SQL reports on Github and no other queries are for [remediated] (mitigated), others are for ‘remediations’ which is simply remediation guidance.
- Cloud reports: Executive summary report does not show what vulnerabilities have been remediated with asset instances month-to-month OR over a custom date range.

HI need a CSV list of all remediated vulnerabilities for a given date range, which may be up to one year as it aligns with our scan data retention policy.  
For example, the report should have this data/columns:

Vulnerability, asset name, first found date, last scan date  
OR  
Vulnerability, first found date, last scan date, number of vulnerability instances

---

<div class="post-metadata">

**Author:** ![bradpjaxx](https://discuss.rapid7.com/user_avatar/discuss.rapid7.com/bradpjaxx/32/2411_2.png) [@bradpjaxx](https://discuss.rapid7.com/u/bradpjaxx)\
**Post date:** [October 30, 2024, 1:07pm UTC](https://discuss.rapid7.com/t/remediated-vulnerabilities-report/43558/12 "2024-10-30T13:07:18Z")

</div>

Im assuming within that SITE you have for these assets, their Scan HISTORY is longer than two months, and that Site has been created for longer than that?

---

<div class="post-metadata">

**Author:** ![Lee](https://discuss.rapid7.com/user_avatar/discuss.rapid7.com/lee/32/2108_2.png) [@Lee](https://discuss.rapid7.com/u/Lee)\
**Post date:** [October 30, 2024, 1:20pm UTC](https://discuss.rapid7.com/t/remediated-vulnerabilities-report/43558/13 "2024-10-30T13:20:10Z")

</div>

Yes, of course. I appreciate you confirming that. Also, our scan data retention has been set to 1 year approx. 2 years ago at implementation of InsightVM.  
We previously came from Tenable.sc and this type of report was super easy to generate and incredibly customizable…just saying

---

<div class="post-metadata">

**Author:** ![Lee](https://discuss.rapid7.com/user_avatar/discuss.rapid7.com/lee/32/2108_2.png) [@Lee](https://discuss.rapid7.com/u/Lee)\
**Post date:** [November 1, 2024, 7:21pm UTC](https://discuss.rapid7.com/t/remediated-vulnerabilities-report/43558/14 "2024-11-01T19:21:45Z")

</div>

My account customer success manager recommended I took another look at the ‘New vs. Remediated Vulnerabilities’ dashboard card…

Unfortunately, that card does not offer the ability to go back beyond the past month. My auditors regularly ask for random months up to a year back (our retention period is 1yr for scan data).  
 ![image](https://forum-uploads-hub-prod-1-us-east-1-rapid7-com.s3.dualstack.us-east-1.amazonaws.com/original/2X/6/650cc13b950fbe7d842eebbcfe25e11ab95b8a29.png)

---

<div class="post-metadata">

**Author:** ![Lee](https://discuss.rapid7.com/user_avatar/discuss.rapid7.com/lee/32/2108_2.png) [@Lee](https://discuss.rapid7.com/u/Lee)\
**Post date:** [November 6, 2024, 9:57pm UTC](https://discuss.rapid7.com/t/remediated-vulnerabilities-report/43558/15 "2024-11-06T21:57:26Z")

</div>

I had several meetings with R7 support and my account manager on this and this is where I am at:

- There is no way to get a basic list (CSV) for ALL vulnerability findings (active + remediated) for a given date range within the last 12 months (within our scan data retention setting).
- There is no way to get a list (CSV) for all remediated vulnerability findings for a given date range within the last 12 months (within our scan data retention setting).

Per R7, in order to get this basic type of vulnerability data (KPI’'s) out of R7 InsightVM, we are being told a data warehouse needs to be setup to export R7 data out…  
[Configuring data warehousing settings | InsightVM Documentation](https://docs.rapid7.com/insightvm/configuring-data-warehousing-settings/)

I’m left scratching my head on this…I have not had this limitation on prior vulnerability management platforms in the past.

---

<div class="post-metadata">

**Author:** ![Lee](https://discuss.rapid7.com/user_avatar/discuss.rapid7.com/lee/32/2108_2.png) [@Lee](https://discuss.rapid7.com/u/Lee)\
**Post date:** [November 13, 2024, 3:46pm UTC](https://discuss.rapid7.com/t/remediated-vulnerabilities-report/43558/16 "2024-11-13T15:46:35Z")

</div>

Per Rapid7 on a related support case, this is their response:

> _Thank you for your continued patience as we work through this. After thorough discussions with our team, we’ve determined that it may not be possible to achieve exactly what you’re looking for with a CSV export. This limitation arises due to the absence of remediation dates in our current reporting model._

As noted earlier, this is not possible with a dashboard card, PDF report (security console or cloud report) either. ☹

---

<div class="post-metadata">

**Author:** ![bradpjaxx](https://discuss.rapid7.com/user_avatar/discuss.rapid7.com/bradpjaxx/32/2411_2.png) [@bradpjaxx](https://discuss.rapid7.com/u/bradpjaxx)\
**Post date:** [November 19, 2024, 6:57pm UTC](https://discuss.rapid7.com/t/remediated-vulnerabilities-report/43558/17 "2024-11-19T18:57:57Z")

</div>

This makes sense as that is all collected in the WAREHOUSE, that is what we use and what i have always used…

I was thinking you may have been able to pull directly out of the NeXpose DB instead…
