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.

Joining two or more collections into a new collection

Use the below code to create a new SCCM 'Collection' using the members of other one or more other collections.

- Create new collection
- Use a 'Query' to define the Membership Rules
- Use system resources
- Use the below SQL code to define the members.

Change the XXXXXXX to the Collection ID you want.

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_CM_RES_COLL_XXXXXXX) or SMS_R_System.ResourceId in (select ResourceId from SMS_CM_RES_COLL_XXXXXXX)

Boom! your new collection is now populated with the members of other collections.

SCCM Collection based on 'File' and version less than "X"

The below System query shows "X.exe" file with anything less than version "X.x.x.x.". Very useful for discovering out of date files and creating and advertisement to install the correct version

Make sure you have your file inventory configured correctly.

SELECT        VRS.ResourceID, VRS.ResourceType, VRS.Name0

FROM            dbo.v_R_System AS VRS INNER JOIN
                         dbo.v_GS_SoftwareFile AS SF ON VRS.ResourceID = SF.ResourceID

 WHERE        SF.FileName = 'X.exe' AND SF.FileVersion <= 'x.x.x.x'

Please note i've used 'less than or equal to' in the above query if you want just less than remove the '='

Also a different way to do the same. Create 2 queries

http://www.sccm-tools.com/tools/wqlqueries/WQLQueries-Colcompswithfile.html