I highly recommend getting the data warehouse setup. It will make querying your data so much easier. But this should get you what you need.
You did not mention how/if you were filtering your report, so this does not take into account any filtering needs. You can modify the first CTE and add the junk dimensions based on your scope. Understanding the reporting data model: Dimensions | Nexpose Documentation
WITH
assets AS ( --get assets and vulnerability metadata ##add dim_scope_tag here if filtering report by tags
SELECT favi.asset_id, favi.vulnerability_id, htmlToText(favi.proof, false) "proof",
CASE WHEN favi.port < 0 THEN NULL ELSE favi.port END AS "port", ds.name "service", dp.name "protocol"
FROM fact_asset_vulnerability_instance favi
JOIN dim_service ds USING(service_id)
JOIN dim_protocol dp USING(protocol_id)
),
solutions AS ( --get the superceded solutions for each id and age of vulns
SELECT a.*, htmlToText(ds.fix) "solution", ds.summary AS "solution_summary", trunc(fava.age_in_days) AS "vulnerability_age", first_discovered
FROM assets a
JOIN dim_asset_vulnerability_best_solution USING(asset_id, vulnerability_id)
JOIN dim_solution ds USING(solution_id)
JOIN fact_asset_vulnerability_age fava USING(asset_id, vulnerability_id)
),
vuln_category AS ( --each id can be in multiple categories, so combine them to filter out
SELECT vulnerability_id, STRING_AGG(category_name, ', ') AS "category_name"
FROM dim_vulnerability_category
GROUP BY 1
),
vulns AS ( --get more info about the vulnerabilities
SELECT s.*, da.host_name, da.ip_address, da.last_assessed_for_vulnerabilities AS last_scan_date,
dv.nexpose_id, dv.severity AS "r7_severity", cvss_v3_score, dve.skill_level AS "exploit_skill_level", dv.title, htmlToText(dv.description) "description"
FROM solutions s
JOIN dim_vulnerability dv USING(vulnerability_id)
JOIN dim_vulnerability_exploit dve USING(vulnerability_id)
JOIN vuln_category vc USING(vulnerability_id)
JOIN dim_asset da USING(asset_id)
WHERE cvss_v3_score >= 8 AND vc.category_name NOT LIKE '%Microsoft Patch%'
)
--final formatting
SELECT host_name, ip_address, last_scan_date, nexpose_id, r7_severity, cvss_v3_score, exploit_skill_level, title, description, solution_summary, solution
FROM vulns