Is there a way to compare a set of assets from two different points in time? For example, on 4/1/2025 I want to see the vulnerabilities on a set of assets, and today I would like to see what has been remediated and what is outstanding based on what was found on 4/1/2025.
in the console, dashboard or via a SQL report
A console report or SQL report would be great.
Here is one you can try, should work on what you want, 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
1 Like
Curious about others results but no matter what I use for the dates, my results only show two statuses - Still Present or New.
so you want to actually see 3 statuses?
REMEDIATED
STILL PRESENT
NEW
1 Like
Thanks for the SQL. I will give it a try.
Yes, that would be great.
We would have to restructure the logic a little bit
NEW - Exists in current_vulns but not in past_vulns
STILL PRESENT - Exists in both past_vulns and current_vulns
REMEDIATED - Exists in past_vulns but NOT in current_vulns
I scraped this from another query, and reworked it from the last one, it validated but i have not tested it on any assets.
this should be close, check this and modify your dates accordingly.
WITH
-- Assets of interest
target_assets AS (
SELECT asset_id, ip_address, host_name, sites
FROM dim_asset
),
-- What vulnerabilities existed on a given past date
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 vulnerabilities (still present)
current_vulns AS (
SELECT asset_id, vulnerability_id
FROM fact_asset_vulnerability_instance
),
-- Combine all possible combinations and assign their status
combined_vulns AS (
SELECT asset_id, vulnerability_id, 'STILL PRESENT' AS status
FROM past_vulns
INTERSECT
SELECT asset_id, vulnerability_id, 'STILL PRESENT'
FROM current_vulns
UNION
SELECT asset_id, vulnerability_id, 'REMEDIATED' AS status
FROM past_vulns
EXCEPT
SELECT asset_id, vulnerability_id, 'REMEDIATED'
FROM current_vulns
UNION
SELECT asset_id, vulnerability_id, 'NEW' AS status
FROM current_vulns
EXCEPT
SELECT asset_id, vulnerability_id, 'NEW'
FROM past_vulns
),
-- Attach metadata for our reporting
vuln_metadata AS (
SELECT vulnerability_id, title, severity, cvss_v2_score
FROM dim_vulnerability
),
-- Optional: parse site name from `sites` text
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
)
-- Final output format
SELECT
ja.ip_address,
ja.host_name,
ja.site_name,
vm.title AS vulnerability_title,
vm.severity,
vm.cvss_v2_score,
cv.status
FROM combined_vulns cv
JOIN joined_assets ja USING (asset_id)
JOIN vuln_metadata vm USING (vulnerability_id)
ORDER BY ja.ip_address, vm.severity DESC;
The posted query has a section for a status of Remediated. I guess I just assumed that I would see that as a status.
see updated, if thats what you are looking for with the 3 statuses..
I’m still not able to see any remediated vulnerabilities, but I’ll keep experimenting with what you have provided. For some reason the following SQL is not returning any records for me.
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-04-30’
),
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
in the report section are you selecting your assets? Like Selecting BY SITE or DAG ?? and changing your Dates?
