SQL example - new and remediated vulnerabilities
This example query presents your vulnerability and trending data and provides an easy way to compare the last two scans (current and previous) at the vulnerability level.
Results include two types of vulnerabilities: new (exists in the current, but not previous scan) and remediated (exist in the previous, but not current scan).
Columns include: IP address, hostname, status (Remediated or New), previous scan date/time, current scan date/time, vulnerability ID, vulnerability title, CVSS, risk score, # of malware kits, # of Metasploit modules and # of ExploitDB modules.
sql
1with assets_vulns as (23SELECT45fasv.asset_id,67fasv.vulnerability_id,89baselineComparison (fasv.scan_id, current_scan) AS baseline,1011s.baseline_scan,1213s.current_scan1415FROM1617fact_asset_scan_vulnerability_instance fasv1819JOIN (2021SELECT2223asset_id,2425previousScan (asset_id) AS baseline_scan,2627lastScan (asset_id) AS current_scan2829FROM3031dim_asset3233) s ON s.asset_id = fasv.asset_id3435AND (3637fasv.scan_id = s.baseline_scan3839OR fasv.scan_id = s.current_scan4041)4243GROUP BY4445fasv.asset_id,4647fasv.vulnerability_id,4849s.baseline_scan,5051s.current_scan5253HAVING5455(5657baselineComparison (fasv.scan_id, current_scan) = 'Same'5859)6061OR (6263baselineComparison (fasv.scan_id, current_scan) = 'New'6465)6667OR (6869baselineComparison (fasv.scan_id, current_scan) = 'Old'7071)7273),7475baseline_scan_date as (7677SELECT7879av.asset_id,8081finished8283FROM assets_vulns av8485LEFT JOIN dim_scan ds ON ds.scan_id = av.baseline_scan8687GROUP BY av.asset_id, finished8889),90919293current_scan_date as (9495SELECT9697av.asset_id,9899finished100101FROM assets_vulns av102103LEFT JOIN dim_scan ds ON ds.scan_id = av.current_scan104105GROUP BY av.asset_id, finished106107),108109new_vulns as (110111SELECT112113av.asset_id,114115av.vulnerability_id,116117COUNT (av.vulnerability_id) AS new_vulns118119FROM120121assets_vulns AS av122123WHERE124125av.baseline = 'New'126127GROUP BY128129av.asset_id,130131av.vulnerability_id132133),134135remediated_vulns AS (136137SELECT138139av.asset_id,140141av.vulnerability_id,142143COUNT (av.vulnerability_id) AS remediated_vulns144145FROM146147assets_vulns AS av148149WHERE150151av.baseline = 'Old'152153GROUP BY154155av.asset_id,156157av.vulnerability_id158159160161),162163vuln_exploit_count AS (164165SELECT166167CASE WHEN ec1.vulnerability_id IS NOT NULL THEN ec1.vulnerability_id ELSE ec2.vulnerability_id END as vulnerability_id, metasploit, exploitdb168169FROM170171(SELECT172173av.vulnerability_id,174175COUNT(dve.source) as metasploit176177FROM assets_vulns av178179JOIN dim_vulnerability_exploit dve ON av.vulnerability_id = dve.vulnerability_id180181WHERE dve.source = 'Metasploit'182183GROUP BY184185av.vulnerability_id186187) ec1188189190191FULL JOIN192193194195(SELECT196197av.vulnerability_id,198199COUNT(dve.source) as exploitdb200201FROM assets_vulns av202203JOIN dim_vulnerability_exploit dve ON av.vulnerability_id = dve.vulnerability_id204205WHERE dve.source = 'Exploit DB'206207GROUP BY208209av.vulnerability_id210211) ec2212213214215ON ec2.vulnerability_id = ec1.vulnerability_id216217)218219220221SELECT222223'Remediated' as status,224225da1.ip_address AS ip_address,226227da1.host_name AS hostname,228229bsd.finished as baseline_scan_datetime,230231csd.finished as current_scan_datetime,232233dv1.vulnerability_id,234235dv1.title,236237CAST(dv1.cvss_score as decimal(10,2)) as cvss_score,238239CAST(dv1.riskscore as decimal(10,0)) as riskscore,240241dv1.malware_kits,242243CASE WHEN vec.metasploit IS NULL THEN 0 ELSE vec.metasploit END as metasploit,244245CASE WHEN vec.exploitdb IS NULL THEN 0 ELSE vec.exploitdb END as exploitdb246247FROM248249remediated_vulns rv250251JOIN dim_asset da1 ON da1.asset_id = rv.asset_id252253LEFT JOIN baseline_scan_date bsd ON bsd.asset_id = da1.asset_id254255LEFT JOIN current_scan_date csd ON csd.asset_id = da1.asset_id256257JOIN dim_vulnerability dv1 ON dv1.vulnerability_id = rv.vulnerability_id258259LEFT JOIN vuln_exploit_count vec ON vec.vulnerability_id = rv.vulnerability_id260261262263UNION ALL264265266267SELECT268269'New' as status,270271da2.ip_address AS ip_address,272273da2.host_name AS hostname,274275bsd.finished as baseline_scan_datetime,276277csd.finished as current_scan_datetime,278279dv2.vulnerability_id,280281dv2.title,282283CAST(dv2.cvss_score as decimal(10,2)) as cvss_score,284285CAST(dv2.riskscore as decimal(10,0)) as riskscore,286287dv2.malware_kits,288289CASE WHEN vec.metasploit IS NULL THEN 0 ELSE vec.metasploit END as metasploit,290291CASE WHEN vec.exploitdb IS NULL THEN 0 ELSE vec.exploitdb END as exploitdb292293FROM294295new_vulns nv296297JOIN dim_asset as da2 ON da2.asset_id = nv.asset_id298299LEFT JOIN baseline_scan_date bsd ON bsd.asset_id = da2.asset_id300301LEFT JOIN current_scan_date csd ON csd.asset_id = da2.asset_id302303JOIN dim_vulnerability dv2 ON dv2.vulnerability_id = nv.vulnerability_id304305LEFT JOIN vuln_exploit_count vec ON vec.vulnerability_id = nv.vulnerability_id306307ORDER BY status DESC, ip_address, hostname, title
Did this page help you?