Custom Report IVM

Hi, i wan to export a report in a csv that contains the summary of all Critical Vulnerabilities including vul title, proof, Vuln Solution, service port, asset name, asset ip address, Asset Location, Asset os Name, Asset Os Version, Vuln Age, Vuln CVSS score, vuln since, but when i try to export an error ocurres and stay on loop, some one have a functional sql ?

Hi
You could use this for example and adjust it to your needs. However i want to point out, that we are operating a dedicated postgresql server.

Select
Host(da.ip_address) AS ip,
da.host_name AS ci_name,
fasvi.asset_id,
da.os_description,
fasvi.date,
dv.title,
dv.vulnerability_id,
dv.nexpose_id,
dv.description,
htmlToText(fasvi.proof) as proof,
fasvi.key,
fasvi.service,
fasvi.port,
fasvi.protocol,
dv.date_published,
dv.date_added,
dv.severity,
da.last_assessed_for_vulnerabilities AS last_seen
FROM
fact_asset_vulnerability_instance fasvi
JOIN
dim_vulnerability dv USING(vulnerability_id)
JOIN
dim_asset da USING(asset_id)
WHERE
Host(da.ip_address) NOT LIKE ALL(ARRAY[‘IP Range’,‘IP Range’, etc.])
AND
dv.title NOT LIKE ‘Scanning Diagnostics:%’

I use this saved query (created on pgAdmin) for my PowerBI Dashboard.

Or here is another one:

Select
Host(da.ip_address) AS ip,
da.host_name AS asset,
da.os_vendor,
da.os_description AS os_version,
replace(dvul.title,‘,’,‘’) AS vulnerability_name,
CASE
WHEN dvc.category_name = ‘CISA KEV’ AND dvul.severity = ‘Moderate’ THEN ‘Critical’
WHEN dvc.category_name = ‘CISA KEV’ AND dvul.severity = ‘Severe’ THEN ‘Critical’
WHEN COALESCE(dvc.category_name, ‘Not Listed’) = ‘Not Listed’ AND dvul.severity = ‘Critical’ THEN ‘Severe’
ELSE dvul.severity
END AS risk,
dvul.risk_score,
COALESCE(dvc.category_name, ‘Not Listed’) AS category_name,
dvul.nexpose_id,
dvul.date_published,
dvul.date_added,
to_char(da.last_assessed_for_vulnerabilities, ‘YYYY-MM-DD’) AS last_seen
FROM
dim_asset da
INNER JOIN
fact_asset_vulnerability_instance fasvi USING(asset_id)
INNER JOIN
dim_vulnerability dvul USING(vulnerability_id)
LEFT JOIN (
Select dim_vulnerability_category.vulnerability_id, dim_vulnerability_category.category_name
FROM dim_vulnerability_category
WHERE dim_vulnerability_category.category_name = ‘CISA KEV’) as dvc
USING(vulnerability_id)
WHERE
date_trunc(‘day’, da.last_assessed_for_vulnerabilities) >= ‘2024-06-11’
AND
Host(da.ip_address) NOT LIKE ALL(ARRAY[‘IP Range’,‘IP Range’,‘10.10.10.%’])
AND
replace(dvul.title,‘,’,‘’) NOT LIKE ‘Scanning Diagnostics:%’

I use this one for our weekly exports. As we scan our environment on a weekly basis, we export all the results with this query, covering all the important information to create our reports. If you have any question regarding the query, feel free to ask.

Best regards

David