For example, you can create a collection of users that are a member of the Human Resources organizational unit in Active Directory Domain Services. I thought it might be useful to share out a few of my most commonly used queries. Is there a way to get the query to NOT include child … These collections demonstrate different queries you can use to create all the collection you need. We’ll start off by creating a sub folder under the device collections and call it Active Directory OU Structure. SCCM Query Per Computer Name I've been creating collections under my SCCM Console and successfully have done two queries where the first is based on the computer account object OUs. Here, you will get solutions related to SCCM. Do this for the rest of your OUs in Active Directory or just the ones you want to keep a collection for. Create new Device Collection ,Name the collection ,limit the collection to All Systems  ,Add rule and select Query rule ,name the collection again ,select Edit Query statement, Click on Criteria ,select start Burst icon ,click Select, select Attribute class as System Resource and Attribute  as, Click Ok ,select Operator as like and Value is your domain name/HK/LI/%PROD%. Parent OU can be DBS,CT,CX or anything under each Business Unit. I do this by querying the SCCM inventory, SMS_G_System_COMPUTER_SYSTEM.SystemType which returns “x64-based PC”or “X86-based PC”. Then for each machine we're trying to find the user that used that machine the most, i.e. Also, if you’re looking to further your knowledge in SCCM,  Be sure to check out Learn SCCM in a month of Lunches. Ensuring SCCM is collecting the information you want to search on. Click Criteria under Query Statement Properties and then click Show Query Language. Example of Query Rule by OU from Active Directory. A simple sql query that will get you the hostname , IP address , OU location and operating system details from your SCCM database . SCCM Configmgr How to Create collections based on OU that contains DEV UAT PROD etc, Hi, You looking for report for systems in specific ou? Hi, I am Gokul TG. The thing is when i create my query based on OU, it also includes the Child OUS. On Criteria Properties dialog box Criteria value as … So to get all computers from PROD/DR OU for Hong Kong Business Unit ,we will use wildcard character (%) with like operator in WQL Query . See the OSD Training Manual. ResourceID, SMS_R_USER. If you are trying to find the systems in the root of an OU but not within any sub-OUs, then just include the root OU’s trailing slash when writing your filtering sub-query, like this: Here are some useful queries for System Center Configuration Manager that you can use to create collections. Specify Query name and click Edit Query Statement. This is a good start, but you can make the WQL query even shorter than this. Select s.Netbios_Name0,ip.IP_Addresses0, s.Distinguished_Name0,s.operatingSystem0, s.Operating_System_Name_and0 from v_RA_System_IPAddresses AS IP inner join v_R_System AS S on ip.ResourceID=s.ResourceID Click Ok ,Ok ,Ok and accept the default steps you see the screen. Query rule. Hi, Click on edit query statement, then Show query language and paste in the following: select SMS_R_USER. The customer told us to create SCCM collections based on the Active Directory OU. Due to the nature of information and technical data which can change without notice and are beyond our control, we expressly disclaim any and all liability on reliance of the information presented. the primary user. Posted on March 22, 2017 by Eswar Koneti ... You can also achieve this using SQL query which is also listed in this blog post. Select OK multiple times to complete the query and when you get back to the SCCM Console, you should see your Collection populated with multiple computers. SCCM Query Rules Based On Active Directory Group Membership Posted on June 25, 2014 The ability to dynamically add computers to device collections in SCCM is useful because it means that software can be deployed simply by adding a computer into the relevant Active Directory group. You looking for report for systems in specific ou? Let’s find out SCCM Application Deployment Custom Report SQL Query with Collection. March 9, 2017. If I simply query the entire OU I get more machines than I want, that's why I'm trying to create a collection based on the machine name. SCCM Configmgr SQL WQL query compare 2 or more collections to get the difference. The install usually takes about 5 - 10 minutes to complete. © 2021 the Sysadmin Channel. Click Edit Query Statement to Opens the Query Statement Properties dialog box where you can create a query to use as the rule for the SCCM user dynamic collection. Enter your email address to subscribe to this blog and receive notifications of new posts by email. Remember to backup your XmlStorage folder in the .\AdminConsole root.