Hi all,
I created the following SQL query. The purpose for the query is to get a CSV file with all found vulnerabilities on an asset and a separate line for each instance of that vulnerabily on the asset.So e.g.an asset has 4 log4j vulnerabilities than there should be 4 lines in the CSV.
The query:
SELECT DISTINCT
da.host_name AS “Asset Names”,
ds.name AS “Site Name”,
dv.title AS “Vulnerability Title”,
fasvi.port AS “Service Port”,
round(dv.cvss_score::numeric, 2) AS “Vulnerability CVSS Score”,
round(dv.riskscore::numeric, 1) AS “Vulnerability Risk Score”,
dv.severity AS “Vulnerability Severity Level”,
dv.nexpose_id AS “Vulnerability ID”,
dv.exploits AS “Exploit Count”,
dv.malware_kits AS “Malware Kit Count”,
fa.last_scan_id AS “Scan ID”,
da.ip_address AS “Asset IP Address”,
round(dv.cvss_v3_score::numeric, 2) AS “Vulnerability CVSSv3 Score”,
htmlToText(fasvi.proof) AS “Vulnerability Proof”
FROM dim_asset da
JOIN fact_asset AS fa ON fa.asset_id = da.asset_id
JOIN fact_asset_scan_vulnerability_finding AS fasvf ON fasvf.scan_id = fa.last_scan_id and fasvf.asset_id = da.asset_id
JOIN fact_asset_scan_vulnerability_instance AS fasvi ON fasvi.scan_id = fa.last_scan_id and fasvi.asset_id = da.asset_id and fasvi.vulnerability_id = fasvf.vulnerability_id
JOIN dim_vulnerability AS dv ON dv.vulnerability_id = fasvf.vulnerability_id
JOIN dim_site_scan AS dss ON dss.scan_id = fa.last_scan_id
JOIN dim_site AS ds on ds.site_id = dss.site_id
The query itself works and produces what I want. However, it runs a very, very long time. Does anybody see anything that would improve the generation time?
Thanks.
