Hello,
I am looking to see if within my sql query I could just run the query against the very last scan that was run on the asset group / asset / whatever is best.
Basically I’d like to query the last scan for any vulnerabilities with the word ‘exploit’ or ‘remote’ or ‘malware’ (for examples) in the title.
Thank you.
It is possible to query against the latest scan, though the query might be a little more complex. Here’s a piece of an example query that shows how to get the latest scan info.
WITH
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
) ...
You can see that it’s getting the latest scan for a site by checking for the max scan ID (AKA the largest scan ID in the table, since that will mean it’s the most recent one) and a scan status of “C” to ensure it’s complete.
From there, I believe you could join with the associated asset + vulnerability tables (like dim_vulnerability) to check the titles for certain words like you mentioned. Hopefully that helps. If you’re looking to see some more query examples, we’ve got several listed here when you scroll down in the left-hand menu.
1 Like