Tuesday, May 29, 2012

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.

5 comments :

  1. But, i want to exclude certain computer names from the collection. when i link "All Windows 7" collection for advertisement, it should not inculde the pcs which i want to..
    like i have machines with host names like..
    WSHYDP01
    WSBOMP01
    WSMUMP02
    WSCHNP02

    i want to exclude these names from my all windows 7 collection..

    ReplyDelete
  2. You will have to look at your original collection, the query you have defined is including those assets from the query. Remember queries will return exactly what you ask from it, nothing more nothing less.

    In your original query you could write something like;

    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.Name != "WSHYDP01" and
    SMS_R_System.Name != "WSBOMP01" and
    SMS_R_System.Name != "WSMUMP02" and
    SMS_R_System.Name != "WSCHNP02" and
    Client = 1
    and ClientType = 1
    and ResourceId not in (select ResourceID from SMS_CM_RES_COLL_XXXXXXXX)

    ReplyDelete
  3. Hi,
    PLease help...
    I already have dynamic collection which will collect members from AD group,now i want remove a member from this collection by removing that member from AD group, is it possible?

    ReplyDelete
  4. Awesome article! Saved me a lot of time and frustration!

    Thanks!

    ReplyDelete
  5. Thank you! Exactly what I was looking for.

    ReplyDelete