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)
SELECT DISTINCT
da.host_name,
da.ip_address,
dos.description,
da.last_assessed_for_vulnerabilities,
ds.summary AS solution,
ds.additional_data,
ds.fix,
ds.url
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%’