Friday, September 27, 2013

WQL Query to add bulk computers to SCCM collection

We use WQL queries to create dynamic SCCM Collections to list machines with a particular software, hardware, etc. But when it comes to adding a random machines from a given list and when we don’t use the SCCM Right-click tools, mostly we go the conventional manual method. Once during a conversation with a Microsoft tech, he helped me with this simple query which made most of my deployments easier. I can say this is faster and lighter than using Right-click tools. Sharing this query here.

We can add any number of Computer list to a Collection, but max 1500 hostnames per query. We can also remove any single machine from this customized collection.  

Select SMS_R_System.ResourceID,SMS_R_System.ResourceType,
from SMS_R_System where in

Easy Steps to consolidate the computer list into the query with comma: 
* Add the machines to MS Excel by listing each machine on each row.
* Include a column (A) before machines list with the single code symbol  (').
* On the Columns C and D enter the comma symbol (,) and single code ('). 
* Drag the single code and comma symbols till the last before row of the machine list.
* Add a formula to Column E [=A:A&B:B&C:C&D:D].
* On Column E, Copy - Paste Special with Values.
* Copy the entire column E and paste in the query between brackets ( )
* It should look like  ('Machine1',


* Click Ok and close the New collection window and we are done.

No comments:

Post a Comment