SQL example - report on a single vulnerability

This example query reports on one vulnerability, f5-big-ip-cve-2015-4852, across ALL assets.

SQL
1
SELECT DISTINCT
2
 csv(DISTINCT dv.nexpose_id) AS "Vulnerability InsightVM ID",
3
 host_name AS "Asset Hostname",
4
 ip_address AS "Asset IP",
5
 round(sum(dv.riskscore)) AS "Asset Risk",
6
 summary AS "Solution",
7
 fix as "Fix"
8
FROM dim_asset
9
 JOIN dim_asset_vulnerability_solution USING(asset_id)
10
 JOIN dim_vulnerability dv USING(vulnerability_id)
11
 JOIN dim_solution ds USING (solution_id)
12
 JOIN dim_solution_highest_supercedence dshs ON ds.solution_id = dshs.superceding_solution_id
13
WHERE dv.nexpose_id = 'f5-big-ip-cve-2015-4852'
14
GROUP BY host_name, ip_address, asset_id, summary, fix