PostgreSQL query for CVE and owner tag - dishplate/blog GitHub Wiki

SELECT DISTINCT 
dta.owner "Owner", 
da.host_name "Hostname", 
da.ip_address "IP Address", 
dv.title "Vulnerability Title", 
dv.cvss_score "CVSS Score", 
htmlToText(favi.proof) "Proof", 
favi.port "Port", 
dacs.aggregated_credential_status_description 
"Credential Status", 
ds.summary "Solution", 
ds.url "URL", 
da.last_assessed_for_vulnerabilities AS "Last Scan Finished"
FROM dim_asset da
	LEFT JOIN (SELECT dta.asset_id "asset_id", dt.tag_name "owner"
		   FROM dim_tag_asset dta 
			JOIN dim_tag dt ON dta.tag_id = dt.tag_id
		   WHERE dt.tag_type = 'OWNER') dta ON da.asset_id = dta.asset_id
	JOIN fact_asset fa ON da.asset_id = fa.asset_id
	JOIN dim_aggregated_credential_status dacs ON fa.aggregated_credential_status_id = dacs.aggregated_credential_status_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_vulnerability_best_solution davbs ON favi.vulnerability_id = davbs.vulnerability_id AND favi.asset_id = davbs.asset_id
	JOIN dim_solution ds ON davbs.solution_id = ds.solution_id
WHERE dv.title ILIKE '%CVE-2020-1472%' OR dv.title ILIKE '%CVE-2021-44228%' OR dv.title ILIKE '%CVE-2023-34362%' OR dv.title ILIKE '%CVE-2020-1472%' OR dv.title ILIKE '%CVE-2023-23397%' OR dv.title ILIKE '%CVE-2020-14724%' OR dv.title ILIKE '%CVE-2020-14725%'