Jump to content


Dan Mahler

Collection Exclusion - Windows Update

Recommended Posts

(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

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Loading...

×
×
  • Create New...