Hi Ashley,
As @holly_wilsey said, this is A LOT of data and it’s very likely that the query may not finish given a large data set (lots of vulnerabilities). Depending on the size of your environment, scoping the query to specific assets/groups will be critical for getting it to complete.
WITH tags AS (
SELECT fa.asset_id AS asset_id, dt.tag_type, csv(dt.tag_name) AS tags
FROM fact_asset fa
LEFT JOIN dim_tag_asset dta ON fa.asset_id = dta.asset_id
LEFT JOIN dim_tag dt ON dta.tag_id = dt.tag_id
GROUP BY fa.asset_id, dt.tag_name, dt.tag_type
),
refs AS (
SELECT dv.vulnerability_id AS vulnerability_id, dvr.source, csv(dvr.reference) AS refs
FROM dim_vulnerability dv
LEFT JOIN dim_vulnerability_reference dvr on dv.vulnerability_id = dvr.vulnerability_id
WHERE dvr.source = 'CVE'
OR dvr.source = 'URL'
GROUP BY dv.vulnerability_id, dvr.source
)
SELECT da.asset_id,
CASE WHEN tags.tag_type = 'OWNER' THEN tags.tags END AS "Asset Owner",
CASE WHEN tags.tag_type = 'CRITICALITY' THEN tags.tags END AS "Asset Criticality",
CASE WHEN tags.tag_type = 'CUSTOM' THEN tags.tags END AS "Custom Tag",
da.ip_address AS "Asset IP Address",
csv(DISTINCT dahn.host_name) AS "Asset Names",
dos.description AS "Asset OS Name",
ROUND(dv.cvss_score::numeric, 1) AS "Vulnerability CVSS Score",
ROUND(dv.cvss_v3_score::numeric, 1) AS "Vulnerability CVSSv3 Score",
ROUND(fa.riskscore) AS "Asset Risk Score",
COUNT(dve.exploit_id) AS "Exploit Count",
dv.severity AS "Vulnerability Severity Level",
dv.date_published AS "Vulnerability Published Date",
fava.first_discovered AS "Vulnerable Since",
fava.age AS "Vulnerability Age",
dv.date_modified AS "Vulnerability Modified Date",
CASE WHEN refs.source = 'CVE' THEN refs.refs END AS "Vulnerability CVE IDs",
dv.title AS "Vulnerability Title",
htmltotext(favi.proof) AS "Vulnerability Proof",
ds.summary AS "Vulnerability Solution",
htmltotext(dv.description) AS "Vulnerability Description",
CASE WHEN refs.source = 'URL' THEN refs.refs END AS "Vulnerability Additional URLs",
dsvc.name AS "Service Name",
das.port AS "Service Port",
dp.name AS "Service Protocol"
FROM fact_asset fa
JOIN dim_asset da ON fa.asset_id = da.asset_id
JOIN tags ON tags.asset_id = da.asset_id
JOIN dim_asset_host_name dahn ON da.asset_id = dahn.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 fact_asset_vulnerability_age fava ON dv.vulnerability_id = fava.vulnerability_id
LEFT JOIN refs ON dv.vulnerability_id = refs.vulnerability_id
LEFT JOIN dim_vulnerability_exploit dve ON dve.vulnerability_id = dv.vulnerability_id
LEFT JOIN dim_service dsvc ON favi.service_id = dsvc.service_id
LEFT JOIN dim_asset_service das ON favi.service_id = das.service_id
LEFT JOIN dim_protocol dp ON dp.protocol_id = das.protocol_id
LEFT JOIN dim_asset_vulnerability_best_solution davbs
ON da.asset_id = davbs.asset_id AND dv.vulnerability_id = davbs.vulnerability_id
LEFT JOIN dim_solution ds ON davbs.solution_id = ds.solution_id
GROUP BY da.asset_id, tags.tag_type, tags.tags, dos.description, dv.cvss_score, dv.cvss_v3_score, fa.riskscore,
dv.severity, dv.date_published, fava.first_discovered, fava.age, dv.date_modified, refs.refs, refs.source,
dv.title, favi.proof, ds.summary, dv.description, dsvc.name, das.port, dp.name