Skip to content

Collection Exclusion - Windows Update

Featured Replies

(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

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

  • Author

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

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

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

Account

Navigation

Search

Search

Configure browser push notifications

Chrome (Android)
  1. Tap the lock icon next to the address bar.
  2. Tap Permissions → Notifications.
  3. Adjust your preference.
Chrome (Desktop)
  1. Click the padlock icon in the address bar.
  2. Select Site settings.
  3. Find Notifications and adjust your preference.