Friday, April 6, 2007

Collection of Computers based on Users

SMS has a nice feature that allows you to create a collection based on users. That way when a users logs into a computer SMS will then run an advertisement for whatever computer they are on. The problem is maybe you want to have a collection of computers not the users. The worst flaw this the collection query I have created below and using collections based on users is that if a person logs in to another machine, for whatever reason, SMS will find them and push software to them.
That in mind, here is a collection that will take any OU group of users and create a collection of computers.


---------------------------------------
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 UserOUName = "xxxx /SECURITY USERS & GROUPS” )
--------------------------------------
Windows User account and group discovery must be enabled and run on your server to use this.

How does this work. It first grabs the list of users. Then matches them to the comptuers that SMS currently sees them logged into. To change this to Last Logged in user you need to change the SMS_G_System_Computer_System to SMS_R_System.LastLogonUserName for computers. This will give you last logged in user.

"Limit to Collection" for all your computer Workstations or further limit it to based on departments

If you don't know what group then you can create a query of just (select UniqueUserName from SMS_R_User where UserOUName = "") click values until you have the value you need and paste it into the larger query.