SQL Query Example: Report on a Single Vulnerability

Document created by synapsr on Feb 5, 2015Last modified by synapsr on Feb 6, 2015
Version 2Show Document
  • View in full screen mode

It is one of those mornings.  Heartbleed, Shellshock, POODLE, GHOST just to name a few recent ones.

You may already be aware of our community report templates for latest vulnerabilities that can be imported into Nexpose with a couple of clicks:

Here's the report template for GHOST as an example GHOST Vulnerable Assets

 

This SQL query reports on one vulnerability across ALL assets.

 

Note:  Vulnerability Title field is case sensitive.

 

WITH remediations AS (

    SELECT DISTINCT fr.solution_id AS ultimate_soln_id, summary, fix, estimate, riskscore, dshs.solution_id AS solution_id

    FROM fact_remediation(10,'riskscore DESC') fr

    JOIN dim_solution ds USING (solution_id)

    JOIN dim_solution_highest_supercedence dshs ON (fr.solution_id = dshs.superceding_solution_id AND ds.solution_id = dshs.superceding_solution_id)

),

 

assets AS (

    SELECT DISTINCT asset_id, host_name, ip_address

    FROM dim_asset

    GROUP BY asset_id, host_name, ip_address

)

 

SELECT DISTINCT

   csv(DISTINCT dv.title) AS "Vulnerability Title",

   host_name AS "Asset Hostname", ip_address AS "Asset IP",

   round(sum(dv.riskscore)) AS "Asset Risk",

   summary AS "Solution",

   fix as "Fix"

 

FROM remediations r

   JOIN dim_asset_vulnerability_solution dvs USING (solution_id)

   JOIN dim_vulnerability dv USING (vulnerability_id)

   JOIN assets USING (asset_id)

 

WHERE dv.title = 'RHSA-2015:0090: glibc security update'

 

GROUP BY r.riskscore, host_name, ip_address, asset_id, summary, fix

ORDER BY "Asset Risk" DESC

Attachments

    Outcomes