Excluding PCs from a query-based collection 1

[SPV]

My question is similar to the one posed by Grrrowler in thread22-950964, but not quite the same. In fact, MisterNiceGuy's reply suggests he already has the answer to my question...

I have created a collection of computers using a query, but I now want to exclude any PCs which appear in a specific collection.

This can probably be done using the NOT IN construct from WQL, but I can't work out how to enumerate the contents of the second (exclusion) collections.

Here's hoping someone can offer me a simple solution.
Thanks.

 

[tbrennans]

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 IPSubnets in ( '167.137.18.0', '167.137.102.128' ) and Name not in (select name from SMS_CM_RES_COLL_Z01001C1) ORDER BY Netbiosname

Tim B

 

[SPV]

Thanks tbrennans. It worked a treat with a little tweaking (I had to use [tt]SMS_G_System_SYSTEM.Name not in (select name from [/tt]...). But your code put me on the right lines.


There was one additional problem, caused by our configuration of SMS 2003. The workaround I used for this may be of use to other readers.

Our configuration of SMS 2003 prevents me seeing collection IDs on the collection properties sheets. Of course I need this in the query to refer to the exclusion collection (Z01001C1 in tbrennan's example above). However I can see the IDs of the top-level collections by viewing the contents of the Collections container (the IDs are the second column).

So I created a collection at the top-level and noted its ID.

Then I created a Link To this collection at the desired location. The new collection keeps the same ID (apparently this is how Link To works).

At this point the top-level collection is redundant so I deleted it to keep things tidy.


Now I can change the contents of the new collection and the query picks up the changes.


Job's a good'un, as they say.

 

[SPV]

I have just discovered another (much more elegant) way of getting the collection ID if the SMS 2003 console won't tell you.

Use the SMS 2.0 AdvertViewer (part of the SMS v2 resource kit I think). As it is read-only, it can connect to the SMS 2003 database. Its property sheets show the collection ID for all existing collections.