SQL example - delta since the last scan

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