Single Vulnerability Report

InsightVM SQL Queries
1

Trying to use the report example from SQL example - report on a single vulnerability | Nexpose Documentation

The output is blank. Does anyone more versed in the schema know if there was a change that perhaps broke this documented example?

3

Getting closer. The issue now is the vulnerability title shows up for every port and not just the ports that the vulnerability instance exists on…

SELECT da.ip_address					AS "Asset IP Address",
       da.host_name						AS "Asset Names",
	   dos.description					AS "Asset OS Name",
	   dv.title							AS "Vulnerability Title",
	   das.port							AS "Service Port"
	   
FROM fact_asset fa
	JOIN dim_asset da ON fa.asset_id = da.asset_id
	JOIN dim_operating_system dos ON da.operating_system_id = dos.operating_system_id
	JOIN fact_asset_vulnerability_instance favi ON da.asset_id = favi.asset_id
	JOIN dim_vulnerability dv ON favi.vulnerability_id = dv.vulnerability_id
	JOIN dim_asset_service das ON favi.service_id = das.service_id

WHERE dv.nexpose_id = 'ssl-self-signed-certificate'
4

Got it.

SELECT da.ip_address AS "Asset IP Address",
       da.host_name AS "Asset Names",
	   dos.description AS "Asset OS Name",
	   dv.title AS "Vulnerability Title",
	   favi.port AS "Service Port",
	   htmltotext(favi.proof) AS "Vulnerability Proof",
	   htmltotext(dv.description) AS "Vulnerability Description"
	   
FROM fact_asset fa
	JOIN dim_asset da ON fa.asset_id = da.asset_id
	JOIN dim_operating_system dos ON da.operating_system_id = dos.operating_system_id
	JOIN fact_asset_vulnerability_instance favi ON da.asset_id = favi.asset_id
	JOIN dim_vulnerability dv ON favi.vulnerability_id = dv.vulnerability_id

WHERE dv.nexpose_id = 'ssl-self-signed-certificate'
1 Like