I see a lot of questions on the forums and lot’s of traffic on my blog regarding computer collections. In many cases you want to separate your managed computers / servers into different collections.
For instance you would want to separate clients from two network segments because they need separate client settings or the clients in the one location needs one piece of software that the other one’s don’t need.
And in many cases collections can be a life-saver for software management.
For instance you can create a dynamic computer collection based on a registry value (To check if adobe reader is < version 9 if true then the computer is added to collection 1, where the IT-admin has deployed the newest Adobe reader as a required software. Next time the collection update happens the computer will no longer be present in the collection.
And also remember that a dynamic collection is based upon a query. And queries in ConfigMgr are based on WQL which basically is SQL for WMI.
Now where do we create collection ?
Open the ConfigMgr console –> Assets and Compliance –> Device Collections
Right click and choose Create
Now enter a name for the collection and choose a limit collection. This narrows down the collection. So if you have a lot of collections you should be more aware of using the limit collection, since this will lighten the database load.
Now under membership rules choose Query Rule –>
Now the query editor will open –>
And from here we choose Edit (We could also choose import another query but that we can do later)
So when we press Edit we come to the Query statement properties pane.
Under the general pane we choose for what attributes we wish to search for.
Under Criteria we choose what attributes we wish to add a statement for instance if a value is true or not.
So if we press the “Show Query Language” you can see the query that is being generated from the menu. IF you wish you can just import a finished query here (further down below in this post )
Now we are going to go trough a query that will find all computers that start with the name neo.
Go back to general and press the + Sign choose simple attribute and select attribute from there choose System Resource and NetBIOS name. Press OK and OK
If you press the Show Query Language now we can see that the query changed
Now we are going to make a criteria, so I do practically the same here
The sign % is for wildcard so all computers that have the netbios name that starts with neo will be fetched by this query.
After that is done you can press OK and OK.
And you need to remember the logic of a collection query.
1 SELECT (what do I need to show) Netbiosname = Computer Name
2 (What are the requirements that need to be fulfilled in order for the query to return true?)
3 If Computer 1 has Adobe Reader = True then inn to the collection you go.
You also need to remember that the symbol % is wildcard
So if you want to search for something that start with n you use this n% in your query
if you what a query with something that ends with n you use n%
Now some examples of queries that you can use (By using the import query button –> )
Adobe Reader Version from Add/Remove Programs in Control Panel
select SMS_R_System.NetbiosName,
SMS_G_System_ADD_REMOVE_PROGRAMS.DisplayName from
SMS_R_System inner join SMS_G_System_ADD_REMOVE_PROGRAMS on
SMS_G_System_ADD_REMOVE_PROGRAMS.ResourceId =
SMS_R_System.ResourceId where
SMS_G_System_ADD_REMOVE_PROGRAMS.DisplayName like “%Adobe Reader%”
Computer based on OU
select SMS_R_System.NetbiosName,
SMS_R_System.SystemOUName from
SMS_R_System where
SMS_R_System.SystemOUName = “OU Name”
Spesific computer name in this case every computer that starts with neo
select SMS_R_System.NetbiosName from
SMS_R_System where SMS_R_System.NetbiosName like “neo%”
Windows 8 computers
select SMS_R_SYSTEM.ResourceID,
SMS_R_SYSTEM.ResourceType,SMS_R_SYSTEM.Name,SMS_R_SYSTEM.SMSUniqueIdentifier,
SMS_R_SYSTEM.ResourceDomainORWorkgroup,SMS_R_SYSTEM.Client from SMS_R_System
inner join SMS_G_System_COMPUTER_SYSTEM on SMS_G_System_COMPUTER_SYSTEM.ResourceId = SMS_R_System.ResourceId
where SMS_R_System.OperatingSystemNameandVersion like "%Workstation 6.1%" or SMS_R_System.OperatingSystemNameandVersion like "%Windows 8%