Sign in
 
Home2012Previous VersionsLibraryForumsGallery
System Center TechCenter > System Center Configuration Manager Forums > Configuration Manager 2007 SDK > SCCM 2007 Collection Membership Query with variables

Answered SCCM 2007 Collection Membership Query with variables

    •  
      Thursday, August 06, 2009 8:54 PM
       
       
      Sign In to Vote
      0
      Sign In to Vote
      We have many sub-OU's that share the same name i.e. "WORK", "PLAY" etc... and we would like to build collections that contain the computers inside ALL of said sub-OU's.  In other words, we already have collections based on specific OU names ... "CONTOSO.COM/ADMIN_OFFICE/WORK" and "CONTOSO.COM/ADMIN_OFFICE/PLAY" as well as other base OU's like "SHIPPING" with the same sub-OU names that we would like to make one HUGE collection from.  I just don't know how to use variables in a query.  Thanks.  =)
       

    Answers

    • Friday, August 07, 2009 12:15 AM
      Moderator
       
       Answered
      Sign In to Vote
      0
      Sign In to Vote
      Time to learn a little bit about SQL, WQL actually. In WQL (and SQL) the wild card character is the percent sign (%). If you use the wildcard character in a WHERE clause you must also use the "like" operator.

      Thus, your OU query would look something like this:

      SELECT * FROM SMS_R_System
      WHERE SystemOUName like "%PLAY%"

      This will match all systems that have an OU that has the word PLAY in it. Queries in ConfigMgr are case-insensitive.

      The built in query editor will help you build this query without having to know the above syntax although it does help to know WQL.
      Jason | http://myitforum.com/cs2/blogs/jsandys | http://blogs.catapultsystems.com/jsandys/default.aspx | Twitter @JasonSandys
      • Marked As Answer by Playdoh Tuesday, August 11, 2009 1:31 PM
      •  
       

    All Replies

    • Friday, August 07, 2009 12:15 AM
      Moderator
       
       Answered
      Sign In to Vote
      0
      Sign In to Vote
      Time to learn a little bit about SQL, WQL actually. In WQL (and SQL) the wild card character is the percent sign (%). If you use the wildcard character in a WHERE clause you must also use the "like" operator.

      Thus, your OU query would look something like this:

      SELECT * FROM SMS_R_System
      WHERE SystemOUName like "%PLAY%"

      This will match all systems that have an OU that has the word PLAY in it. Queries in ConfigMgr are case-insensitive.

      The built in query editor will help you build this query without having to know the above syntax although it does help to know WQL.
      Jason | http://myitforum.com/cs2/blogs/jsandys | http://blogs.catapultsystems.com/jsandys/default.aspx | Twitter @JasonSandys
      • Marked As Answer by Playdoh Tuesday, August 11, 2009 1:31 PM
      •  
       
    • Tuesday, August 11, 2009 1:33 PM
       
       
      Sign In to Vote
      0
      Sign In to Vote
      Thanks, Jason.  I did look at some of the syntax as you suggested and was able to find a viable query.  Thanks for your help!

      -CJA
       
    • Friday, August 26, 2011 1:10 PM
       
       
      Sign In to Vote
      0
      Sign In to Vote

      Hi Jason,

      I have used this query and I am able to pull only 23 computers out of 30 from OU "Finance Computers"

      I want to pull all 30 objects from this OU into SCCM collection

      My query is as follows:

       

      SELECT * FROM SMS_R_System
      WHERE SystemOUName like "%Finance Computers%"

       

       

      Does it take time to pull objects or am I missing any thing..?

       

      Any help will be appreciated.

       

      Many thanks...
      Mohammad Ameenuddin
       
    • Friday, August 26, 2011 3:17 PM
      Moderator
       
       
      Sign In to Vote
      0
      Sign In to Vote

      (This should be a new thread)

      Has a resource actually been created in ConfigMgr for all 30 systems in that OU? AD Discovery will not create resources in ConfigMgr for systems that are disabled or that it cannot resolves via DNS. Thus, just because a computer object exists in AD does not mean a corresponding resource exists in ConfigMgr.

      Jason | http://myitforum.com/cs2/blogs/jsandys | Twitter @JasonSandys