SQL Query Example: Proof of a Specific Remediation

Document created by synapsr on Feb 6, 2015
Version 1Show Document
  • View in full screen mode

In the world of vulnerability management and remediation it is always good to check progress.  This SQL Query allows you to report on a specific remediation, say to check progress on a remediation report previously shared with the asset owners.


Note: this example is for "remove world write permissions".


SELECT dsi.name AS site, da.ip_address, da.host_name, dos.description AS operating_system, favi.date AS scan_finished, proofAsText(ds.fix) AS remediation, proofAsText(favi.proof)

FROM fact_asset_vulnerability_instance favi

JOIN dim_vulnerability_solution dvs USING (vulnerability_id)

JOIN dim_asset da USING (asset_id)

JOIN dim_operating_system dos USING (operating_system_id)

JOIN dim_solution ds USING (solution_id)

JOIN dim_site_asset dsa USING (asset_id)

JOIN dim_site dsi USING (site_id)

WHERE solution_id IN (

SELECT solution_id

FROM dim_solution_highest_supercedence

WHERE superceding_solution_id IN (

SELECT solution_id

FROM dim_solution

WHERE lower(summary) LIKE '%remove world write permissions%'