I got tired of googling this every time I make a new collection, so I figured it was time for my own post. I am now using SCCM for work, so I have had to learn a few new tricks to make life easier. One thing I’m doing often enough is creating a custom device collection of computers. The slow and tedious way to do this is to individually add each machine. Blah! What a waste of time. To make it go as fast as possible, I try to already have an excel list of the machine names I want to add to the collection.

In my excel file, my list of machine names are in column A. I’m going to be using column B for the values I’ll copy into SCCM. The formula that we’ll be using to convert the names into the format that SCCM wants is:

=char(34)&A2&char(34)&","

Lets break that down to see how it will convert the name into a format that can be used..

• Char(34) is going to give us the quotation marks we want on either side of our computer name.
• A2, in this instance, is the cell that contains one of my computer names. You will have to adjust this cell reference to fit your excel sheet.
• At the very end of it, we add a comma.

Once we have that taken care of, we can drag the corner of the cell down to apply that same formula to the rest of our list.

Below is the basic query we will be using and adding our formatted list of names to.

select * from SMS_R_System where SMS_R_System.Name in ( )

The end result, using the example list of computer names above, will look like this. The one thing to note is that you need to remove the very last comma from the list of computer names or you’ll get an error message when you try to save it.

select * from SMS_R_System where SMS_R_System.Name in (
"computer001",
"computer002",
"computer003",
"computer004",
"computer005",
"computer006",
"computer007",
"computer008",
"computer009"
)

That is how you populate a SCCM device collection from an Excel list.