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