This came up at work again, so I’ve decided that so I can find it this time, I’ll post it here. This is directly from SMS Guys who have moved on to here.
1. Create a collection that will contain all clients to be excluded from another collection (we’ll call it ‘ExclusionsCollection’ for this example.)
2. Once you have that, go into the Collections table in the SMS SQL database and find the row that contains ‘ExclusionsCollection’. Note the corresponding ‘ResultTableName’. EDIT: A simpler way is to look in the Collection’s properties – the ID is at the bottom.
3. In the collection that you want to exclude the members of ‘ExclusionsList’ from, copy and paste the following query:
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)
I ask myself why I haven’t thought of this yet. Sometimes you just need to think outside the box. Until now, we’ve managed client exceptions strictly through query statements which lack not only traceability but mostly visibility when they get too complex.
Using SMS_CM_RES_COLL doesn’t necessarily make it easier to read but easier to ‘get’ for our common technicians.
“Put client in this collection and it wont receive this package” is easier than “Open admin console, open collection properties, edit query, enter systemname”
It’s also a lot easier to maintain in the long run. Client exceptions in a query wont update themselves when a client is renamed or deleted from the site.
Big thumbs up for this one!
Worked perfectly – thanks for this writeup. Created a sub-collection, pointed the query at the parent collection with all of the PCs I wanted (and a few I didn’t, that were in another collection), excluded the unwanted collection via collection ID and it actually worked. Saved tons of time.
Splendid!
Excellent post Leegend. Just a heads up for those less experienced in SCCM and Query structuring – if your collection is already based on a query, use the last section of Leegends script and tag it on the end of your existing query, eg and
ResourceId not in (select ResourceID from SMS_CM_RES_COLL_XXXxxxxx)
Thanks Mark! It can be tricky to get the logic for all your rules right, so I found this technique helped keep things simple (logically).
Being a newbie myself, I am still a little confused. I have created a “Workstation” collection based up a “Model” query. I would like to exclude all clients that curretly reside in my “Exclusions” collection. Based upon the previous posts I have created a membership rule “Exclusions”: limited to the “Workstations” collection with the following query statement;
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_CDT00225)
CDT00225 = my “Exclusions” collection.
Well I’d expect that to exclude objects in CDT00225. It would be worth checking the number of objects in each collection and if that matches up to what you expect. So, if “Workstation” has 100 and “Exclusions” has 10, you should get 90
Correct. My Workstation collection has 425 clients and my Exception collection has 9 so my end result should be 416…. It is not, it is still 425.
And those 9 clients are definitely members of Workstation?
Yes they are.