Creating an SCCM Collection from an List of Computers in Excel

I frequently use this trick to manage collections of computers in SCCM where the original list comes from Excel, or from a query of another system that I can dump into Excel.  Doing it this way, you don't have to rely on adding computers one at a time to a collection query or dumping systems into tools like the the ConfigMgr 2012 Right Click Tools (which by the way are awesome).  

One thing to note is that you're adding systems by name when you do it in a query, and not as direct adds.  This may or may not be your desired state, just something to keep in mind.

1. Assuming your list of systems is in Column A, and Column B is blank, enter the following formula into B1 (adjust accordingly if you have headers):

=Char(34) & A1 & Char(34) & ","

Using the Char(34) function, you're adding in quotes, around your computer name, and then appending a comma afterward.  This is the format required in WQL formatted queries for use in Configuration Manager.  Drag this formula down the page and you should have something that looks like this:

For those programatically inclined, I posted a VBA solution to automate Step #1 in 2009 in this post.

2. Create a new query in SCCM.  You can do this under queries, or as part of a collection, whatever is needed.  Edit the WQL of the query and enter the following:

1select *  from  SMS_R_System where SMS_R_System.Name in ( )

What you now have is a query that returns no computers as there are no names in your list.

3. Now just copy and paste in your list from Excel right inside the parenthesis, making sure to exclude the final comma after the last computer name.  You'll have something like this:

1select *  from  SMS_R_System where SMS_R_System.Name in (
2"SANDIAGO-1",
3"SANDIAGO-2",
4"SANDIAGO-3",
5"CHICAGO-1",
6"CHICAGO-2",
7"CHICAGO-3"
8)

That's all there is to it - you now have a query that returns a list of records based on a list populated from Excel.