Need Help: Identifying Single Patch Solutions for Vulnerabilities

InsightVM
1

Hello Rapid7 Community,

I am seeking assistance with creating a query that generates a report based on solutions and excludes superseded patches. The objective is to streamline our vulnerability management process by identifying the real fix for each vulnerability, rather than listing multiple suggested solutions that may be superseded by a single patch.

Specifically, I need a query that:

  1. Lists all vulnerabilities.
  2. Identifies the single patch that addresses each vulnerability, excluding any superseded patches.

This approach will help us analyze the effort required to implement fixes and understand how many vulnerabilities can be resolved by applying the relevant KB (Knowledge Base) articles.

Could anyone provide guidance or share a query that achieves this?

2

I dont know if this is exactly what you are wanting, but this does something similar to pull up the LATEST of the Solutions, and should be the superceded version..

Not promising that is it exactly, but you can try. This is for the warehouse.

also in there is your filterings such as:
– is commented out

WHERE ds.summary ILIKE '%Microsoft%'
    --OR ds.fix ILIKE '%Hotfix%'
    --OR ds.fix ILIKE '%KB%

WITH top_fixes AS (
    SELECT 
        dv.vulnerability_id,
        dv.nexpose_id AS vulnerability_nexpose_id,
        dv.title AS vulnerability_title,
        dshs.superceding_solution_id AS top_solution_id
    FROM dim_vulnerability dv
    JOIN dim_vulnerability_solution dvs 
        ON dv.vulnerability_id = dvs.vulnerability_id
    JOIN dim_solution_highest_supercedence dshs 
        ON dvs.solution_id = dshs.solution_id
    WHERE dshs.superceding_solution_id != dvs.solution_id
),
solution_metadata AS (
    SELECT 
        ds.solution_id,
        ds.nexpose_id AS solution_nexpose_id,
        ds.solution_type,
        ds.summary AS solution_summary,
        ds.fix AS solution_fix,
        ds.url AS solution_url
    FROM dim_solution ds
    -- Optional filter to match specific solutions (uncomment and modify as needed):
    WHERE ds.summary ILIKE '%Microsoft%'
        --OR ds.fix ILIKE '%Hotfix%'
        --OR ds.fix ILIKE '%KB%'
),
solution_impact AS (
    SELECT 
        dshs.superceding_solution_id AS solution_id,
        COUNT(DISTINCT dv.vulnerability_id) AS vulnerability_count
    FROM dim_vulnerability dv
    JOIN dim_vulnerability_solution dvs 
        ON dv.vulnerability_id = dvs.vulnerability_id
    JOIN dim_solution_highest_supercedence dshs 
        ON dvs.solution_id = dshs.solution_id
    WHERE dshs.superceding_solution_id != dvs.solution_id
    GROUP BY dshs.superceding_solution_id
)

SELECT 
    tf.vulnerability_nexpose_id AS vulnerability_id,
    tf.vulnerability_title,
    sm.solution_nexpose_id AS solution_id,
    sm.solution_type,
    sm.solution_summary,
    sm.solution_fix,
    sm.solution_url,
    si.vulnerability_count AS vulnerabilities_fixed_by_this_solution
FROM top_fixes tf
JOIN solution_metadata sm 
    ON tf.top_solution_id = sm.solution_id
JOIN solution_impact si 
    ON sm.solution_id = si.solution_id
ORDER BY tf.vulnerability_nexpose_id;