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