SQL query with all active vulnerabilities

Hello everyone,

We are trying to create a SQL query with all the active vulnerabilities in the moment where the report is created. We are trying but it’s being quite challenging, a lot of duplicates appear without apparent reason and also already remediated vulnerabilities appear. We have reached rapid7 support but we are not getting the answers we need…

So the question is, do you have a sql query for getting all active vulnerabilities in all instances? The fields we would need in this query are:
“Asset ID”,
“Asset Sites”, (all sites that one asset belongs to separated with comas)
“IP Address”,
“Vulnerability ID”,
“Vulnerability Title”,
“Vulnerability Description”,
“Severity Score”,
“Vulnerability Categories”,
“CVSS Score”,
“CVSSv3 Score”,
“Exploits Available”,
“Operating System”.

PD: we have tried with a basic template but we are missing important information as for example the vuln id (there is a vuln id in the basic template but it’s not the unique identifier for each vulnerability)

Thank you guys in advance! Regards,

Check out github. there’s a ton of examples there. https://github.com/rapid7/insightvm-sql-queries/tree/master/sql-query-export

Thanks slove, Unfortunately I have checked this queries before and there it is not what I’m looking for. Maybe I’m just not good enough at sql queries. I can share my query so you can see what I’m trying:

WITH assets AS (
SELECT asset_id, host_name,ip_address,sites,operating_system_id
FROM dim_asset

vulnerability_categories AS (
SELECT vulnerability_id, csv(category_name) as “categories”
FROM dim_vulnerability_category
GROUP BY vulnerability_id

vulnerabilities AS (
SELECT d_v.vulnerability_id, d_v.title, d_v.description, d_v.severity_score,d_v.cvss_score,d_v.cvss_v3_score,d_v.exploits, v_c.categories
FROM dim_vulnerability d_v
JOIN vulnerability_categories v_c ON (d_v.vulnerability_id = v_c.vulnerability_id)

scans AS (
SELECT asset_id, vulnerability_id, d_site.name as site_name, proof, last_assessed_for_vulnerabilities AS last_assessed, finished AS most_recent_scan
SELECT fa_instance.asset_id, vulnerability_id, max(dim_scan.scan_id) as scan_id, proofAsText(proof) as proof, last_assessed_for_vulnerabilities
FROM dim_scan
JOIN fact_asset_scan_vulnerability_instance fa_instance ON (fa_instance.scan_id = dim_scan.scan_id)
JOIN dim_asset da_asset ON (da_asset.asset_id = fa_instance.asset_id)
WHERE last_assessed_for_vulnerabilities >= current_date - interval ‘7 days’
GROUP BY fa_instance.asset_id, vulnerability_id, proof, last_assessed_for_vulnerabilities
) d_scan_recent
JOIN dim_scan d_scan ON (d_scan_recent.scan_id = d_scan.scan_id)
JOIN dim_site_scan d_s_scan ON (d_scan.scan_id = d_s_scan.scan_id)
JOIN dim_site d_site ON (d_site.site_id = d_s_scan.site_id)

SELECT d_scan.asset_id as “Asset ID”,
LOWER(d_a.host_name) as “Hostname”,
d_a.sites as “Asset Sites”,
d_a.ip_address as “IP Address”,
d_scan.vulnerability_id as “Vulnerability ID”,
d_scan.proof as “Proof”,
d_v.title as “Vulnerability Title”,
d_v.description as “Vulnerability Description”,
d_v.severity_score as “Severity Score”,
d_v.categories as “Vulnerability Categories”,
CAST (d_v.cvss_score AS DECIMAL (10,2)) as “CVSS Score”,
CAST (d_v.cvss_v3_score AS DECIMAL (10,2)) as “CVSSv3 Score”,
d_v.exploits as “Exploits Available”,
d_sol.fix as “Fix”,
d_os.name as “Operating System”,
–d_sol.summary as “Summary”,
–d_sol.additional_data as “Additional Data”,
d_scan.most_recent_scan as “Scan Date”,
d_scan.last_assessed AS “Last Assessed”,
d_scan.site_name as “Scan Site Name”
FROM scans d_scan
JOIN vulnerabilities d_v ON (d_v.vulnerability_id=d_scan.vulnerability_id)
JOIN assets d_a ON (d_a.asset_id=d_scan.asset_id)
JOIN dim_asset_vulnerability_best_solution d_best_sol ON
( d_best_sol.asset_id = d_scan.asset_id AND d_best_sol.vulnerability_id = d_scan.vulnerability_id)
JOIN dim_solution d_sol ON (d_sol.solution_id = d_best_sol.solution_id)
JOIN dim_operating_system d_os ON (d_os.operating_system_id = d_a.operating_system_id)

Just in case someone can help. Thanks!