I want to use a WQL query to filter out devices in an SCCM device collection with the following criteria: Name contains CS, MON, AVS, WUG, FS or IBM and not DR or LON
Here's my query:
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 SMS_R_System.Name like "%CS%" or SMS_R_System.Name like "%MON%" or SMS_R_System.Name like "%AVS%" or SMS_R_System.Name like "%WUG%" or SMS_R_System.Name like "%FS%" and SMS_R_System.Name not like "%DR%" and SMS_R_System.Name not like "%LON%"
I've also tried the 'not' operator before the field name:
...and not SMS_R_System.Name like "%DR%" and not SMS_R_System.Name like "%LON%"
However, the 'not' operator gets ignored either way.
It works fine if I just used one 'like' and one 'not like' argument together but adding any additional 'like' values seems to stop this from working.
I'm pretty sure what I'm trying to do can be done but am obviously missing something. Any ideas?
Using advice provided by Syberdoor I managed to get this working by using parenthesis around the like and not like operators effectively creating one like and one not like group rather than many:
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 (SMS_R_System.Name like "%CS%" or SMS_R_System.Name like "%MON%" or SMS_R_System.Name like "%AVS%" or SMS_R_System.Name like "%WUG%" or SMS_R_System.Name like "%FS%" or SMS_R_System.Name like "%IBM%") and not (SMS_R_System.Name like "%DR%" or SMS_R_System.Name like "%LON%")
Spot on thanks.