Recent Posts

Tags

News

Community

Email Notifications

Conference Presentations

Affiliations

Articles Published

My Personal Links

Archives

Duncan McAlynn Microsoft System Center

Resourcefulness is the great divider between excellence and mediocrity.

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"

Comments

Mary B. said:

Just tweaked with patchstateEX and it was as good as new.  Thanks for keeping this online.  Saved me some time.

# July 6, 2007 3:11 PM

Phil Day said:

Thanks for the sql queries, the did the job for me.  I did find that for Officeproducrs I needed to change the query a bit.  It seems that Office patches dont have a Q number or an MS0n-nnn number collected in the database.

Si I created this query to do the same job for Office patches :

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.Title like '%918419%' )    

# August 17, 2007 5:39 AM
Leave a Comment

(required) 

(required) 

(optional)

(required)