Good morning,
I’m having issues with trying to get a SQL report to render what I need properly.
This is for Microsoft Patch Tuesday vulnerabilities from the last 90 days.
This is the query below are there any suggestions on how to get this to pull faster and or even at all?
WITH last_scan AS (
SELECT
asset_id,
MAX(scan_finished) AS last_scan_date
FROM fact_asset_scan
GROUP BY asset_id
)
SELECT
UPPER(da.host_name) AS host_name,
STRING_AGG(da.ip_address, E'\n' ORDER BY da.ip_address) AS ip_address,
ls.last_scan_date
FROM fact_asset_vulnerability_instance favi
JOIN dim_asset da
ON favi.asset_id = da.asset_id
JOIN last_scan ls
ON da.asset_id = ls.asset_id
JOIN dim_vulnerability dv
ON favi.vulnerability_id = dv.vulnerability_id
WHERE dv.nexpose_id LIKE '%microsoft%'
AND dv.date_published BETWEEN '2026-01-01' AND '2026-06-03'
AND dv.nexpose_id NOT LIKE '%cve-2022-0001%'
AND dv.nexpose_id NOT LIKE '%office%'
AND dv.nexpose_id NOT LIKE '%edge%'
AND dv.nexpose_id NOT LIKE '%visual_studio%'
AND dv.nexpose_id NOT LIKE '%vs_code%'
GROUP BY
UPPER(da.host_name),
ls.last_scan_date
ORDER BY
UPPER(da.host_name) ASC;