Create A Dynamic Collection Containing The Last PC Used By The Member Of An Active Directory Group

During a recent project to roll out Windows Updates using System Center Configuration Manger 2007 R2 (SCCM) I wanted to deploy updates to a user group.  Unfortunately, SCCM will only deploy Windows Updates to a collection that contains computers, not users.  I used the code below to create a SCCM collection that contains the computer that was last logged onto by a member of a given Active Directory group.

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_COMPUTER_SYSTEM on SMS_G_System_COMPUTER_SYSTEM.ResourceId = SMS_R_System.ResourceId where SMS_G_System_COMPUTER_SYSTEM.UserName in (select UniqueUserName  from  SMS_R_User where SMS_R_User.UserGroupName = “DOMAIN\\GROUPNAME“)

To use the code follow the steps below.

Firstly, start the new collection wizard, enter a name and click Next

Click on the button highlighted in yellow bellow.

Enter a name and click Edit Query Statement

Paste in the code replacing the domain and group name with your own.  Click OK and finish the wizard.

Advertisements