Creating an SCCM Collection from a List of Users or Computers

This should work for SCCM 2007 and newer.

How often do you get a request to push software or setting change to specific list of users and computers and groan at the thought of having to create the collection for it?

I cannot tell you how many times I have searched for this solution. Someone made some great PowerShell scripts, but I couldn't always get them to work and/or it was fairly slow. (REF: http://blogs.technet.com/b/mniehaus/archive/2009/04/10/adding-members-to-a-configmgr-collection-using-powershell-v2-ctp3.aspx)

I found another way to import using a .CSV, but that, too, was a little cumbersome. That method involved using a report that pulled the SMBIOS and MAC Address information, then using Access to query against the list to create the .CSV file to import. But that only worked for Computers, not users. (REF: http://www.windows-noob.com/forums/index.php?/topic/4505-how-can-i-import-computers-into-sccm-2012-using-a-file/)

A few days ago, I found a method in a forum that simplifies this tremendously (REF: http://www.myitforum.com/forums/Can-you-import-users-into-a-collection-via-CSV-m211472.aspx):

The query to import computers is simply:

select * from SMS_R_System where name in ("computer1" , "computer2" , "computer3" , "computer4" ) 

For users, you would change this to:

select * from SMS_R_User where name in ("user1" , "user2" , "user3" , "user4" ) 

The trick here is how to get your list of computers or users formatted with the quotes and commas. I did this in Excel.

Copy your list of users or computers to Column A. In cell B1, enter the following:

="""" & A1 & ""","

That's equals four quotes (space) ampersand (space) A1 (space) ampersand (space) three quotes, comma, quote. Copy the formula down from B1.

I use Notepad from here, so I can copy and paste the query into SCCM later. In Notepad, write your query like this to start:

select * from SMS_R_User where name in (

Copy and paste Column B from your Excel spreadsheet and add a closing parentheses:

select * from SMS_R_User where name in ("USER1",

"USER2",
"USER3",
"USER4",)

Now fire up your SCCM Console. The above query is a User collection, so go there to create a new user collection.

1. Give the collection a name, click Next, then choose Query Rule from the drop down list.
2. Give the query rule a name, then click Edit Query Statement...
3. Click Show Query Language
4. Copy and paste your query from Notepad into this window.
5. Click OK
6. Click Next a couple more times to finish creating the collection.
7. Update Membership, then Refresh after a couple minutes.

You should now see your list of users (or computers). If any users or computers no longer exist (or possibly misspelled), they will not appear in the collection. You can now deploy to your specific list of users/computers! :-)

Hope that helps!