locked
Can't exclude machines from dynamic collection query RRS feed

  • Question

  • I have a dynamic query on a collection.   Within the target group of machines I want to exclude 3.  They are mg11757, mg11758, mg11759.  I have tried to use a wildcard and say "not like" and I've tried to exclude them explicity by using is equal too and nothing works.   I update collection membership, wait 5 min etc.... and when I "show members" on my collection they are still there.  What is wrong with my logic? 

    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_COMPUTER_SYSTEM on SMS_G_System_COMPUTER_SYSTEM.ResourceID = SMS_R_System.ResourceId where SMS_R_System.IPSubnets like "10.10.132.%" or SMS_R_System.IPAddresses like "10.10.131.%" or SMS_R_System.IPSubnets like "10.10.130.%" or SMS_R_System.IPSubnets like "10.10.133.%" and SMS_G_System_COMPUTER_SYSTEM.Name not like "MGH-%" and SMS_G_System_COMPUTER_SYSTEM.Name not like "MGMG%" and SMS_G_System_COMPUTER_SYSTEM.Name not like "MG1175%"


    mqh7

    Monday, February 8, 2016 5:50 PM

Answers

  • Just like with order of operations in math, there is an order operations to operators in SQL. Thus, you need to add parens in the appropriate place to control this.

    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_COMPUTER_SYSTEM on SMS_G_System_COMPUTER_SYSTEM.ResourceID = SMS_R_System.ResourceId where (SMS_R_System.IPSubnets like "10.10.132.%" or SMS_R_System.IPAddresses like "10.10.131.%" or SMS_R_System.IPSubnets like "10.10.130.%" or SMS_R_System.IPSubnets like "10.10.133.%") and (SMS_G_System_COMPUTER_SYSTEM.Name not like "MGH-%" and SMS_G_System_COMPUTER_SYSTEM.Name not like "MGMG%" and SMS_G_System_COMPUTER_SYSTEM.Name not like "MG1175%")


    Jason | http://blog.configmgrftw.com | @jasonsandys

    • Marked as answer by mqh7 Monday, February 8, 2016 6:59 PM
    Monday, February 8, 2016 6:42 PM