Query to fetch all vulnerabilities remediated for last month

InsightVM SQL Queries
1

Hi,
I am looking to prepare a query where I can find all the vulnerabilities remediated on all the assets in past 1/2/3… months. I know a few methods like finding the vulnerability count on each asset on each day of the month and then manually subtract past date count from next date count to get the remediations on that day.
But this idea is not very efficient or precise plus it also includes lot of manual work.
So, do we have any query/report which can fetch this data -count of all remediations performed on all assets for whole month?

1 Like
2

Our example query here would probably be a good start

Just remove the NEW section to focus only on remediated. It’s comparing two scans so you could change out the scan dates that it’s using to use the latest and then a scan from roughly a month ago, or 2 months ago, or 3 months ago.

1 Like
3

Hi John,

I tried using the query to fetch a comparison report for many sites but it doesn’t yield any result for me except the headers. I am not sure if I have to manually enter the dates in the query:

baseline_scan_date as (
SELECT
av.asset_id,
finished
FROM assets_vulns av
LEFT JOIN dim_scan ds ON ds.scan_id = av.baseline_scan
GROUP BY av.asset_id, finished
),

current_scan_date AS (
SELECT
av.asset_id,
finished
FROM assets_vulns av
LEFT JOIN dim_scan ds ON ds.scan_id = av.current_scan
GROUP BY av.asset_id, finished

I am assuming, if I select a site to run this SQL query on, it will compare with the last scan and provide me a result of all the remediated vulnerabilities between the dates.

Regards

4

The baseline and current scans are pulled in from the temporary table above those two “asset_vulns” and it is using the previousScan and lastScan from the dim_asset table. So it doesn’t really care about what site you scope it to specifically. Scoping to a specific site just tells it what assets will be included in the scope but if an asset is in multiple sites and being scanned in those multiple sites this report will be agnostic to the site the scans ran in and only looks at the actual data from those scan ids. Given that you scoped it to a specific site it could be the reason why you’re getting no results. Basically one of those scans was not for this site so as far as the report goes, it does not exist.

1 Like
5

Thanks John. Would I be able to define “two dates” in this SQL query to fetch a report of remediated vulnerabilities? I have 10 thousand devices, so probably the report may take longer.

6

Technically speaking, yes you could compare two different scans instead of the previous and last scan.

However, the previous and last scans are coming directly from the asset page as it knows those scan IDs at all times. To compare different scan IDs you would need to find the specific scan IDs and replace them in the SQL Query.

Now you could do another SQL query to get those IDs if you want or you could go into a site and view the scan history in the GUI. When you highlight over a Scan name you should see a little URL display in the bottom left hand of the screen and at the end of the URL should display the ID for that specific scan.

1 Like