locked
How to exclude a collection from sccm collection query? RRS feed

  • Question

  • Hi,

    I have a collection that includes only Windows XP systems but I want to exclude certain Windows XP from this collection.  What I did is create a collection (call Excluded Workstations collection that contains all the workstations) I want to exclude.

    My question is: How do I exclude Excluded Workstations collection from my Windows XP Collection so that these specific machines do not get the advertisement?

    Ex: Windows  XP Collection = Computer 1, 2, 3, 4, 5, 6

    Excluded Workstations = Computer 1 and 2 (although they are windows XP I still want to exclude them).

    Do you think that if I simply link that Excluded Workstation as a subgroup to my WindowsXPCollection and exclude subcollections from my advertisement it will work? But my Windows XP Collection contains the computers I don't need.

    Please advise. Thanks.

    Wednesday, May 12, 2010 7:36 PM

Answers

  • It would be easier to add to the windows XP collection you created

    where SMS_G_System_COMPUTER_SYSTEM.Name not in ( "comp1", "comp2" )

    Exclude the other collection

    and ResourceId not in (select ResourceID from SMS_CM_RES_COLL_XXXxxxxx)

    where the XXX are the collection ID you want to exclude.


    http://www.sccm-tools.com http://sms-hints-tricks.blogspot.com
    Wednesday, May 12, 2010 7:45 PM

All replies

  • It would be easier to add to the windows XP collection you created

    where SMS_G_System_COMPUTER_SYSTEM.Name not in ( "comp1", "comp2" )

    Exclude the other collection

    and ResourceId not in (select ResourceID from SMS_CM_RES_COLL_XXXxxxxx)

    where the XXX are the collection ID you want to exclude.


    http://www.sccm-tools.com http://sms-hints-tricks.blogspot.com
    Wednesday, May 12, 2010 7:45 PM
  • The only way I know of is to explicitly exclude them in the query. So, for each workstation you want to exclude, add, AND System.Name is not equal to Computer1, etc. Once you build the first one in query building, you can view the source and copy the source for that statement as a template, replacing Computer1 with the subsequent machines.
    Wednesday, May 12, 2010 7:51 PM
  • Worked! Thank you!

    Wednesday, May 12, 2010 9:26 PM
  • Sorry for the thread bump of the year, but i cant manage to get this to work.

    My query looks like:

    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 inner join SMS_G_System_SoftwareFile on SMS_G_System_SoftwareFile.ResourceID = SMS_R_System.ResourceId where SMS_G_System_SoftwareFile.FilePath like "%\\Program Files\\Internet Explorer\\" or SMS_G_System_SoftwareFile.FilePath like "%\\Program\\Internet Explorer\\" and SMS_G_System_SoftwareFile.FileName like "iexplore.exe" and SMS_G_System_SoftwareFile.FileVersion like "7.%" and not SMS_R_System.ResourceId in (select ResourceID from SMS_CM_RES_COLL_SCA00357)

    It querys IE 7 version and after that it should filter and delete a few that does not want the new IE version. SCA is sitecode and 00357 col id. However the lab computer still stays in both collections..

    If i look at the subselected query it says enter a valid constat at the "red !".

    Hope that anyone nows why this happens.


    Kind regards

    Friday, May 18, 2012 1:37 PM
  • how is this relevent to the current thread?  Could you please post a new thread with your question.  Let's not confuse the people that read this thread a year from now. 

    http://www.sccm-tools.com http://sms-hints-tricks.blogspot.com

    Friday, May 18, 2012 3:23 PM
  • I agree with Matthew but there are two issue with your query.

    1. You are missing brackets
    2. You NOT is in the wrong place.

    Try this:

    http://tinyurl.com/7nx7yks


    http://www.enhansoft.com/

    Saturday, May 19, 2012 2:48 PM
  • I wrote a blog post about this how to do this in SCCM 2007

    http://joejoeinc.blogspot.com.au/2012/05/sccm-joining-collection-and-excluding.html


    • Edited by JoeJoeInc Tuesday, January 21, 2014 10:05 PM
    Wednesday, May 30, 2012 1:26 AM