SQL Query - Solutions

InsightVM SQL Queries
2

Hello Steve!

A while back, I was struggling with getting a CSV format version of the top remediations report, and I wrote a query that came pretty close, but the sql report has some undocumented limitations, and I ended up just writing a python script to convert a HTML version of the report into a CSV file. The script is linked here: Top25 remediations report, but as an SQL query - #15 by jhaltorp

The latest SQL query I’ve been experimenting for this is the one below, maybe you’ll find it useful:

with remediations as (
    SELECT solution_id, ds.summary as fix, (regexp_replace(ds.fix,'<[^>]*>|^\s+|\s\s+',' ','g')) as fix_info 
    FROM fact_remediation(100,'riskscore DESC') 
        LEFT JOIN dim_solution ds USING (solution_id)
)

SELECT DISTINCT ON (
        da.ip_address,
        r.fix
    )
    r.solution_id,
    r.fix,
    da.ip_address,
    da.host_name,
    dos.description AS operating_system,
    CASE
    WHEN dv.cvss_score > 9 THEN
        'Critical'
    WHEN dv.cvss_score BETWEEN 7 AND 8.9 THEN
        'High'
    END AS "Severity",
    r.fix_info,
    favi.date AS scan_finished,
    favi.port AS port,
    (regexp_replace(favi.proof,'<[^>]*>|^\s+|\s\s+',' ','g')) as proof
FROM fact_asset_vulnerability_instance favi
    LEFT JOIN dim_vulnerability dv USING (vulnerability_id)
    LEFT JOIN dim_asset da USING (asset_id)
    LEFT JOIN dim_operating_system dos USING (operating_system_id)
    LEFT JOIN dim_vulnerability_solution dvs USING (vulnerability_id)
    LEFT JOIN remediations r USING (solution_id)
WHERE dv.cvss_score >= 7.0
AND dvs.solution_id in (
    SELECT solution_id 
    FROM remediations
)
1 Like