Vulnerability Report Showing # of New, Remediated and Outstanding Vulnerabilities for past 90 days by CVSS rating (Critical, High, Medium, and Low)

InsightVM SQL Queries
1

I am trying to develop a report to help with compliance reporting. I need the report to show:

  • number of new vulnerabilities (CRITICAL, HIGH, MEDIUM, LOW)
  • number of remediated vulnerabilities (CRITICAL, HIGH, MEDIUM, LOW)
  • number of outstanding vulnerabilities (CRITICAL, HIGH, MEDIUM, LOW)
    Period is last 90 days and I want to use the ‘first discovered date’ as the date to determine what vulnerabilities to include for new.
    Rating scale below:
    |None|0.0|
    |Low|0.1 - 3.9|
    |Medium|4.0 - 6.9|
    |High|7.0 - 8.9|
    |Critical|9.0 - 10.0|

It baffles me why this is so difficult to obtain from Rapid 7…

4 Likes
2

Not sure if you had gotten an answer about this, but i think this is something that may work or tailor to what you need… potentially…

You can run this in the console reporting under the SQL report section, you will want to select your Assets, IP, SITE or AG based on what your looking for..

WITH
-- 1. Map vulnerabilities to severity
vuln_scores AS (
  SELECT
    vulnerability_id,
    CASE
      WHEN cvss_score = 0 THEN 'None'
      WHEN cvss_score BETWEEN 0.1 AND 3.9 THEN 'Low'
      WHEN cvss_score BETWEEN 4.0 AND 6.9 THEN 'Medium'
      WHEN cvss_score BETWEEN 7.0 AND 8.9 THEN 'High'
      WHEN cvss_score BETWEEN 9.0 AND 10.0 THEN 'Critical'
      ELSE 'Unknown'
    END AS severity
  FROM dim_vulnerability
),

-- 2. New vulnerabilities (first seen in the last 90 days)
new_vulns AS (
  SELECT
    va.vulnerability_id,
    vs.severity
  FROM fact_asset_vulnerability_age va
  JOIN vuln_scores vs ON va.vulnerability_id = vs.vulnerability_id
  WHERE va.first_discovered >= current_date - interval '90 days'
),

-- 3. Outstanding vulnerabilities (currently still present)
outstanding_vulns AS (
  SELECT
    vi.vulnerability_id,
    vs.severity
  FROM fact_asset_vulnerability_instance vi
  JOIN vuln_scores vs ON vi.vulnerability_id = vs.vulnerability_id
),

-- 4. Remediated vulnerabilities (first discovered in last 90 days, but no longer present)
remediated_vulns AS (
  SELECT
    va.vulnerability_id,
    vs.severity
  FROM fact_asset_vulnerability_age va
  JOIN vuln_scores vs ON va.vulnerability_id = vs.vulnerability_id
  LEFT JOIN fact_asset_vulnerability_instance vi
    ON va.asset_id = vi.asset_id AND va.vulnerability_id = vi.vulnerability_id
  WHERE va.first_discovered >= current_date - interval '90 days'
    AND vi.vulnerability_id IS NULL
)

-- 5. Aggregate by severity
SELECT
  vs.severity,
  COUNT(DISTINCT nv.vulnerability_id) AS new_vulnerabilities,
  COUNT(DISTINCT rv.vulnerability_id) AS remediated_vulnerabilities,
  COUNT(DISTINCT ov.vulnerability_id) AS outstanding_vulnerabilities
FROM vuln_scores vs
LEFT JOIN new_vulns nv ON vs.vulnerability_id = nv.vulnerability_id
LEFT JOIN remediated_vulns rv ON vs.vulnerability_id = rv.vulnerability_id
LEFT JOIN outstanding_vulns ov ON vs.vulnerability_id = ov.vulnerability_id
GROUP BY vs.severity
ORDER BY
  CASE vs.severity
    WHEN 'Critical' THEN 1
    WHEN 'High' THEN 2
    WHEN 'Medium' THEN 3
    WHEN 'Low' THEN 4
    WHEN 'None' THEN 5
    ELSE 6
  END;