SQL Query for Remediated and New Vulnerabilities Between Two Specific Scans

There is a SQL Query sample in InsightVM documentation to report on remediated and new vulnerabilities from the last scan taking the previous as a baseline.

My question is:
Has anyone come across an iteration of the same query for exporting remediated and new vulnerabilities between two specific scans (selected either manually or as part of the query code)?

Thank you very much in advance.
Jose

4 Likes

Hello,
i’ve got same problem some time ago and found a solution. Long time has been passed since I’ve written it thus maybe it contains some errors but should work. If you encounter any problems then I’ll check it:

WITH last_scans AS (
SELECT * FROM (
SELECT name as site_name, site_id, scan_name, scan_id, ROW_NUMBER() OVER (PARTITION BY name ORDER BY scan_id DESC) AS scan_number
FROM dim_site_scan
JOIN dim_scan USING (scan_id)
JOIN dim_site USING (site_id)
#last two specific scans with particular name
WHERE status_id = 'C' AND (scan_name = 'weekly' ) AND name IN (
    'Linux Srv',
    'Windows Srv')
) scans
WHERE scan_number <= 2
),
 
first_scan_for_site AS (
SELECT scan_number, scan_id, scan_name, site_id, site_name
FROM last_scans
WHERE scan_number = 1
),
 
second_scan_for_site AS (
SELECT scan_number, scan_id, scan_name, site_id, site_name
FROM last_scans
WHERE scan_number = 2
),

first_scan_for_asset AS (
SELECT site_id, asset_id, scan_id
FROM first_scan_for_site
JOIN dim_site_asset USING (site_id)
),

second_scan_for_asset AS (
SELECT site_id, asset_id, scan_id
FROM second_scan_for_site
JOIN dim_site_asset USING (site_id)
),

current_snapshot AS (
SELECT site_id, scan_id AS current_scan, asset_id
FROM dim_site
JOIN dim_site_asset USING (site_id)
JOIN first_scan_for_site fsfa USING (site_id)
JOIN dim_asset_scan USING (asset_id, scan_id)
),

previous_snapshot AS (
SELECT site_id, scan_id AS previous_scan, asset_id
FROM dim_site
JOIN dim_site_asset USING (site_id)
JOIN second_scan_for_site ssfa USING (site_id)
JOIN dim_asset_scan USING (asset_id, scan_id)
),

asset_scans AS (
SELECT DISTINCT asset_id, current_scan, previous_scan FROM current_snapshot
LEFT JOIN previous_snapshot USING (asset_id)
),

baseline_vulns AS (
SELECT fasf.asset_id, fasf.vulnerability_id, baselineComparison(fasf.scan_id, current_scan) AS baseline, s.previous_scan, s.current_scan
FROM fact_asset_scan_vulnerability_finding fasf
JOIN (
SELECT asset_id, previous_scan, current_scan FROM asset_scans
) s ON s.asset_id = fasf.asset_id
AND (
fasf.scan_id = s.previous_scan OR fasf.scan_id = s.current_scan
)
GROUP BY fasf.asset_id, fasf.vulnerability_id, s.previous_scan, s.current_scan
)

SELECT ls.site_name, da.ip_address, da.host_name, dv.title, dv.severity_score, baseline FROM baseline_vulns
JOIN dim_asset da USING (asset_id)
JOIN dim_vulnerability dv USING (vulnerability_id)
JOIN last_scans ls ON baseline_vulns.previous_scan = ls.scan_id OR baseline_vulns.current_scan = ls.scan_id
1 Like

Thanks for your suggestion. I was able to modify the sample query to fit my needs. I am going to posted below.

This can be easily achieved modifying just a couple of conditions in the query. I was able to get the results wanted just replacing the function in the sample query for “scanAsOfDate”.

                         scanAsOfDate (asset_id, 'mm/dd/yyyy') AS baseline_scan,
                         scanAsOfDate (asset_id, 'mm/dd/yyyy') AS current_scan

Just substitute ‘mm/dd/yyyy’ for both the baseline (or initial) scan, and the current (or last) scan.


