i did something similar for someone else on here that we use, this may or may not work for your use case, and i dont know if you ever got what you need as i see this is over a year ago.
You can run this in the console reporting under the SQL report section, you will want to select your Assets, IP, SITE or AG based on what your looking for..
WITH
-- 1. Map vulnerabilities to severity
vuln_scores AS (
SELECT
vulnerability_id,
CASE
WHEN cvss_score = 0 THEN 'None'
WHEN cvss_score BETWEEN 0.1 AND 3.9 THEN 'Low'
WHEN cvss_score BETWEEN 4.0 AND 6.9 THEN 'Medium'
WHEN cvss_score BETWEEN 7.0 AND 8.9 THEN 'High'
WHEN cvss_score BETWEEN 9.0 AND 10.0 THEN 'Critical'
ELSE 'Unknown'
END AS severity
FROM dim_vulnerability
),
-- 2. New vulnerabilities (first seen in the last 90 days)
new_vulns AS (
SELECT
va.vulnerability_id,
vs.severity
FROM fact_asset_vulnerability_age va
JOIN vuln_scores vs ON va.vulnerability_id = vs.vulnerability_id
WHERE va.first_discovered >= current_date - interval '90 days'
),
-- 3. Outstanding vulnerabilities (currently still present)
outstanding_vulns AS (
SELECT
vi.vulnerability_id,
vs.severity
FROM fact_asset_vulnerability_instance vi
JOIN vuln_scores vs ON vi.vulnerability_id = vs.vulnerability_id
),
-- 4. Remediated vulnerabilities (first discovered in last 90 days, but no longer present)
remediated_vulns AS (
SELECT
va.vulnerability_id,
vs.severity
FROM fact_asset_vulnerability_age va
JOIN vuln_scores vs ON va.vulnerability_id = vs.vulnerability_id
LEFT JOIN fact_asset_vulnerability_instance vi
ON va.asset_id = vi.asset_id AND va.vulnerability_id = vi.vulnerability_id
WHERE va.first_discovered >= current_date - interval '90 days'
AND vi.vulnerability_id IS NULL
)
-- 5. Aggregate by severity
SELECT
vs.severity,
COUNT(DISTINCT nv.vulnerability_id) AS new_vulnerabilities,
COUNT(DISTINCT rv.vulnerability_id) AS remediated_vulnerabilities,
COUNT(DISTINCT ov.vulnerability_id) AS outstanding_vulnerabilities
FROM vuln_scores vs
LEFT JOIN new_vulns nv ON vs.vulnerability_id = nv.vulnerability_id
LEFT JOIN remediated_vulns rv ON vs.vulnerability_id = rv.vulnerability_id
LEFT JOIN outstanding_vulns ov ON vs.vulnerability_id = ov.vulnerability_id
GROUP BY vs.severity
ORDER BY
CASE vs.severity
WHEN 'Critical' THEN 1
WHEN 'High' THEN 2
WHEN 'Medium' THEN 3
WHEN 'Low' THEN 4
WHEN 'None' THEN 5
ELSE 6
END;
here is a different one using points in time…
run in the Console in the report section using the SQL query option, you will want to select your assets manually, by site, IP, AG etc.
You will need to modify your dates in query based on what you are wanting.
WITH target_assets AS (
SELECT asset_id, ip_address, host_name, sites
FROM dim_asset
),
past_vulns AS (
SELECT asset_id, vulnerability_id
FROM fact_asset_vulnerability_age
WHERE first_discovered <= DATE '2025-01-01'
AND most_recently_discovered >= DATE '2025-01-01'
),
current_vulns AS (
SELECT asset_id, vulnerability_id
FROM fact_asset_vulnerability_instance
),
joined_assets AS (
SELECT ta.*, ds.name AS site_name
FROM target_assets ta
LEFT JOIN dim_site ds ON POSITION(ds.name IN ta.sites) > 0
),
vuln_metadata AS (
SELECT vulnerability_id, title, severity, cvss_v2_score
FROM dim_vulnerability
)
-- Remediated vulns
SELECT
ja.ip_address AS ip,
ja.host_name AS hostname,
ja.site_name,
vm.cvss_v2_score,
vm.severity,
vm.title AS vulnerability,
'Remediated' AS status
FROM past_vulns p
JOIN joined_assets ja ON p.asset_id = ja.asset_id
LEFT JOIN current_vulns c ON p.asset_id = c.asset_id AND p.vulnerability_id = c.vulnerability_id
JOIN vuln_metadata vm ON p.vulnerability_id = vm.vulnerability_id
WHERE c.vulnerability_id IS NULL
UNION ALL
-- Still present
SELECT
ja.ip_address,
ja.host_name,
ja.site_name,
vm.cvss_v2_score,
vm.severity,
vm.title,
'Still Present' AS status
FROM past_vulns p
JOIN current_vulns c ON p.asset_id = c.asset_id AND p.vulnerability_id = c.vulnerability_id
JOIN joined_assets ja ON p.asset_id = ja.asset_id
JOIN vuln_metadata vm ON p.vulnerability_id = vm.vulnerability_id
UNION ALL
-- New since X date
SELECT
ja.ip_address,
ja.host_name,
ja.site_name,
vm.cvss_v2_score,
vm.severity,
vm.title,
'New Since X Date' AS status
FROM current_vulns c
LEFT JOIN past_vulns p ON c.asset_id = p.asset_id AND c.vulnerability_id = p.vulnerability_id
JOIN joined_assets ja ON c.asset_id = ja.asset_id
JOIN vuln_metadata vm ON c.vulnerability_id = vm.vulnerability_id
WHERE p.vulnerability_id IS NULL