Hi Rapid7 Geniuses,
I need some help to create a SQL query to report on assets linked to a specific site (by ID or Name) which are vulnerable to a specific CVE ID (Nexpose ID). I would like to return an output with the following information: Vulnerability ID, Vulnerability Title (or Description), CVSS, Severity, Risk Score, Asset Hostname, Asset IP, Vulnerability Status (Vulnerable or Invulnerable). I need this report for my custom InsightConnect workflow.
Thank you,
Delvain Mbina
I want to add the remediation step (summary) and fix in the output table. This is the query that I have so far:
SELECT da.host_name AS “Asset Hostname”, da.ip_address AS “Asset IP”, csv(DISTINCT 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”, dso.summary AS “summary”, htmlToText(dso.fix) AS “fix”
FROM fact_remediation(10, ‘riskscore DESC’) AS fr
JOIN dim_solution AS dso ON fr.solution_id = dso.solution_id
JOIN dim_asset_vulnerability_solution davs ON fr.solution_id = davs.solution_id
JOIN dim_asset AS da ON davs.asset_id = da.asset_id
JOIN dim_vulnerability dv ON davs.solution_id = dv.vulnerability_id
JOIN dim_vulnerability_reference dvr ON dv.vulnerability_id = dvr.vulnerability_id
JOIN dim_site_asset dsa ON da.asset_id = dsa.asset_id
JOIN dim_site ds ON dsa.site_id = ds.site_id
Where ds.site_id = 3 AND (dvr.source = ‘CVE’ AND dvr.reference LIKE ‘%CVE-2021-36958%’)
GROUP BY da.ip_address, da.host_name, dv.title, dv.severity, dv.cvss_vector, dv.riskscore, dso.summary, dso.fix