Historical report of Vulnerability Counts

zsmiley · January 8, 2025, 4:16pm

I need to create a report to meet regulatory requirements that can provide the following information:

• Time period: 12 month period in the past.
• Vulnerability Counts, broken down by CVSS v3 Low, Med, High, Critical severities:
	○ Detected vulnerabilities.
	○ Remediated vulnerabilities.
	○ Open vulnerabilities.
• The above counts should include vulnerabilities on assets that have been removed.
• Asset Scoping to be determined by a regex pattern matching against asset names.
• Notes
	○ Our Console has been configured to use Active Risk strategy.

I reviewed this SQL Query Export, but it looks to use a baseline scan rather than a historical time period.

github.com

rapid7/insightvm-sql-queries/blob/master/sql-query-export/New-and-Remediated-Vulns-with-Vuln-details.sql

-- New and Remediated Vulns with Vuln details
-- Copy the SQL query below
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 (

This file has been truncated. show original

I’ve explored various Dashboard cards but the appropriate cards provide counts for just a few weeks.

It seems that the Query Builder doesn’t have the capability to report on historical counts, especially for deleted assets.

I’ve explored the Console-generated reports, as well, but I can’t find a report that can be configured to the above requirements.

Any insights would be greatly appreciated