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
1
WITH
2
3
   site_last_scan AS (
4
5
      SELECT site_id,
6
7
      (SELECT scan_id AS last_scan
8
9
       FROM dim_site_scan
10
11
          JOIN dim_scan USING (scan_id)
12
13
       WHERE site_id = ds.site_id
14
15
       ORDER BY finished DESC
16
17
       LIMIT 1) AS last_scan
18
19
      FROM dim_site ds
20
21
   ),
22
23
   site_previous_scan AS (
24
25
      SELECT site_id,
26
27
      (SELECT scan_id AS last_scan
28
29
       FROM dim_site_scan
30
31
          JOIN dim_scan USING (scan_id)
32
33
       WHERE site_id = ds.site_id AND scan_id NOT IN (SELECT last_scan FROM site_last_scan WHERE site_id = ds.site_id)
34
35
       ORDER BY finished DESC
36
37
       LIMIT 1) AS previous_scan
38
39
      FROM dim_site ds
40
41
   ),
42
43
last_asset_count AS (SELECT sls.site_id, count(fas.asset_id) AS last_asset_count
44
45
FROM site_last_scan AS sls
46
47
LEFT OUTER JOIN fact_asset_scan AS fas ON sls.last_scan = fas.scan_id
48
49
GROUP BY sls.site_id),
50
51
 
52
53
previous_asset_count AS (SELECT sps.site_id, count(fas.asset_id) AS previous_asset_count
54
55
FROM site_previous_scan AS sps
56
57
LEFT OUTER JOIN fact_asset_scan AS fas ON sps.previous_scan = fas.scan_id
58
59
GROUP BY sps.site_id),
60
61
 
62
63
last_vuln_count AS (SELECT sls.site_id, count(fasv.vulnerability_id) AS last_vuln_count
64
65
FROM site_last_scan AS sls
66
67
LEFT OUTER JOIN fact_asset_scan_vulnerability_finding AS fasv ON sls.last_scan = fasv.scan_id
68
69
GROUP BY sls.site_id),
70
71
 
72
73
previous_vuln_count AS (SELECT sps.site_id, count(fasv.vulnerability_id) AS previous_vuln_count
74
75
FROM site_previous_scan AS sps
76
77
LEFT OUTER JOIN fact_asset_scan_vulnerability_finding AS fasv ON sps.previous_scan = fasv.scan_id
78
79
GROUP BY sps.site_id),
80
81
 
82
83
asset_count_change AS (SELECT lac.site_id, (lac.last_asset_count - pac.previous_asset_count)  AS asset_count_change,
84
85
CASE WHEN (lac.last_asset_count - pac.previous_asset_count)  > 1000 THEN 'MEGA INCREASE'
86
87
WHEN (lac.last_asset_count - pac.previous_asset_count)  > 100 THEN 'SLIGHT INCREASE'
88
89
WHEN (lac.last_asset_count - pac.previous_asset_count)  > 10 THEN 'TINY INCREASE'
90
91
WHEN (lac.last_asset_count - pac.previous_asset_count)  < -1000 THEN 'MEGA DECREASE'
92
93
WHEN (lac.last_asset_count - pac.previous_asset_count)  < -100 THEN 'SLIGHT DECREASE'
94
95
WHEN (lac.last_asset_count - pac.previous_asset_count)  < -10 THEN 'TINY DECREASE'
96
97
ELSE 'IGNORE'
98
99
END AS asset_status
100
101
FROM last_asset_count AS lac
102
103
JOIN previous_asset_count AS pac ON lac.site_id = pac.site_id),
104
105
 
106
107
vuln_count_change AS (SELECT lac.site_id, (lac.last_vuln_count - pac.previous_vuln_count) AS vuln_count_change,
108
109
CASE WHEN (lac.last_vuln_count - pac.previous_vuln_count) > 1000 THEN 'MEGA INCREASE'
110
111
WHEN (lac.last_vuln_count - pac.previous_vuln_count) > 100 THEN 'SLIGHT INCREASE'
112
113
WHEN (lac.last_vuln_count - pac.previous_vuln_count) > 10 THEN 'TINY INCREASE'
114
115
WHEN (lac.last_vuln_count - pac.previous_vuln_count) < -1000 THEN 'MEGA DECREASE'
116
117
WHEN (lac.last_vuln_count - pac.previous_vuln_count) < -100 THEN 'SLIGHT DECREASE'
118
119
WHEN (lac.last_vuln_count - pac.previous_vuln_count) < -10 THEN 'TINY DECREASE'
120
121
ELSE 'IGNORE'
122
123
END AS vuln_status
124
125
FROM last_vuln_count AS lac
126
127
JOIN previous_vuln_count AS pac ON lac.site_id = pac.site_id)
128
129
 
130
131
SELECT 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_status
132
133
FROM last_asset_count AS lac
134
135
JOIN previous_asset_count AS pac ON lac.site_id = pac.site_id
136
137
JOIN last_vuln_count AS lvc ON lac.site_id = lvc.site_id
138
139
JOIN previous_vuln_count AS pvc ON lac.site_id = pvc.site_id
140
141
JOIN asset_count_change AS acc ON lac.site_id = acc.site_id
142
143
JOIN vuln_count_change AS vcc ON lac.site_id = vcc.site_id
144
145
JOIN dim_site AS ds ON lac.site_id = ds.site_id