Hello,
I am trying to pull the most recent scan data from our environment using the latest scan results of the targeted assets. For some reason, the query below is pulling data from the assets’ scan history and this includes vulnerabilities that were already remediated which we do not want. I went through the GitHub repo and did not see a way to pull solely the latest/most recent vulnerabilities from completed scans for our assets. I found this ticket here of someone having the same issue to me however, I am having trouble integrating this into my existing query.
Reference Forum:
SQL Query
WITH os_business_units AS (
SELECT DISTINCT dta.asset_id, string_agg(dt.tag_name, ', ') AS “tag_name”
FROM dim_tag dt
JOIN dim_tag_asset dta ON dt.tag_id = dta.tag_id
WHERE dt.tag_name iLIKE ‘OS Business Unit:%’
GROUP BY dta.asset_id
),
os_owner AS (
SELECT DISTINCT dta.asset_id, string_agg(dt.tag_name, ', ') AS “tag_name”
FROM dim_tag dt
JOIN dim_tag_asset dta ON dt.tag_id = dta.tag_id
WHERE dt.tag_name iLIKE ‘OS Owner:%’
GROUP BY dta.asset_id
),
os_leadership AS (
SELECT DISTINCT dta.asset_id, string_agg(dt.tag_name, ', ') AS “tag_name”
FROM dim_tag dt
JOIN dim_tag_asset dta ON dt.tag_id = dta.tag_id
WHERE dt.tag_name iLIKE ‘OS Leadership:%’
GROUP BY dta.asset_id
),
last_scan_for_site AS (
SELECT site_id, MAX(scan_id) AS “scan_id”
FROM dim_site_scan
JOIN dim_scan USING (scan_id)
WHERE status_id = ‘C’
GROUP BY site_id
)
SELECT
da.host_name AS “Asset Name”,
da.ip_address AS “IP Address”,
da.sites AS “Site”,
dos.description AS “Operating System”,
CASE WHEN dv.title iLIKE ‘%CVE%’ THEN substring(dv.title, ‘CVE-\d{4}-\d{4,7}’)
ELSE CASE WHEN dv.title iLIKE ‘MS%’ THEN substring(dv.title, 1, 8)
ELSE ‘N/A’
END
END AS “CVE ID”,
dv.title AS “Vulnerability”,
proofAsText(fasvi.proof) AS “Proof”,
dv.severity AS “Severity”,
ds.summary AS “Remediation Information”,
min(fasvi.date) :: date AS “Discovery Date”,
NOW() :: date - min(fasvi.date) :: date AS “Days Open”,
SUBSTRING(da.last_assessed_for_vulnerabilities::varchar(255), 1, 10) AS “Last Scan”,
ob.tag_name AS “Business Unit”,
ol.tag_name AS “Leadership”,
oo.tag_name AS “Remediation Owner”,
NULL AS “Jira ID”
FROM
fact_asset_scan_vulnerability_instance fasvi
JOIN dim_asset da ON fasvi.asset_id = da.asset_id
JOIN dim_vulnerability dv ON dv.vulnerability_id = fasvi.vulnerability_id
JOIN dim_vulnerability_category dvc ON dv.vulnerability_id = dvc.vulnerability_id
JOIN dim_operating_system dos ON dos.operating_system_id = da.operating_system_id
JOIN dim_vulnerability_solution dvs ON dvs.vulnerability_id = dv.vulnerability_id
JOIN dim_tag_asset dta ON da.asset_id = dta.asset_id
LEFT JOIN dim_tag dt ON dt.tag_id = dta.tag_id
LEFT JOIN os_business_units ob ON da.asset_id = ob.asset_id
LEFT JOIN os_leadership ol ON da.asset_id = ol.asset_id
LEFT JOIN os_owner oo ON da.asset_id = oo.asset_id
LEFT JOIN last_scan_for_site lsfs ON dsi.site_id = ds.site_id
LEFT JOIN dim_solution ds ON dvs.solution_id = ds.solution_id
WHERE
dv.date_published < (NOW() - INTERVAL ‘60 days’)
AND
((dv.cvss_v3_score IS NULL AND dv.cvss_v2_score >= 7)
OR
(dv.cvss_v3_score >= 7)
)
AND dos.asset_type NOT iLike ‘Workstation’
AND dos.family NOT iLIKE ‘Windows’
AND da.last_assessed_for_vulnerabilities >= (NOW() - INTERVAL ‘30 days’)
Group BY
da.host_name,
da.ip_address,
da.sites,
dos.description,
dv.title,
dv.severity,
fasvi.proof,
ds.summary,
ds.applies_to,
da.last_assessed_for_vulnerabilities,
ob.tag_name,
oo.tag_name,
ol.tag_name