tomcat-vulns

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 = '%tomcat%'

 

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

ORDER BY "Asset Risk" DESC