SCCM - Joining Collection and Excluding Collections

Say you have a collection of computers in SCCM. Then you have a second collection of computers you wish to exclude.

Easy. Create a third collection based on members of the first collection but exclude members from the second collection.

Using the following query you can accomplish this. Just remember to replace XXXX and YYYY with the correct collections.

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 not in (select ResourceID from SMS_CM_RES_COLL_CXXXXXXX)
and SMS_R_System.ResourceId in (select ResourceID from SMS_CM_RES_COLL_CYYYYYYY)

You could also accomplish this with 2 collections.

Create a collection of computers you want to target and also excluding those in a specific collection

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
Client = 1
and ClientType = 1
and ResourceId not in (select ResourceID from SMS_CM_RES_COLL_XXXXXXXX)

If you find this article useful please leave me a comment or click on an ad to show your support.