SQL query how to only lists the most recent entry from a combination of fields

InsightVM SQL Queries
1

Some background on this topic. We’ve got quite a number of exceptions and I got the question if I could produce a report that lists all assets involved in each exception. So here is how I approached this:
SELECT dv.nexpose_id, dv.title, da.ip_address, da.host_name, ds.name, fasvie.port, fasvie.scan_id, extract(‘week’ from dsc.started) as scan_week, dve.expiration_date
FROM fact_asset_scan_vulnerability_instance_excluded fasvie
JOIN dim_vulnerability dv USING (vulnerability_id)
JOIN dim_asset da USING (asset_id)
JOIN dim_service ds USING (service_id)
JOIN dim_scan dsc USING (scan_id)
JOIN dim_vulnerability_exception dve USING (vulnerability_exception_id)
ORDER BY da.ip_address, dv.nexpose_id, fasvie.scan_id DESC
This produces the information that I want. The problem however is that fact_asset_scan_vulnerability_instance_excluded is cumulative. So, depending on how many times an asset is scanned, I get multiple entries of an asset_id/vulnerability_id combination, each with an unique scan_id
Is there a way I can clean up the output so only the asset_id/vulnerability_id combination with the highest scan_id (being the most recent scan) ends up in the output

2

Hi, @achmea_nexpose_support_team! I think that what you might be looking for is the max function, which will get the max value for that column in your select. Then you can just GROUP BY the rest of the fields to get the results. You’d need to do it for both the scan related columns, scan ID of course, but also the scan_week since those values could very well be different. It would like this:

SELECT dv.nexpose_id,
       dv.title,
       da.ip_address,
       da.host_name,
       ds.name,
       fasvie.port,
       max(fasvie.scan_id)                   AS scan_id,
       max(extract('week' from dsc.started)) as scan_week,
       dve.expiration_date
FROM fact_asset_scan_vulnerability_instance_excluded fasvie
         JOIN dim_vulnerability dv USING (vulnerability_id)
         JOIN dim_asset da USING (asset_id)
         JOIN dim_service ds USING (service_id)
         JOIN dim_scan dsc USING (scan_id)
         JOIN dim_vulnerability_exception dve USING (vulnerability_exception_id)
GROUP BY dv.nexpose_id, dv.title, da.ip_address, da.host_name, ds.name, fasvie.port, dve.expiration_date
ORDER BY da.ip_address, dv.nexpose_id DESC;
1 Like
3

Hi Tyler,
That’s it. Just gave it a try and it does exactly what I had in mind.
Thank you very much for helping out.

Cheers, Ron

2 Likes