SQL Query Example: New and Remediated Vulnerabilities

Document created by synapsr on Feb 5, 2015
Version 1Show Document
  • View in full screen mode

Here's another query to present your vulnerability data and trending.  This query provides an easy way to compare the last two scans (current and previous) at the vulnerability level.

Results include two types of vulnerabilities: new (exist in current, but not previous) and remediated (exist in previous, but not current).

 

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.

 

 

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,

previousScan (asset_id) AS baseline_scan,

lastScan (asset_id) 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

2 people found this helpful

Attachments

    Outcomes