Creating an SMS Collection for Systems Missing a Patch
The following is a query that you can use to create an SMS collection of systems that are missing a patch. In the code snipplet you'd need to change the ““ reference to the actual Knowledge Base article number that you're looking for.
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_PATCHSTATE on SMS_G_System_PATCHSTATE.ResourceID = SMS_R_System.ResourceId
where SMS_G_System_PATCHSTATE.QNumbers = "" and
SMS_G_System_PATCHSTATE.Status = "Applicable"
Alternatively, you can use the following code snipplet to base the collection on the MS bulletin ID. Just replace the “MS04-025“ with the appropriate bulletin ID.
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_PATCHSTATE on SMS_G_System_PATCHSTATE.ResourceID = SMS_R_System.ResourceId
where SMS_G_System_PATCHSTATE.Status = "Applicable" and
SMS_G_System_PATCHSTATE.ID = "MS04-025"