WITH assets_vulns
AS (SELECT fasv.asset_id,
fasv.vulnerability_id,
Baselinecomparison (fasv.scan_id, current_scan) AS baseline,
s.baseline_scan,
s.current_scan
FROM fact_asset_scan_vulnerability_instance fasv
JOIN (SELECT asset_id,
scanAsOfDate (asset_id, ‘mm/dd/yyyy’) AS baseline_scan,
scanAsOfDate (asset_id, ‘mm/dd/yyyy’) AS current_scan
FROM dim_asset) s
ON s.asset_id = fasv.asset_id
AND ( fasv.scan_id = s.baseline_scan
OR fasv.scan_id = s.current_scan )
GROUP BY fasv.asset_id,
fasv.vulnerability_id,
s.baseline_scan,
s.current_scan
HAVING ( Baselinecomparison (fasv.scan_id, current_scan) = ‘Same’ )
OR ( Baselinecomparison (fasv.scan_id, current_scan) = ‘New’ )
OR ( Baselinecomparison (fasv.scan_id, current_scan) = ‘Old’ ))
,
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),
new_vulns
AS (SELECT av.asset_id,
av.vulnerability_id,
Count (av.vulnerability_id) AS new_vulns
FROM assets_vulns AS av
WHERE av.baseline = ‘New’
GROUP BY av.asset_id,
av.vulnerability_id),
remediated_vulns
AS (SELECT av.asset_id,
av.vulnerability_id,
Count (av.vulnerability_id) AS remediated_vulns
FROM assets_vulns AS av
WHERE av.baseline = ‘Old’
GROUP BY av.asset_id,
av.vulnerability_id),
vuln_exploit_count
AS (SELECT CASE
WHEN ec1.vulnerability_id IS NOT NULL THEN
ec1.vulnerability_id
ELSE ec2.vulnerability_id
END AS vulnerability_id,
metasploit,
exploitdb
FROM (SELECT av.vulnerability_id,
Count(dve.source) AS metasploit
FROM assets_vulns av
JOIN dim_vulnerability_exploit dve
ON av.vulnerability_id = dve.vulnerability_id
WHERE dve.source = ‘Metasploit’
GROUP BY av.vulnerability_id) ec1
FULL JOIN (SELECT av.vulnerability_id,
Count(dve.source) AS exploitdb
FROM assets_vulns av
JOIN dim_vulnerability_exploit dve
ON av.vulnerability_id =
dve.vulnerability_id
WHERE dve.source = ‘Exploit DB’
GROUP BY av.vulnerability_id) ec2
ON ec2.vulnerability_id = ec1.vulnerability_id) SELECT
‘Remediated’ AS status,
da1.ip_address AS ip_address,
da1.host_name AS hostname,
bsd.finished AS baseline_scan_datetime,
csd.finished AS current_scan_datetime,
dv1.vulnerability_id,
dv1.title,
Cast(dv1.cvss_score AS DECIMAL(10, 2)) AS cvss_score,
Cast(dv1.riskscore AS DECIMAL(10, 0)) AS riskscore,
dv1.malware_kits,
CASE
WHEN vec.metasploit IS NULL THEN 0
ELSE vec.metasploit
END AS metasploit,
CASE
WHEN vec.exploitdb IS NULL THEN 0
ELSE vec.exploitdb
END AS exploitdb
FROM remediated_vulns rv
JOIN dim_asset da1
ON da1.asset_id = rv.asset_id
LEFT JOIN baseline_scan_date bsd
ON bsd.asset_id = da1.asset_id
LEFT JOIN current_scan_date csd
ON csd.asset_id = da1.asset_id
JOIN dim_vulnerability dv1
ON dv1.vulnerability_id = rv.vulnerability_id
LEFT JOIN vuln_exploit_count vec
ON vec.vulnerability_id = rv.vulnerability_id
UNION ALL
SELECT ‘New’ AS status,
da2.ip_address AS ip_address,
da2.host_name AS hostname,
bsd.finished AS baseline_scan_datetime,
csd.finished AS current_scan_datetime,
dv2.vulnerability_id,
dv2.title,
Cast(dv2.cvss_score AS DECIMAL(10, 2)) AS cvss_score,
Cast(dv2.riskscore AS DECIMAL(10, 0)) AS riskscore,
dv2.malware_kits,
CASE
WHEN vec.metasploit IS NULL THEN 0
ELSE vec.metasploit
END AS metasploit,
CASE
WHEN vec.exploitdb IS NULL THEN 0
ELSE vec.exploitdb
END AS exploitdb
FROM new_vulns nv
JOIN dim_asset AS da2
ON da2.asset_id = nv.asset_id
LEFT JOIN baseline_scan_date bsd
ON bsd.asset_id = da2.asset_id
LEFT JOIN current_scan_date csd
ON csd.asset_id = da2.asset_id
JOIN dim_vulnerability dv2
ON dv2.vulnerability_id = nv.vulnerability_id
LEFT JOIN vuln_exploit_count vec
ON vec.vulnerability_id = nv.vulnerability_id
ORDER BY status DESC,
ip_address,
hostname,
title

1 Like

Hi @jjnaranjo,

I tried executing the query you have shared and I got the below error. Could you please help me fix it?

image

Thanks,
Parth

This looks like it could potentially be an issue with the quotation marks used in the query. Sometimes when you copy a query from one place to another, the resulting text can contain “smart quotes”, which will cause the query to fail. The error message in your image seems to point to this since it says “error at or near ‘DB’”, which has quotes around it in the query.

The solution would be to re-type the quotes in the query to remove the smart quotes and allow it to validate correctly.

Would you mind sharing the final query?