After working with Rapid7 for a couple weeks of back and forth, I was able to get a SQL version of the top 25 remediations report that doesn’t have blank assets for certain vulnerabilities that had assets in the regular report.
Enjoy!
WITH solution_asset_mapping AS (
-- 1. Rollup solutions mapped to active asset vulnerability instances
SELECT
dvs.solution_id,
favi.asset_id
FROM dim_vulnerability_solution dvs
JOIN fact_asset_vulnerability_instance favi
ON favi.vulnerability_id = dvs.vulnerability_id
UNION
-- 2. Best solution mappings calculated by InsightVM
SELECT
solution_id,
asset_id
FROM dim_asset_vulnerability_best_solution
UNION
-- 3. Direct asset-vulnerability-solution records
SELECT
solution_id,
asset_id
FROM dim_asset_vulnerability_solution
)
SELECT
ds.summary AS solution_summary,
proofAsText(ds.fix) AS solution_steps,
fr.assets AS asset_count,
fr.vulnerabilities AS vulns_count,
fr.riskscore,
fr.exploits AS exploits_count,
fr.malware_kits AS malware_count,
array_to_string(
array_agg(DISTINCT da.ip_address) FILTER (WHERE da.ip_address IS NOT NULL),
', '
) AS ip_addresses,
array_to_string(
array_agg(DISTINCT da.host_name) FILTER (WHERE da.host_name IS NOT NULL),
', '
) AS host_names
FROM fact_remediation(2147483647, 'riskscore DESC') AS fr
JOIN dim_solution AS ds
ON ds.solution_id = fr.solution_id
LEFT JOIN solution_asset_mapping sam
ON sam.solution_id = fr.solution_id
LEFT JOIN dim_asset AS da
ON da.asset_id = sam.asset_id
GROUP BY
fr.solution_id,
ds.summary,
ds.fix,
fr.assets,
fr.vulnerabilities,
fr.riskscore,
fr.exploits,
fr.malware_kits
ORDER BY
fr.riskscore DESC
LIMIT 25;