SQL Query Example: Vulnerability Count by Asset - New, Existing and Remediated

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

Sometimes you just need vulnerability data presented a certain way and once again, SQL queries to the rescue.  This query provides an easy way to compare the last two scans (current and previous) at the asset level. Results include the current number of vulnerabilities, the number of new vulnerabilities that exist in current, but not previous scan and the number of remediated vulnerabilities that exist in previous, but not current scan.

Columns include: IP address, hostname, previous scan date/time, current scan date/time, # of existing vulns, # of new vulns and # of remediated vulns.

 

 

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 da

) 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

),

existing_vulns as (

SELECT

av.asset_id,

COUNT (av.vulnerability_id) AS existing_vulns

FROM

assets_vulns AS av

WHERE

av.baseline = 'Same'

GROUP BY

av.asset_id

),

new_vulns as (

SELECT

av.asset_id,

COUNT (av.vulnerability_id) AS new_vulns

FROM

assets_vulns AS av

WHERE

av.baseline = 'New'

GROUP BY

av.asset_id

),

 

remediated_vulns AS (

SELECT

av.asset_id,

COUNT (av.vulnerability_id) AS remediated_vulns

FROM

assets_vulns AS av

WHERE

av.baseline = 'Old'

GROUP BY

av.asset_id

)

 

SELECT

da.ip_address AS ip_address, da.host_name AS hostname, bsd.finished as baseline_scan_datetime, csd.finished as current_scan_datetime,

COALESCE (ev.existing_vulns, 0) AS existing_vulns,

COALESCE (rv.remediated_vulns, 0) AS remediated_vulns,

COALESCE (nv.new_vulns, 0) AS new_vulns

FROM

existing_vulns AS ev

FULL JOIN remediated_vulns AS rv ON ev.asset_id = rv.asset_id

FULL JOIN new_vulns AS nv ON ev.asset_id = nv.asset_id

JOIN dim_asset as da ON da.asset_id = ev.asset_id

LEFT JOIN baseline_scan_date bsd ON bsd.asset_id = da.asset_id

LEFT JOIN current_scan_date csd ON csd.asset_id = da.asset_id

3 people found this helpful

Attachments

    Outcomes