Top 25 Remediations by Risk export to CSV - SQL query

jgarcia · August 31, 2023, 6:42pm

I’m looking for a SQL query to create a Top 25 Remediations by Risk report in a CSV file format.

Appreciate your help.

landon_dalke1 · September 6, 2023, 3:43pm

rapid7/insightvm-sql-queries/blob/master/sql-query-export/Top-25-Remediation-Report.sql

-- sql version of in product report top 25 remediations  can also adjust number by the number listed below
-- Copy the SQL query below
SELECT
    DISTINCT ds.summary AS "Solution",
    proofAsText(ds.fix) AS "Fix",
    ds.estimate AS "Estimate",
    dv.title AS "Vulnerability Title",
    da.ip_address AS "IP Address",
    da.host_name AS "Host Name",
    dacs.aggregated_credential_status_description AS "Access Level",
    round(dv.riskscore) AS "Risk Score",
    dv.severity AS "Severity",
    da.last_assessed_for_vulnerabilities AS "last discovered"
FROM
    fact_remediation(25, 'riskscore DESC') fr
    JOIN dim_solution ds ON (fr.solution_id = ds.solution_id)
    JOIN dim_asset_vulnerability_solution davs ON (fr.solution_id = davs.solution_id)
    JOIN dim_vulnerability dv USING (vulnerability_id)
    JOIN dim_asset da USING (asset_id)
    JOIN fact_asset fa USING (asset_id)

This file has been truncated. show original

jjbaker · September 24, 2023, 11:38pm

The query linked to seems to be broken: https://github.com/rapid7/insightvm-sql-queries/blob/master/sql-query-export/Top-25-Remediation-Report.sql

If you try and validate that in insightvm it returns get invalid query.

You have to go to the history in github and revert to the original commit to get a working query. The version updated on Jun 15 2023 with comment “Updated formatting of queries and corrected some text” looks broken.

landon_dalke1 · September 25, 2023, 9:47pm

Try it without the comments at the top. The comments don’t work for some reason. I just tested it and it validated. Thanks!