SQL example - proof of a specific remediation

This example query allows you to report on a specific remediation. Checking progress on a remediation report previously shared with the asset owners is a common use case for this.

This example is for "remove world write permissions".

sql
1
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)
2
3
FROM fact_asset_vulnerability_instance favi
4
5
JOIN dim_vulnerability_solution dvs USING (vulnerability_id)
6
7
JOIN dim_asset da USING (asset_id)
8
9
JOIN dim_operating_system dos USING (operating_system_id)
10
11
JOIN dim_solution ds USING (solution_id)
12
13
JOIN dim_site_asset dsa USING (asset_id)
14
15
JOIN dim_site dsi USING (site_id)
16
17
WHERE solution_id IN (
18
19
SELECT solution_id
20
21
FROM dim_solution_highest_supercedence
22
23
WHERE superceding_solution_id IN (
24
25
SELECT solution_id
26
27
FROM dim_solution
28
29
WHERE lower(summary) LIKE '%remove world write permissions%'
30
31
)
32
33
)