SCCM Configmgr WQL Query to get Clients from Multiple collections Coll Name Starts with ABC ?

One of my Blog reader has posted a comment asking for ‘How to create Collection to pop-up clients from multiple Collections start with ABC% ‘ ? ABC is the Collection Name starts with.

If you want to create a collection to see all clients from different Collections and the Collection Name starts with ABC .How do you get this done ?

For this requirement, You Cannot use Include collection as this is not Dynamic .If you choose to go with Include collection (which is Static) ,you cannot get the clients from collections that will be created in the Future.

To get this task done, we will using concept called sub Selected Queries with IN condition.

Create a New Collection ,Limit the Collection to All systems, Add a Query Rule ,Edit the Query Statement and paste the following WQL into it.

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 where SMS_R_System.ResourceId in (
SELECT ResourceID from SMS_COLLECTION CO inner join SMS_FullCollectionMembership FCM
on FCM.COLLECTIONID=CO.COLLECTIONID and CO.Name like 'ABC%')

Where ABC is Name of the Collection that Start with ,for Ex: SU or Microsoft or Adobe etc.