SQL Query to Return all Known Exploited Vulnerability for An Asset

InsightVM SQL Queries
1

To Whomever Might Help me :pray:t4:

I need a SQL query (that will be run from InsightConnect) to return a list of vulnerabilities found on a specific asset and known to be actively exploited (based on Rapid7 database or CISA catalog).

Best,
Delvain Mbina

2

This is what I came up with but I am not getting any outputs

SELECT dv.nexpose_id AS “Vulnerability ID”, dv.title AS “Vulnerability Title”,
dv.severity AS “Severity”, dv.cvss_vector AS “CVSS”, dv.riskscore AS “Risk Score”
FROM fact_asset_vulnerability_finding favf
JOIN dim_asset da USING (asset_id)
JOIN dim_asset_host_name dahn ON da.asset_id = dahn.asset_id
JOIN dim_vulnerability dv USING (vulnerability_id)
JOIN dim_vulnerability_category dvc ON dv.vulnerability_id = dvc.vulnerability_id
Where da.host_name LIKE ‘Computer01%’ AND dvc.category_name = ‘cisa kev’
GROUP BY dv.nexpose_id, dv.title, dv.severity, dv.cvss_vector, dv.riskscore
ORDER BY dv.riskscore ASC

3

i noticed that the case was off on the cat name, some spaces in the AS titles, and quotes/ticks looked like they may have gone through Word or something (try notepad++ or some other text editor).

while i took some stuff out for my testing…you can put the missing items back in. this works:

SELECT dv.nexpose_id AS “Vulnerability_ID”, dv.title AS “Vulnerability_Title”
FROM fact_asset_vulnerability_finding favf
JOIN dim_vulnerability dv USING (vulnerability_id)
JOIN dim_vulnerability_category dvc ON dv.vulnerability_id = dvc.vulnerability_id
Where dvc.category_name = ‘CISA KEV’
GROUP BY dv.nexpose_id, dv.title
ORDER BY dv.nexpose_id ASC

4

Thank you for pointing out my mistakes and your suggestions. I modified the SQL query and the following works:

SELECT dv.nexpose_id AS “Vulnerability ID”, dv.title AS “Vulnerability Title”, dv.severity AS “Severity”, dv.cvss_vector AS “CVSS”, dv.riskscore AS “Risk Score”, dv.exploits AS “Exploits”, dv.malware_kits AS “Malware”
FROM fact_asset_vulnerability_finding favf
JOIN dim_asset da USING (asset_id)
JOIN dim_asset_host_name dahn ON da.asset_id = dahn.asset_id
JOIN dim_vulnerability dv USING (vulnerability_id)
JOIN dim_vulnerability_category dvc ON dv.vulnerability_id = dvc.vulnerability_id
Where da.host_name LIKE ‘computer01’ AND dvc.category_name = ‘CISA KEV’
GROUP BY dv.nexpose_id, dv.title, dv.severity, dv.cvss_vector, dv.riskscore, dv.exploits, dv.malware_kits
ORDER BY dv.severity ASC