SQL example - new and remediated vulnerabilities

This example query presents your vulnerability and trending data and provides an easy way to compare the last two scans (current and previous) at the vulnerability level.

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

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.


sql
1
with assets_vulns as (
2
3
SELECT
4
5
fasv.asset_id,
6
7
fasv.vulnerability_id,
8
9
baselineComparison (fasv.scan_id, current_scan) AS baseline,
10
11
s.baseline_scan,
12
13
s.current_scan
14
15
FROM
16
17
fact_asset_scan_vulnerability_instance fasv
18
19
JOIN (
20
21
SELECT
22
23
asset_id,
24
25
previousScan (asset_id) AS baseline_scan,
26
27
lastScan (asset_id) AS current_scan
28
29
FROM
30
31
dim_asset
32
33
) s ON s.asset_id = fasv.asset_id
34
35
AND (
36
37
fasv.scan_id = s.baseline_scan
38
39
OR fasv.scan_id = s.current_scan
40
41
)
42
43
GROUP BY
44
45
fasv.asset_id,
46
47
fasv.vulnerability_id,
48
49
s.baseline_scan,
50
51
s.current_scan
52
53
HAVING
54
55
(
56
57
baselineComparison (fasv.scan_id, current_scan) = 'Same'
58
59
)
60
61
OR (
62
63
baselineComparison (fasv.scan_id, current_scan) = 'New'
64
65
)
66
67
OR (
68
69
baselineComparison (fasv.scan_id, current_scan) = 'Old'
70
71
)
72
73
),
74
75
baseline_scan_date as (
76
77
SELECT
78
79
av.asset_id,
80
81
finished
82
83
FROM assets_vulns av
84
85
LEFT JOIN dim_scan ds ON ds.scan_id = av.baseline_scan
86
87
GROUP BY av.asset_id, finished
88
89
),
90
91
 
92
93
current_scan_date as (
94
95
SELECT
96
97
av.asset_id,
98
99
finished
100
101
FROM assets_vulns av
102
103
LEFT JOIN dim_scan ds ON ds.scan_id = av.current_scan
104
105
GROUP BY av.asset_id, finished
106
107
),
108
109
new_vulns as (
110
111
SELECT
112
113
av.asset_id,
114
115
av.vulnerability_id,
116
117
COUNT (av.vulnerability_id) AS new_vulns
118
119
FROM
120
121
assets_vulns AS av
122
123
WHERE
124
125
av.baseline = 'New'
126
127
GROUP BY
128
129
av.asset_id,
130
131
av.vulnerability_id
132
133
),
134
135
remediated_vulns AS (
136
137
SELECT
138
139
av.asset_id,
140
141
av.vulnerability_id,
142
143
COUNT (av.vulnerability_id) AS remediated_vulns
144
145
FROM
146
147
assets_vulns AS av
148
149
WHERE
150
151
av.baseline = 'Old'
152
153
GROUP BY
154
155
av.asset_id,
156
157
av.vulnerability_id
158
159
 
160
161
),
162
163
vuln_exploit_count AS (
164
165
SELECT
166
167
CASE WHEN ec1.vulnerability_id IS NOT NULL THEN ec1.vulnerability_id ELSE ec2.vulnerability_id END as vulnerability_id, metasploit, exploitdb
168
169
FROM
170
171
(SELECT
172
173
av.vulnerability_id,
174
175
COUNT(dve.source) as metasploit
176
177
FROM assets_vulns av
178
179
JOIN dim_vulnerability_exploit dve ON av.vulnerability_id = dve.vulnerability_id
180
181
WHERE dve.source = 'Metasploit'
182
183
GROUP BY
184
185
av.vulnerability_id
186
187
) ec1
188
189
 
190
191
FULL JOIN
192
193
 
194
195
(SELECT
196
197
av.vulnerability_id,
198
199
COUNT(dve.source) as exploitdb
200
201
FROM assets_vulns av
202
203
JOIN dim_vulnerability_exploit dve ON av.vulnerability_id = dve.vulnerability_id
204
205
WHERE dve.source = 'Exploit DB'
206
207
GROUP BY
208
209
av.vulnerability_id
210
211
) ec2
212
213
 
214
215
ON ec2.vulnerability_id = ec1.vulnerability_id
216
217
)
218
219
 
220
221
SELECT
222
223
'Remediated' as status,
224
225
da1.ip_address AS ip_address,
226
227
da1.host_name AS hostname,
228
229
bsd.finished as baseline_scan_datetime,
230
231
csd.finished as current_scan_datetime,
232
233
dv1.vulnerability_id,
234
235
dv1.title,
236
237
CAST(dv1.cvss_score as decimal(10,2)) as cvss_score,
238
239
CAST(dv1.riskscore as decimal(10,0)) as riskscore,
240
241
dv1.malware_kits,
242
243
CASE WHEN vec.metasploit IS NULL THEN 0 ELSE vec.metasploit END as metasploit,
244
245
CASE WHEN vec.exploitdb IS NULL THEN 0 ELSE vec.exploitdb END as exploitdb
246
247
FROM
248
249
remediated_vulns rv
250
251
JOIN dim_asset da1 ON da1.asset_id = rv.asset_id
252
253
LEFT JOIN baseline_scan_date bsd ON bsd.asset_id = da1.asset_id
254
255
LEFT JOIN current_scan_date csd ON csd.asset_id = da1.asset_id
256
257
JOIN dim_vulnerability dv1 ON dv1.vulnerability_id = rv.vulnerability_id
258
259
LEFT JOIN vuln_exploit_count vec ON vec.vulnerability_id = rv.vulnerability_id
260
261
 
262
263
UNION ALL
264
265
 
266
267
SELECT
268
269
'New' as status,
270
271
da2.ip_address AS ip_address,
272
273
da2.host_name AS hostname,
274
275
bsd.finished as baseline_scan_datetime,
276
277
csd.finished as current_scan_datetime,
278
279
dv2.vulnerability_id,
280
281
dv2.title,
282
283
CAST(dv2.cvss_score as decimal(10,2)) as cvss_score,
284
285
CAST(dv2.riskscore as decimal(10,0)) as riskscore,
286
287
dv2.malware_kits,
288
289
CASE WHEN vec.metasploit IS NULL THEN 0 ELSE vec.metasploit END as metasploit,
290
291
CASE WHEN vec.exploitdb IS NULL THEN 0 ELSE vec.exploitdb END as exploitdb
292
293
FROM
294
295
new_vulns nv
296
297
JOIN dim_asset as da2 ON da2.asset_id = nv.asset_id
298
299
LEFT JOIN baseline_scan_date bsd ON bsd.asset_id = da2.asset_id
300
301
LEFT JOIN current_scan_date csd ON csd.asset_id = da2.asset_id
302
303
JOIN dim_vulnerability dv2 ON dv2.vulnerability_id = nv.vulnerability_id
304
305
LEFT JOIN vuln_exploit_count vec ON vec.vulnerability_id = nv.vulnerability_id
306
307
ORDER BY status DESC, ip_address, hostname, title