SQL example - report on a single vulnerability
This example query reports on one vulnerability across ALL assets.
sql
1WITH remediations AS (23SELECT DISTINCT fr.solution_id AS ultimate_soln_id, summary, fix, estimate, riskscore, dshs.solution_id AS solution_id45FROM fact_remediation(10,'riskscore DESC') fr67JOIN dim_solution ds USING (solution_id)89JOIN dim_solution_highest_supercedence dshs ON (fr.solution_id = dshs.superceding_solution_id AND ds.solution_id = dshs.superceding_solution_id)1011),12131415assets AS (1617SELECT DISTINCT asset_id, host_name, ip_address1819FROM dim_asset2021GROUP BY asset_id, host_name, ip_address2223)24252627SELECT DISTINCT2829csv(DISTINCT dv.nexpose_id) AS "Vulnerability InsightVM ID",3031host_name AS "Asset Hostname", ip_address AS "Asset IP",3233round(sum(dv.riskscore)) AS "Asset Risk",3435summary AS "Solution",3637fix as "Fix"38394041FROM remediations r4243JOIN dim_asset_vulnerability_solution dvs USING (solution_id)4445JOIN dim_vulnerability dv USING (vulnerability_id)4647JOIN assets USING (asset_id)48495051WHERE dv.nexpose_id = 'linuxrpm-rhsa-2015-0090'52535455GROUP BY r.riskscore, host_name, ip_address, asset_id, summary, fix5657ORDER BY "Asset Risk" DESC
Did this page help you?