How to create a collection to list all the systems with a particular KB

User the following queries to build the collection to list all the systems with a particular KB.

For finding x86 systems:

select SMS_R_SYSTEM.ResourceID,SMS_R_SYSTEM.ResourceType,SMS_R_SYSTEM.Name,SMS_R_SYSTEM.SMSUniqueIdentifier,SMS_R_SYSTEM.ResourceDomainORWorkgroup,SMS_R_SYSTEM.Client from SMS_R_System inner join SMS_G_System_ADD_REMOVE_PROGRAMS on SMS_G_System_ADD_REMOVE_PROGRAMS.ResourceID = SMS_R_System.ResourceId where SMS_G_System_ADD_REMOVE_PROGRAMS.DisplayName like "%KB123456%"
For finding x64 systems:

select SMS_R_SYSTEM.ResourceID,SMS_R_SYSTEM.ResourceType,SMS_R_SYSTEM.Name,SMS_R_SYSTEM.SMSUniqueIdentifier,SMS_R_SYSTEM.ResourceDomainORWorkgroup,SMS_R_SYSTEM.Client from SMS_R_System inner join SMS_G_System_ADD_REMOVE_PROGRAMS_64 on SMS_G_System_ADD_REMOVE_PROGRAMS_64.ResourceId = SMS_R_System.ResourceId where SMS_G_System_ADD_REMOVE_PROGRAMS_64.DisplayName like "%KB123456%" 
Replace KB123456 with the KB number of require security update.

5 comments:

Matt Wright said...

Thank you! This is exactly what I was looking for. I didn't want to muck about creating a CI and I was unable to run the built in reports. Thanks again.

Anonymous said...

Hi there,

This didn't work on KB2841134. Would you have a quiery for IE11?

Thx.

Anonymous said...

^ Hah, Im also stuck on this with the IE11 patch.

Unknown said...

I have this for IE:

select SMS_R_System.ADSiteName, SMS_R_System.LastLogonUserName, SMS_R_System.NetbiosName, SMS_G_System_SoftwareProduct.ProductName, SMS_G_System_SoftwareProduct.ProductVersion, SMS_G_System_PC_BIOS.SerialNumber, SMS_R_System.OperatingSystemNameandVersion from SMS_R_System inner join SMS_G_System_SoftwareProduct on SMS_G_System_SoftwareProduct.ResourceID = SMS_R_System.ResourceId inner join SMS_G_System_PC_BIOS on SMS_G_System_PC_BIOS.ResourceID = SMS_R_System.ResourceId where SMS_G_System_SoftwareProduct.ProductName like "%internet explorer%" and SMS_G_System_SoftwareProduct.ProductName not like "%PDF%" and SMS_G_System_SoftwareProduct.ProductName not like "%toolbar%"

Hnayyar said...

please help...

this query for 64Bit systems not working for me.

Instead of KB when I put any installed software name so that works fine.

Is it because its not searching in View Installed Updates. As the KB I am creating query for is in View Installed Updates.

Please help I am stuck very badly.
Harsh.mni1@live.in

Post a Comment