SQL example - group by CVSS severity and split hostname

In cases where you need to present vulnerabilities grouped by CVSS severity, this example query can serve that purpose. It also splits the hostname.

sql
1
SELECT
2
3
da.ip_address AS "IP",
4
5
split_part(da.host_name, ',', 1) AS "Name 1",
6
7
split_part(da.host_name, ',', 2) AS "Name 2",
8
9
split_part(da.host_name, ',', 3) AS "Name 3",
10
11
dos. NAME AS "OS",
12
13
dv.title AS "Title",
14
15
CASE
16
17
WHEN dv.cvss_score = 10 THEN
18
19
'Critical'
20
21
WHEN dv.cvss_score BETWEEN 7
22
23
AND 9.9 THEN
24
25
'High'
26
27
WHEN dv.cvss_score BETWEEN 6.9
28
29
AND 4 THEN
30
31
'Medium'
32
33
WHEN dv.cvss_score BETWEEN 3.9
34
35
AND 1 THEN
36
37
'Low'
38
39
WHEN dv.cvss_score = 0 THEN
40
41
'Informational'
42
43
END AS "Severity",
44
45
dv.description AS "Description",
46
47
ds.estimate AS "Time to Fix",
48
49
ds.fix AS "Solution",
50
51
dvr.reference AS "Reference",
52
53
fasvi.proof AS "Proof",
54
55
fava.age_in_days AS "Age In Days"
56
57
FROM
58
59
fact_asset_vulnerability_instance AS fasvi
60
61
JOIN fact_asset_vulnerability_age AS fava ON fasvi.vulnerability_id = fava.vulnerability_id
62
63
JOIN dim_vulnerability AS dv ON fasvi.vulnerability_id = dv.vulnerability_id
64
65
JOIN dim_asset AS da ON fasvi.asset_id = da.asset_id
66
67
JOIN dim_vulnerability_reference AS dvr ON dv.vulnerability_id = dvr.vulnerability_id
68
69
JOIN dim_operating_system AS dos ON da.operating_system_id = dos.operating_system_id
70
71
JOIN dim_asset_vulnerability_solution AS davs ON davs.asset_id = da.asset_id
72
73
JOIN dim_solution AS ds ON ds.solution_id = davs.solution_id