My Opinion: for a collection query, all you care about in the end is that the resourceid added fit all of your WHERE conditions. But I have a problem personally with some of your 'where' conditions.
I have no idea why you think you need to filter on three things:
Operating_System_Name_and0 like '%workstation 10%'
AND
Operating_System_Name_and0 not like '%server%'
AND
and operatingSystem0 like'%Windows 10 Pro%'
If they are going to be a workstation, they won't be a server... so adding the 'not like a server' has no purpose.
If you are filtering by Windows 10 Pro from the OperationsSystem as reported via Hardware Inventoy, I don't see a purpose in filtering by the OSNameAndVersion as reported by discovery.
So to me, the only things you care about is...
- Windows 10 Pro, as reported by inventory
- OU like regional% (reported by Heartbeat Discovery)
- even if it's in regional% for an OU, don't include the ones that might be in STAGING ou
- 3 specific builds, as reported by discovery.
So this is what I think you want... Oh, and try to avoid having leading % in a where like statement. There is no reason to make SQL work harder than it needs to.
and... don't use "not like" in a WQL Collection Query (and should avoid it in sql too, it might mess you up). What you want is a subselect query... example below.
Select SMS_R_System.ResourceID
from SMS_R_System
inner join SMS_G_System_OPERATING_SYSTEM on SMS_G_System_OPERATING_SYSTEM.ResourceID = SMS_R_System.ResourceID
where
SMS_G_System_Operating_SYSTEM.Name like "Microsoft Windows 10 Pro%"
and
SMS_R_System.SystemOUName like "Your.F.Q.D.N/Something/Regional%"
and
SMS_R_System.Build in ("10.0.19043","10.0.19044","10.0.19045")
and
SMS_R_System.ResourceID NOT IN (
Select SMS_R_System.Resourceid from SMS_R_System Where SystemOUName like "Your.F.Q.D.N/Something/Regional%Staging%"
)
Of course, adjust your SystemOUName to be correct for your OUs / FQDN