If you are looking for a collection to query all the machines only from a particular OU not the SUB OU .Let say ,I have Top OU called Workstations\comp(120 systems) in eskon.net domain and in turn it has 3 sub OU's OU1(10 systems),OU2(20 systems),OU3(49 systems).
Here is the simple query to get machines only from TOP OU called workstations.
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 = "eskon.net/workstations/comp" /*find all machines in that OU (but also finds machines in subOUs */
AND sms_r_system.resourceID NOT IN (SELECT resourceID FROM sms_r_system where systemouname LIKE "eskon.net/workstations/comp/%") /*remove records for machines in subOUs*/
The above collection should display only machines that are present in comp OU not from the SUB OU's (like OU1,OU2,OU3).
Thanks for sharing, solves my problem! It was indeed the NOT IN /% at the end of the second query.
Thanks 🙂