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
Hi David, thank you for your help, i using a new one sql query but at the moment that export it duplicates the values, i don’t know where I’m failing.
SELECT
dsite.name AS “Site Name”,
da.ip_address AS “IP Address”,
da.host_name AS “Host Name”,
dv.title AS “Vulnerabiltiy”,
dv.description AS “Vulnerability Description”,
dv.severity AS “Severity”,
dv.cvss_score AS “CVSS”,
ds.url AS “URL”,
ds.solution_type AS “Solution Type”,
ds.summary AS “Solution”,
fv.first_discovered AS “First Discovered”
FROM
fact_asset_vulnerability_instance AS fav
JOIN fact_vulnerability AS fv ON fav.vulnerability_id = fv.vulnerability_id
JOIN dim_vulnerability AS dv ON fav.vulnerability_id = dv.vulnerability_id
JOIN dim_site_asset AS dsa ON fav.asset_id = dsa.asset_id
JOIN dim_site AS dsite ON dsa.site_id = dsite.site_id
JOIN dim_ASset AS da ON fav.asset_id = da.asset_id
JOIN dim_vulnerability_solution AS dvs ON fv.vulnerability_id = dvs.vulnerability_id
JOIN dim_solution AS ds ON dvs.solution_id = ds.solution_id
GROUP by
dsite.name,
da.ip_address,
da.host_name,
dv.title,
dv.severity,
dv.cvss_score,
ds.solution_type,
ds.summary,
dv.description,
ds.url,
fv.first_discovered
ORDER by
dsite.name ASC