SQL example - report on a single vulnerability

sql
1
WITH remediations AS (
2
3
    SELECT DISTINCT fr.solution_id AS ultimate_soln_id, summary, fix, estimate, riskscore, dshs.solution_id AS solution_id
4
5
    FROM fact_remediation(10,'riskscore DESC') fr
6
7
    JOIN dim_solution ds USING (solution_id)
8
9
    JOIN dim_solution_highest_supercedence dshs ON (fr.solution_id = dshs.superceding_solution_id AND ds.solution_id = dshs.superceding_solution_id)
10
11
),
12
13
 
14
15
assets AS (
16
17
    SELECT DISTINCT asset_id, host_name, ip_address
18
19
    FROM dim_asset
20
21
    GROUP BY asset_id, host_name, ip_address
22
23
)
24
25
 
26
27
SELECT DISTINCT
28
29
   csv(DISTINCT dv.nexpose_id) AS "Vulnerability InsightVM ID",
30
31
   host_name AS "Asset Hostname", ip_address AS "Asset IP",
32
33
   round(sum(dv.riskscore)) AS "Asset Risk",
34
35
   summary AS "Solution",
36
37
   fix as "Fix"
38
39
 
40
41
FROM remediations r
42
43
   JOIN dim_asset_vulnerability_solution dvs USING (solution_id)
44
45
   JOIN dim_vulnerability dv USING (vulnerability_id)
46
47
   JOIN assets USING (asset_id)
48
49
 
50
51
WHERE dv.nexpose_id = 'linuxrpm-rhsa-2015-0090'
52
53
 
54
55
GROUP BY r.riskscore, host_name, ip_address, asset_id, summary, fix
56
57
ORDER BY "Asset Risk" DESC