SQL Export By Solution

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%’

)

)

You could try something like this:

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!

@holly_wilsey Would it be possible to add Exploits and CVSS score to this query?

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.

Here is the schema for the dim_vulnerability table.