Has anyone had any luck with a SQL query to do an export of hosts missing a specific solution? We have a large number of servers that miss a patching cycle but with roll ups and back patching we are looking for a pull from the DB for servers needing a specific solution. Microsoft patches are categorized under one solution but when you do tickets or reports it will pull every vuln. I found one in the Ask and Answer are while back but it doesn’t pull much of anything. See below. Any help would be great. I wish they had it in query builder and in reporting to report based on a solution. Would make things a lot easier.
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 ‘%2020-08 Cumulative Update for Windows Server 2016 for x64-based Systems%’
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)
JOIN dim_asset_vulnerability_best_solution davbs USING (solution_id)
WHERE ds.summary LIKE '%2020-08 Cumulative Update for Windows Server 2016 for x64-based Systems%'
This is updated to use dim_asset_vulnerability_best_solution since that will take care of supersedence when it comes to the solution. I also removed the lower from the summary and fixed the quotes since those both could’ve been an issue in matching the string you provided. I hope that helps!
The dim_vulnerability table has exploits and CVSS score, so you can join with that table in the query and add those two fields to the SELECT statement.
I found a problem with using dim_asset_vulnerability_best_solution if a vulnerability title does not have best solution it removes that from the report