SQL Query Listing Systems that require Specific KB

Good Afternoon,

I am looking for a SQL query that will pull a list of systems that contain a specific Microsoft KB in the solution to a Microsoft vulnerability.

Basically, when I am having the patch team apply a Microsoft KB, I want to be able to input that KB into a query that will return all the systems that need that specific KB.

I tried to search for a similar query but was not able to find anything. If someone could either point me in the right direction or provide a SQL query that would do the job, I’d greatly appreciate it.


This might help get you started - it searches the url field for the KB. The example below searches for KB 5009546 as an example. (The fix field comes out blank for me, but I think it’s HTML so may need ‘cleverer’ stuff that I can do to get that working and searchable)

ds.summary AS solution,
FROM dim_asset da
JOIN dim_operating_system dos ON dos.operating_system_id = da.operating_system_id
JOIN dim_asset_vulnerability_solution dasv ON dasv.asset_id = da.asset_id
JOIN dim_vulnerability dv on dv.vulnerability_id = dasv.vulnerability_id
JOIN dim_solution ds on ds.solution_id = dasv.solution_id
JOIN fact_asset_vulnerability_instance favi ON da.asset_id = favi.asset_id AND favi.vulnerability_id = dv.vulnerability_id
WHERE url LIKE ‘%5009546%’

I just gave it a try and it works wonderfully. Thank you so much for your help!