locked
SCCM Collection Query for OU and multiple subnets RRS feed

 > 

  • Question

  • Question
    Sign in to vote
    0
    Sign in to vote

    Hi

    I have created a query that will pull members based on their OU and also if they are in specific subnet. What I would like to do, is modify the query so it can search more than 1 subnet (as some departments are spread over multiple subnets)

    So in the below example i might want to add 'like "10.50%" in addition to "10.15%", does anyone know how I can teak this rule?

    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.SystemOUName = 'domain/ou' and SMS_R_System.IPSubnets like "10.15%"

    Thursday, August 1, 2019 4:10 PM

All replies

  • Question
    Sign in to vote
    0
    Sign in to vote

    A simple Or operator will do 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 
where SMS_R_System.SystemOUName = 'domain/ou' and (SMS_R_System.IPSubnets like "10.15.%" or
 SMS_R_System.IPSubnets like "10.50.%")

    Jason | https://home.configmgrftw.com | @jasonsandys

    Thursday, August 1, 2019 6:18 PM
  • Question
    Sign in to vote
    0
    Sign in to vote
    Hi,

    Jason is right, we can also add or SMS_R_System.IPSubnets like "10.50.%" directly without brackets, the result is the same:

    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.SystemOUName = 'domain/ou' and SMS_R_System.IPSubnets like "10.15.%" or
 SMS_R_System.IPSubnets like "10.50.%"

    Regards,
    Allen

    Please remember to mark the replies as answers if they help.

    If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com.

    Friday, August 2, 2019 2:54 AM
  • Question
    Sign in to vote
    0
    Sign in to vote

    > we can also add or SMS_R_System.IPSubnets like "10.50.%" directly without brackets,

    While technically valid, no, you can't as that would change the logic of the where clause and thus not fulfill the requirement from the OP.

    Jason | https://home.configmgrftw.com | @jasonsandys

    Friday, August 2, 2019 12:50 PM