SQL example - delta since the last scan
This example query compares results from the last scan and provides them with configurable thresholds.
Feel free to adjust the threshold terminology to anything you see fit.
sql
1WITH23site_last_scan AS (45SELECT site_id,67(SELECT scan_id AS last_scan89FROM dim_site_scan1011JOIN dim_scan USING (scan_id)1213WHERE site_id = ds.site_id1415ORDER BY finished DESC1617LIMIT 1) AS last_scan1819FROM dim_site ds2021),2223site_previous_scan AS (2425SELECT site_id,2627(SELECT scan_id AS last_scan2829FROM dim_site_scan3031JOIN dim_scan USING (scan_id)3233WHERE site_id = ds.site_id AND scan_id NOT IN (SELECT last_scan FROM site_last_scan WHERE site_id = ds.site_id)3435ORDER BY finished DESC3637LIMIT 1) AS previous_scan3839FROM dim_site ds4041),4243last_asset_count AS (SELECT sls.site_id, count(fas.asset_id) AS last_asset_count4445FROM site_last_scan AS sls4647LEFT OUTER JOIN fact_asset_scan AS fas ON sls.last_scan = fas.scan_id4849GROUP BY sls.site_id),50515253previous_asset_count AS (SELECT sps.site_id, count(fas.asset_id) AS previous_asset_count5455FROM site_previous_scan AS sps5657LEFT OUTER JOIN fact_asset_scan AS fas ON sps.previous_scan = fas.scan_id5859GROUP BY sps.site_id),60616263last_vuln_count AS (SELECT sls.site_id, count(fasv.vulnerability_id) AS last_vuln_count6465FROM site_last_scan AS sls6667LEFT OUTER JOIN fact_asset_scan_vulnerability_finding AS fasv ON sls.last_scan = fasv.scan_id6869GROUP BY sls.site_id),70717273previous_vuln_count AS (SELECT sps.site_id, count(fasv.vulnerability_id) AS previous_vuln_count7475FROM site_previous_scan AS sps7677LEFT OUTER JOIN fact_asset_scan_vulnerability_finding AS fasv ON sps.previous_scan = fasv.scan_id7879GROUP BY sps.site_id),80818283asset_count_change AS (SELECT lac.site_id, (lac.last_asset_count - pac.previous_asset_count) AS asset_count_change,8485CASE WHEN (lac.last_asset_count - pac.previous_asset_count) > 1000 THEN 'MEGA INCREASE'8687WHEN (lac.last_asset_count - pac.previous_asset_count) > 100 THEN 'SLIGHT INCREASE'8889WHEN (lac.last_asset_count - pac.previous_asset_count) > 10 THEN 'TINY INCREASE'9091WHEN (lac.last_asset_count - pac.previous_asset_count) < -1000 THEN 'MEGA DECREASE'9293WHEN (lac.last_asset_count - pac.previous_asset_count) < -100 THEN 'SLIGHT DECREASE'9495WHEN (lac.last_asset_count - pac.previous_asset_count) < -10 THEN 'TINY DECREASE'9697ELSE 'IGNORE'9899END AS asset_status100101FROM last_asset_count AS lac102103JOIN previous_asset_count AS pac ON lac.site_id = pac.site_id),104105106107vuln_count_change AS (SELECT lac.site_id, (lac.last_vuln_count - pac.previous_vuln_count) AS vuln_count_change,108109CASE WHEN (lac.last_vuln_count - pac.previous_vuln_count) > 1000 THEN 'MEGA INCREASE'110111WHEN (lac.last_vuln_count - pac.previous_vuln_count) > 100 THEN 'SLIGHT INCREASE'112113WHEN (lac.last_vuln_count - pac.previous_vuln_count) > 10 THEN 'TINY INCREASE'114115WHEN (lac.last_vuln_count - pac.previous_vuln_count) < -1000 THEN 'MEGA DECREASE'116117WHEN (lac.last_vuln_count - pac.previous_vuln_count) < -100 THEN 'SLIGHT DECREASE'118119WHEN (lac.last_vuln_count - pac.previous_vuln_count) < -10 THEN 'TINY DECREASE'120121ELSE 'IGNORE'122123END AS vuln_status124125FROM last_vuln_count AS lac126127JOIN previous_vuln_count AS pac ON lac.site_id = pac.site_id)128129130131SELECT ds.name, lac.last_asset_count, pac.previous_asset_count, lvc.last_vuln_count, pvc.previous_vuln_count, acc.asset_count_change, acc.asset_status, vcc.vuln_count_change, vcc.vuln_status132133FROM last_asset_count AS lac134135JOIN previous_asset_count AS pac ON lac.site_id = pac.site_id136137JOIN last_vuln_count AS lvc ON lac.site_id = lvc.site_id138139JOIN previous_vuln_count AS pvc ON lac.site_id = pvc.site_id140141JOIN asset_count_change AS acc ON lac.site_id = acc.site_id142143JOIN vuln_count_change AS vcc ON lac.site_id = vcc.site_id144145JOIN dim_site AS ds ON lac.site_id = ds.site_id
Did this page help you?