SQL Query - Solutions

InsightVM SQL Queries
#1

Does anyone have a query on hand, or a portion of one, that will show me a list of systems and their associated remediations? Basically the top 25 report but in an exportable / manipulatable format.

I’m playing with tables but I thought someone might already have something. Basically, I’m looking to write reporting that shows me all the assets in a given filtered group that require a cumulative update or security update to be applied, then I plan to run that monthly after all patching “should” be completed, to find systems that are lagging. If I can extract that data I’ve got more I want to do with it after the fact.

#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
#3

Theres alot going on here, but you can pick it apart and use what you need. Hopefully its useful.

WITH 

assets AS ( --get assets and vulnerability metadata ##scoping must be done by tags in console
    SELECT dta.asset_id, favi.vulnerability_id, htmlToText(favi.proof, false) "proof", 
    CASE WHEN favi.port < 0 THEN NULL ELSE favi.port END AS "port", ds.name "service",  dp.name "protocol"
    FROM dim_scope_tag
    JOIN dim_tag_asset dta USING(tag_id)
    JOIN fact_asset_vulnerability_instance favi USING(asset_id)
    JOIN dim_service ds USING(service_id)
    JOIN dim_protocol dp USING(protocol_id)
),

solutions AS ( --get the superceded solutions for each id and age of vulns
    SELECT a.*, htmlToText(ds.fix) "fix", ds.summary, trunc(fava.age_in_days) AS "orig_vulnerability_age", first_discovered
    FROM assets a
    JOIN dim_asset_vulnerability_best_solution USING(asset_id, vulnerability_id)
    JOIN dim_solution ds USING(solution_id)
    JOIN fact_asset_vulnerability_age fava USING(asset_id, vulnerability_id)
),

r7 AS ( 
    SELECT vulnerability_id, category_name
    FROM dim_vulnerability_category 
    WHERE category_name = 'Rapid7 Critical'
),

vulns AS ( --get more info about the vulnerabilities and add severity
    SELECT s.*, da.host_name, da.ip_address, da.last_assessed_for_vulnerabilities AS last_scan_date,
    dv.nexpose_id, dv.title, htmlToText(dv.description) "description", 
    CASE 
        WHEN riskscore >= 900 OR dv.nexpose_id = 'tlsv1_0-enabled' OR (r7.category_name = 'Rapid7 Critical' AND dv.nexpose_id NOT LIKE '%sweet32%') THEN 'Critical'
        WHEN riskscore >= 700 AND riskscore < 900 THEN 'High'
        WHEN riskscore >= 400 AND riskscore < 700 THEN 'Medium'
        ELSE 'Low'
    END AS "dlx_risk_based_severity",
    CASE
    WHEN dv.cvss_v3_score IS NOT NULL THEN 
        CASE WHEN cvss_v3_score >= 9 OR dv.nexpose_id = 'tlsv1_0-enabled' THEN 'Critical'
             WHEN cvss_v3_score >= 7 AND cvss_v3_score < 9 THEN 'High'
             WHEN cvss_v3_score >= 4 AND cvss_v3_score < 7 THEN 'Medium'
             ELSE 'Low'
        END
    WHEN dv.cvss_v3_score IS NULL THEN
        CASE WHEN cvss_score >= 9 OR dv.nexpose_id = 'tlsv1_0-enabled' THEN 'Critical'
             WHEN cvss_score >= 7 AND cvss_score < 9 THEN 'High'
             WHEN cvss_score >= 4 AND cvss_score < 7 THEN 'Medium'
             ELSE 'Low'
        END
    END as "dlx_cvss_based_severity"
    FROM solutions s
    JOIN dim_vulnerability dv USING(vulnerability_id)
    LEFT JOIN r7 USING(vulnerability_id)
    JOIN dim_asset da USING(asset_id)
),

age_adjustment AS ( --fix for vulnerabilities that have an increased risk based severity being reported as past SLA
    SELECT v.*,
    CASE 
        WHEN first_discovered < '06-01-2021' THEN
            CASE 
                WHEN dlx_risk_based_severity IN ('Critical','High','Medium') AND dlx_cvss_based_severity IN ('Low') THEN current_date - DATE '2021-06-01'
                WHEN dlx_risk_based_severity IN ('Critical','High') AND dlx_cvss_based_severity IN ('Medium') THEN current_date - DATE '2021-06-01' 
                WHEN dlx_risk_based_severity IN ('Critical') AND dlx_cvss_based_severity IN ('High') THEN current_date - DATE '2021-06-01'
                ELSE v.orig_vulnerability_age
            END
        ELSE v.orig_vulnerability_age
    END AS "vulnerability_age"
    FROM vulns v
),

sla AS ( -- add internal sla targets
    SELECT v.*, dos.system, dos.version,
    CASE
        WHEN dlx_risk_based_severity = 'Medium' AND dos.system = 'CentOS Linux' THEN 120
        WHEN dlx_risk_based_severity = 'High' AND dos.system = 'CentOS Linux' THEN 90 
        WHEN dlx_risk_based_severity = 'Critical' AND dos.system = 'CentOS Linux' THEN 60
        WHEN dlx_risk_based_severity = 'Medium' THEN 90 
        WHEN dlx_risk_based_severity = 'High' THEN 60 
        WHEN dlx_risk_based_severity = 'Critical' THEN 30 
        ELSE NULL
    END as "sla_target"
    FROM age_adjustment v
    JOIN dim_asset USING(asset_id)
    JOIN dim_operating_system dos USING(operating_system_id)
)
--final formatting and sla 
SELECT host_name "Asset Hostname", ip_address "Asset IP Address", system "Asset OS System", version "Asset OS Version", 
nexpose_id "Vulnerability ID", title "Vulnerability Title", description "Vulnerability Description", dlx_risk_based_severity "DLX Severity", 
CASE
    WHEN vulnerability_age <= sla_target THEN 'Within SLA'
    WHEN vulnerability_age > sla_target THEN 'Past SLA'
    ELSE 'No SLA'
END AS "DLX SLA", 
vulnerability_age "Vulnerability Age", proof "Vulnerability Proof", summary "Solution Summary", fix "Solution",
port "Port", protocol "Protocol", service "Service",  to_char(last_scan_date, 'mm/dd/yyyy') "Last Scan Date"
FROM sla
1 Like