Fine tuning the SQL Query with severity criteria 8 to 10

HI All,

I need help with existing SQl query i use to extract custom report.

I want to amend below features:

  1. Only pull CVSSv3 severity 8 to 10.
  2. Proof and solution only for asset OS based (I am getting for multiple OS currently).
  3. Adding exploitability skill (Novice, beginner and Expert)
  4. Date and time of last vulnerability assessed.
  5. Exclude Microsoft patching.

SELECT da.ip_address, da.host_name, dos.description AS OS, dso.summary, proofAsText(dso.fix) AS Solution ,dv.severity,
CASE WHEN favi.port = -1 THEN NULL ELSE favi.port END AS Port,dp.name AS protocol,proofAsText(favi.proof) AS Proof
FROM fact_asset_vulnerability_instance favi
JOIN dim_asset da USING (asset_id)
JOIN dim_operating_system dos USING (operating_system_id)
JOIN dim_vulnerability dv USING (vulnerability_id)
JOIN dim_vulnerability_solution dvs USING (vulnerability_id)
JOIN dim_solution dso USING (solution_id)
JOIN dim_protocol dp USING (protocol_id)

ORDER BY da.ip_address, dso.summary,dv.severity

I highly recommend getting the data warehouse setup. It will make querying your data so much easier. But this should get you what you need.

You did not mention how/if you were filtering your report, so this does not take into account any filtering needs. You can modify the first CTE and add the junk dimensions based on your scope. Understanding the reporting data model: Dimensions | Nexpose Documentation

WITH 
assets AS ( --get assets and vulnerability metadata ##add dim_scope_tag here if filtering report by tags
    SELECT favi.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 fact_asset_vulnerability_instance favi
    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) "solution", ds.summary AS "solution_summary", trunc(fava.age_in_days) AS "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)
),

vuln_category AS ( --each id can be in multiple categories, so combine them to filter out
    SELECT vulnerability_id, STRING_AGG(category_name, ', ') AS "category_name"
    FROM dim_vulnerability_category
    GROUP BY 1
),

vulns AS ( --get more info about the vulnerabilities
    SELECT s.*, da.host_name, da.ip_address, da.last_assessed_for_vulnerabilities AS last_scan_date,
    dv.nexpose_id, dv.severity AS "r7_severity", cvss_v3_score, dve.skill_level AS "exploit_skill_level", dv.title, htmlToText(dv.description) "description"
    FROM solutions s
    JOIN dim_vulnerability dv USING(vulnerability_id)
    JOIN dim_vulnerability_exploit dve USING(vulnerability_id)
    JOIN vuln_category vc USING(vulnerability_id)
    JOIN dim_asset da USING(asset_id)
    WHERE cvss_v3_score >= 8 AND vc.category_name NOT LIKE '%Microsoft Patch%'
)

--final formatting 
SELECT host_name, ip_address, last_scan_date, nexpose_id, r7_severity, cvss_v3_score, exploit_skill_level, title, description, solution_summary, solution
FROM vulns
2 Likes