Report that shows exploitable Vulnerabilities that are older than 60 days

InsightVM SQL Queries
1

Hello, I am new to Rapid7 and don’t do many SQL queries.

I am looking for a report that shows exploitable Vulnerabilities that are older than 60 days

Any help would be appreciated.

2

Found this in Copilot but it of course is not working,

SELECT
da.ip_address,
da.host_name,
dv.title AS vulnerability_title,
dv.riskscore,
dv.date_published,
dv.exploits,
fs.proof_as_text
FROM
dim_asset da
JOIN
fact_asset_vulnerability_finding favf ON da.asset_id = favf.asset_id
JOIN
dim_vulnerability dv ON favf.vulnerability_id = dv.vulnerability_id
JOIN
dim_vulnerability_reference dvr ON dv.vulnerability_id = dvr.vulnerability_id
LEFT JOIN
fact_vulnerability_solution fs ON favf.vulnerability_id = fs.vulnerability_id
WHERE
dv.date_published < (CURRENT_DATE - INTERVAL ‘60 days’)
AND dv.exploits IS NOT NULL
ORDER BY
dv.date_published DESC;

3

Use the “Exploitable Vulnerability Findings Discovery Date by CVSS Score (Grouped by V3 Category)” widget in your dashboard and select the age of the vulnerabilities to filter. There are many other prebuilt cards you can add related to this need.

Exploitable Vulnerability Findings.pdf (13.7 KB)

4

remove the Malware kits at the OR statement if you dont need or want that in there.

WITH vulnerability_age AS (
    SELECT
        asset_id,
        vulnerability_id,
        first_discovered
    FROM fact_asset_vulnerability_age
    WHERE first_discovered <= current_date - INTERVAL '60 days'
),
exploitable_vulns AS (
    SELECT
        vulnerability_id
    FROM dim_vulnerability
    WHERE exploits > 0 OR malware_kits > 0
)
SELECT
    da.ip_address AS "IP Address",
    da.host_name AS "Host Name",
    dv.title AS "Vulnerability Title",
    dv.nexpose_id AS "Nexpose ID",
    dv.severity AS "Severity",
    dv.cvss_score AS "CVSS Score",
    dv.exploits AS "Exploit Count",
    dv.malware_kits AS "Malware Kit Count",
    va.first_discovered AS "First Discovered"
FROM vulnerability_age va
JOIN exploitable_vulns ev ON va.vulnerability_id = ev.vulnerability_id
JOIN dim_asset da ON va.asset_id = da.asset_id
JOIN dim_vulnerability dv ON va.vulnerability_id = dv.vulnerability_id
ORDER BY va.first_discovered ASC;