none
SCCM Collection Query for OU and multiple subnets RRS feed

  • Question

  • 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

  • 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
    Moderator
  • 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
  • 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
    Moderator