How to pull vulnerability proof per instance

InsightVM SQL Queries
1

I have this query and i would like to add a column that shows vulnerability proof per vulnerabilitiy/asset instance. I believe i might need to do a join from a different table (would it be “FROM fact_asset_vulnerability_finding favf”)? Please help if possible :slightly_smiling_face:

SELECT DISTINCT
csv(DISTINCT dv.nexpose_id) AS “Vulnerability InsightVM ID”,
dv.title as “Vulnerability”,
dv.cvss_v2_score AS “CVSS_v2 Score”,
dv.cvss_v3_score AS “CVSS_v3 Score”,
dv.exploits AS “Number of Exploits Available”,
host_name AS “Asset Hostname”,
ip_address AS “Asset IP”,
last_assessed_for_vulnerabilities AS “Last Scan Date”,
summary AS “Solution”,
fix as “Fix”
FROM dim_asset
JOIN dim_asset_vulnerability_solution USING(asset_id)
JOIN dim_vulnerability dv USING(vulnerability_id)
JOIN dim_solution ds USING (solution_id)
JOIN dim_solution_highest_supercedence dshs ON ds.solution_id = dshs.superceding_solution_id
WHERE dv.cvss_score >= 9 OR dv.cvss_v3_score >= 9
GROUP BY dv.title, host_name, dv.cvss_v2_score, dv.cvss_v3_score, dv.exploits, ip_address, asset_id, summary, fix, last_assessed_for_vulnerabilities

1 Like
2

If you are going for the proof per vuln per asset, I would just use the built in Report Card report.

3

Thing is i want it in CSV format, and i want to keep that number of exploits available attribute which is not available in standard report filter

4

The table I used to add the proof column was fact_asset_vulnerability_instance (Understanding the reporting data model: Facts | InsightVM Documentation). I used proofAsTest to strip away the markup language that proof is stored with.

SELECT DISTINCT
csv(DISTINCT dv.nexpose_id) AS "Vulnerability InsightVM ID",
dv.title as "Vulnerability",
dv.cvss_v2_score AS "CVSS_v2 Score",
dv.cvss_v3_score AS "CVSS_v3 Score",
dv.exploits AS "Number of Exploits Available",
host_name AS "Asset Hostname",
ip_address AS "Asset IP",
last_assessed_for_vulnerabilities AS "Last Scan Date",
proofAsText(favi.proof) AS "Proof",
summary AS "Solution",
fix as "Fix"
FROM dim_asset da
JOIN dim_asset_vulnerability_solution davs ON davs.asset_id = da.asset_id
JOIN dim_vulnerability dv ON dv.vulnerability_id = davs.vulnerability_id
JOIN dim_solution ds ON ds.solution_id = davs.solution_id
JOIN fact_asset_vulnerability_instance favi ON da.asset_id = favi.asset_id AND favi.vulnerability_id = davs.vulnerability_id
JOIN dim_solution_highest_supercedence dshs ON ds.solution_id = dshs.superceding_solution_id
WHERE dv.cvss_score >= 9 OR dv.cvss_v3_score >= 9
GROUP BY dv.title, host_name, dv.cvss_v2_score, dv.cvss_v3_score, dv.exploits, ip_address, da.asset_id, summary, fix, last_assessed_for_vulnerabilities, favi.proof
2 Likes
5

This works perfectly! thanks so much! I added a item in there for OS as well. I think this should be posted up in github:

SELECT DISTINCT
ip_address AS “Asset IP”,
host_name AS “Asset Hostname”,
dos.description AS “OS”,
csv(DISTINCT dv.nexpose_id) AS “Vulnerability InsightVM ID”,
dv.title as “Vulnerability”,
dv.cvss_v2_score AS “CVSS_v2 Score”,
dv.cvss_v3_score AS “CVSS_v3 Score”,
dv.exploits AS “Number of Exploits Available”,
last_assessed_for_vulnerabilities AS “Last Scan Date”,
proofAsText(favi.proof) AS “Proof”,
summary AS “Solution”,
fix as “Fix”
FROM dim_asset da
JOIN dim_asset_operating_system AS daos ON da.asset_id = daos.asset_id
JOIN dim_operating_system AS dos ON daos.operating_system_id = dos.operating_system_id
JOIN dim_asset_vulnerability_solution davs ON davs.asset_id = da.asset_id
JOIN dim_vulnerability dv ON dv.vulnerability_id = davs.vulnerability_id
JOIN dim_solution ds ON ds.solution_id = davs.solution_id
JOIN fact_asset_vulnerability_instance favi ON da.asset_id = favi.asset_id AND favi.vulnerability_id = davs.vulnerability_id
JOIN dim_solution_highest_supercedence dshs ON ds.solution_id = dshs.superceding_solution_id
WHERE dv.cvss_score >= 9 OR dv.cvss_v3_score >= 9
GROUP BY dv.title, host_name, dos.description, dv.cvss_v2_score, dv.cvss_v3_score, dv.exploits, ip_address, da.asset_id, summary, fix, last_assessed_for_vulnerabilities, favi.proof

2 Likes
6

Please feel free to create a pull request to our public repo if it’s something you think should be up there. That public repo does have a lot of Rapid7 created queries but also has a handful of community generated ones as well brought over from years ago.

There is a team here at Rapid7 that meets weekly to approve the requests.

1 Like