Collection Exclusion - Windows Update

[Dan Mahler]

(New to SCCM, learning fast. Familiar enough with SQL to edit statements and understand what is happening)

 

I have a set of 5 nested collections I utilize for Windows update deployment.

 

Among these, I have a collection called "Servers" containing all 2003 and 2008 servers. The SQL Query is as follows.

 

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 OperatingSystemNameAndVersion like 'Microsoft Windows NT%Server%'

 

I have a collection called "Manual" (Collection ID LMG00033) that I would like to exclude from the Servers collection.

 

The solutions I have read aren't working for me.

 

How can I modify the Servers SQL Query to exclude the Manual Colletion.

 

Thank you in advance

 

DWM

[Trevor Sullivan]

Try something like this:

 

select * from SMS_R_System

where

SMS_R_System.ResourceID not in (select ResourceID from SMS_FullCollectionMembership where CollectionID = 'LMG00033')

and OperatingSystemNameAndVersion like 'Microsoft Windows NT%Server%'

 

Cheers,

Trevor Sullivan

http://trevorsullivan.net

http://twitter.com/pcgeek86

[Jorgen Nilsson]

That should do it!

I have written a little post about how to amongst other things use an exclusion collection. You can check it out here: http://ccmexec.com/?p=486

 

Regards,

jörgen

[Dan Mahler]

Yes. Trevor's suggestion appears to have worked.

 

This is clearly a matter of learning how to properly structure the SQL Query. I'll get there!

 

Thank you both for your input.

 

DWM

[Trevor Sullivan]

Yes. Trevor's suggestion appears to have worked.

 

This is clearly a matter of learning how to properly structure the SQL Query. I'll get there!

 

Thank you both for your input.

 

DWM

You're welcome. Basically what I did was use a sub-select query to select *all* members of the LMG00033 collection, and then in the outer query, we selected everything that's *not* in the sub-select. Glad it helped!

 

Cheers,

Trevor Sullivan

http://trevorsullivan.net

http://twitter.com/pcgeek86