While reading a Reddit, I ran across a post WQL Query SCCM. Originally the post was listed in another language, I forget which one as it is not important. After some back and forth, I understand they he wanted to create a collection of VM host. In this article I will provider you with the WQL queries to create both All VMs and All VM Host collections.
As a Microsoft Endpoint Configuration Manager (MEMCM / MECM / SCCM / ConfigMgr) Administrator we all always creating new collections. And this is a good example of subselect query. You can learn more from The Subselect Query.
What is a subselect?
In a nutshell, a subselect query will have one query inside another. For more details, please see The Subselect Query . In this example we will find all VM hosts for each VM itself. Then we will take this information and make a collection of VM Host.
VM Collections
You will need two queries in order to make up the one collection. e.g. the list of all VM hosts. Then you will need to make that a subselect query that links to SMS_R_System. This is so that you can create a collection of VM host computer. Collection queries use WQL and not SQL. WQL is a subset of SQL, hence why they look very similar.
Subselect query of All VM Hosts
This WQL query will list all virtual host computers, listed within the SMS_R_System view. Heartbeat discovery will populate these details. This query will be used and a subselect query within the All VM Host query.
select distinct SMS_R_System.VirtualMachineHostName from SMS_R_System where SMS_R_System.VirtualMachineHostName is not null
All VM Hosts – Collection
This collection query uses the subselect query to list all VM Host within your MECM environment.
select * from SMS_R_System where SMS_R_System.NetbiosName in (select distinct SMS_R_System.VirtualMachineHostName from SMS_R_System where SMS_R_System.VirtualMachineHostName is not null)
Bonus
As a bonus, I created the WQL query that can be use this query to find all VM computers within MECM.
All VMs
select distinct SMS_R_System.* from SMS_R_System where SMS_R_System.VirtualMachineHostName != ‘’
Note: watch out for the styled quote. Replace then with normal single quote marks.
Since there are no tricks need to create the collection from these queries. I will not show you how to create the collections from them.
Results
Notice the All Desktop and Server Clients, collection has 15 computers. Also notice that IR-01 and DESKTOP-LT0JDRF are shown. Both are physical computers within this lab. IR-01 is my Hyper-v Host.
Notice that All VM Hosts collection only list my Hyper-v server.
Again, notice that my All VM collection list all of my VM and exclude both physical computers.
What is interesting about this set of collections, is that is it built upon the default inventory of VM host details within MECM. There is no messing around with making it up.
Finally, don’t forget that you can subscribe to my RRS feed to stay on top of the latest trips and tricks. Additionally, If you have any questions, please feel free to touch base @Garthmj.