Forum Discussion

James_Robertson_1's avatar
James_Robertson_1
Copper Contributor
Jul 09, 2021

Microsoft Endpoint Configuration Manager, Device Collection Queries

Hello all,

 

Here is the question. I am wanting to limit the query for my machines in my Domain. I have a partially working query however I am failing miserably on the exclusions.

Here is what I am doing and wanting to accomplish.

I want to create a new query to include only Windows 10 OS, and on those same systems if they belong to an OU I do not want them included in the query.

I am new to Endpoint so bear with me.

Device Collections

Created a new Device Collections Called Software Distribution (General Name)
Create a Member ship rule to include Criteria Windows 10 OS platforms. (Works)
Add to the same rule to try and Exclude devices that reside in certain AD OU's. (Does not work)

What am I missing?

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.OperatingSystemNameandVersion = "Microsoft Windows NT Workstation 10.0" and SMS_R_System.SystemOUName != "FOREST/DOMAIN/LOCATION/FIRST FLOOR/SERVER ROOM"

 

This statement is "Is not equal to" and I have also tried "is not like"

 

Thanks for looking

Running latest ver of MECM on a Windows 2019 Server platform

1 Reply

  • Aventador06's avatar
    Aventador06
    Copper Contributor

    Hi,
    If I understand your need, I'd do :
    1) COL - W10 : Collection including all Windows 10 computers :
    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.OperatingSystemNameandVersion like "%Workstation 10%"

    2) COL - OU : Collection including all computers from the OU you want to exclude :
    select * from SMS_R_System where SMS_R_System.SystemOUName = "FOREST/DOMAIN/LOCATION/FIRST FLOOR/SERVER ROOM"

    3) Finally, create a collection that will :
    Include : COL - W10
    Exclude : COL - OU
    Hope this helps you

Resources