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.

Step 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:

Excel Computer List for SCCM

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

Step 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:

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

Step 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:

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.

FacebooktwitterredditpinterestlinkedinmailFacebooktwitterredditpinterestlinkedinmail

7 Comments

    • Hi Cora, it depends on what the purpose of your collection is. All Systems is fine, unless you’re looking to ensure that they’re in a collection you’ve already setup, such as all laptops, all servers, etc.

      Reply
  1. This method is useful for when you have less than roughly 1092 systems, as the WQL query statement area has a maximum of approximately 19,656 characters.

    Reply
    • You’re right, Tuna – you’ll need to break them up into multiple queries if it’s too long, or use something like the Right Click Tools for Configuration Manager to add them as direct adds.

      Reply
  2. This trick is from exactly 4 years ago and still is relevant. Thank you so much! : )

    Reply
  3. =CHAR(34)&A1&CHAR(34)&”,”

    remove all the extraneous spaces and it works

    Reply

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.