Tech Community Live: Endpoint Manager edition
Jul 21 2022, 08:00 AM - 12:00 PM (PDT)

Microsoft Endpoint Configuration Manager, Device Collection Queries

%3CLINGO-SUB%20id%3D%22lingo-sub-2533786%22%20slang%3D%22en-US%22%3EMicrosoft%20Endpoint%20Configuration%20Manager%2C%20Device%20Collection%20Queries%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2533786%22%20slang%3D%22en-US%22%3E%3CP%20class%3D%22%22%3EHello%20all%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%20class%3D%22x-hidden-focus%22%3EHere%20is%20the%20question.%20I%20am%20wanting%20to%20limit%20the%20query%20for%20my%20machines%20in%20my%20Domain.%20I%20have%20a%20partially%20working%20query%20however%20I%20am%20failing%20miserably%20on%20the%20exclusions.%3CBR%20%2F%3E%3CBR%20%2F%3EHere%20is%20what%20I%20am%20doing%20and%20wanting%20to%20accomplish.%3C%2FP%3E%3CP%3EI%20want%20to%20create%20a%20new%20query%20to%20include%20only%20Windows%2010%20OS%2C%20and%20on%20those%20same%20systems%20if%20they%20belong%20to%20an%20OU%20I%20do%20not%20want%20them%20included%20in%20the%20query.%3CBR%20%2F%3E%3CBR%20%2F%3EI%20am%20new%20to%20Endpoint%20so%20bear%20with%20me.%3CBR%20%2F%3E%3CBR%20%2F%3EDevice%20Collections%3C%2FP%3E%3CP%3ECreated%20a%20new%20Device%20Collections%20Called%20Software%20Distribution%20(General%20Name)%3CBR%20%2F%3ECreate%20a%20Member%20ship%20rule%20to%20include%20Criteria%20Windows%2010%20OS%20platforms.%20(Works)%3CBR%20%2F%3EAdd%20to%20the%20same%20rule%20to%20try%20and%20Exclude%20devices%20that%20reside%20in%20certain%20AD%20OU's.%20(Does%20not%20work)%3CBR%20%2F%3E%3CBR%20%2F%3EWhat%20am%20I%20missing%3F%3CBR%20%2F%3E%3CBR%20%2F%3Eselect%20SMS_R_SYSTEM.ResourceID%2CSMS_R_SYSTEM.ResourceType%2CSMS_R_SYSTEM.Name%2CSMS_R_SYSTEM.SMSUniqueIdentifier%2CSMS_R_SYSTEM.ResourceDomainORWorkgroup%2CSMS_R_SYSTEM.Client%20from%20SMS_R_System%20where%20SMS_R_System.OperatingSystemNameandVersion%20%3D%20%22Microsoft%20Windows%20NT%20Workstation%2010.0%22%20and%20SMS_R_System.SystemOUName%20!%3D%20%22FOREST%2FDOMAIN%2FLOCATION%2FFIRST%20FLOOR%2FSERVER%20ROOM%22%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThis%20statement%20is%20%22Is%20not%20equal%20to%22%20and%20I%20have%20also%20tried%20%22is%20not%20like%22%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20for%20looking%3C%2FP%3E%3CP%20class%3D%22%22%3ERunning%20latest%20ver%20of%20MECM%20on%20a%20Windows%202019%20Server%20platform%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2533786%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EApp%20Management%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2536168%22%20slang%3D%22en-US%22%3ERe%3A%20Microsoft%20Endpoint%20Configuration%20Manager%2C%20Device%20Collection%20Queries%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2536168%22%20slang%3D%22en-US%22%3EHi%2C%3CBR%20%2F%3EIf%20I%20understand%20your%20need%2C%20I'd%20do%20%3A%3CBR%20%2F%3E1)%20COL%20-%20W10%20%3A%20Collection%20including%20all%20Windows%2010%20computers%20%3A%3CBR%20%2F%3Eselect%20SMS_R_SYSTEM.ResourceID%2CSMS_R_SYSTEM.ResourceType%2CSMS_R_SYSTEM.Name%2CSMS_R_SYSTEM.SMSUniqueIdentifier%2CSMS_R_SYSTEM.ResourceDomainORWorkgroup%2CSMS_R_SYSTEM.Client%20from%20SMS_R_System%20where%20SMS_R_System.OperatingSystemNameandVersion%20like%20%22%25Workstation%2010%25%22%3CBR%20%2F%3E%3CBR%20%2F%3E2)%20COL%20-%20OU%20%3A%20Collection%20including%20all%20computers%20from%20the%20OU%20you%20want%20to%20exclude%20%3A%3CBR%20%2F%3Eselect%20*%20from%20SMS_R_System%20where%20SMS_R_System.SystemOUName%20%3D%20%22FOREST%2FDOMAIN%2FLOCATION%2FFIRST%20FLOOR%2FSERVER%20ROOM%22%3CBR%20%2F%3E%3CBR%20%2F%3E3)%20Finally%2C%20create%20a%20collection%20that%20will%20%3A%3CBR%20%2F%3EInclude%20%3B%20COL%20-%20W10%3CBR%20%2F%3EExclude%20%3A%20COL%20-%20OU%3CBR%20%2F%3EHope%20this%20helps%20you%3CBR%20%2F%3E%3CBR%20%2F%3E%3C%2FLINGO-BODY%3E
Occasional Visitor

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

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