SQL example - report on a single vulnerability

This example query reports on one vulnerability across ALL assets.

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