NEW WQL Query Help

[webbth]

I'm hoping there is someone out there with better WQL chops than me that can help with this rather complex query. Right now we have a query in place that takes all computers in configuration manager, looks at the primary users, checks to see if the primary user is in an AD group, and includes them in the collection if they are. What I need to do is either:

• exclude systems with multiple primary users
  or
• exclude systems if any of the primary users are not in the AD group (preferred)

Example:

Computer	Primary Users
CompA	User1, User2, User3
CompB	User2, User3
CompC	User3

User2 and User3 are in ADGroup but User1 is not, so the WQL query should return only CompB and CompC.

Current WQL:
select distinct 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 JOIN SMS_UserMachineRelationship ON SMS_R_System.ResourceID=SMS_UserMachineRelationship.ResourceID JOIN SMS_R_User ON SMS_UserMachineRelationship.UniqueUserName=SMS_R_User.UniqueUserName WHERE SMS_UserMachineRelationship.Types=1 AND SMS_UserMachineRelationship.IsActive=1 AND SMS_R_User.UserGroupName="DOMAIN\\GROUP"

I tried adding AND NOT SMS_R_User.UserGroupName!="DOMAIN\\GROUP" on the end of the query but that didn't have the right result, and I'm at the end of my WQL knowledge. Thanks in advance!

 

[Garth]

Why don't you create a collection based on the the security group only. aka what exactly are you going to do with collection?

 

[webbth]

So this is a collection for Windows updates, so it has to be device based. The catch is we need to target devices used by specific users, hence the filter on the primary user and AD group membership. The challenge is that I need a more complex query than the "any primary user is in AD group" that I have currently.

 

[webbth]

Alright, I think I have a solution for anyone that may be looking at something similar. What I ended up having to do was create a user collection based on the AD group (collection ID CM00000 in the example code), and then use that user collection in my query.

I then had to create two device collections, Collection1 has a query that finds devices with primary users that are not in the AD group user collection. Collection2 has a query that finds devices with primary users that are in the AD group user collection and excludes Collection1 (to get rid of devices with multiple primaries, some of whom may not be in the target group).

Now I can use Collection2 for my Windows updates test collection and it will automatically contain all the devices who's primary user(s) are in my IT department, but not any devices where a primary user is not in the IT department.

Collection1 (devices with a primary user NOT in the target group)

SELECT DISTINCT
SMS_R_SYSTEM.ResourceID,
SMS_R_SYSTEM.ResourceType,
SMS_R_SYSTEM.Name,
SMS_R_SYSTEM.SMSUniqueIdentifier,
SMS_R_SYSTEM.ResourceDomainORWorkgroup,
SMS_R_SYSTEM.Client,
SMS_UserMachineRelationship.UniqueUserName

FROM SMS_R_System
LEFT JOIN SMS_UserMachineRelationship ON
SMS_UserMachineRelationship.ResourceID = SMS_R_System.ResourceId
    
WHERE SMS_UserMachineRelationship.Types=1
AND SMS_UserMachineRelationship.IsActive=1
AND SMS_UserMachineRelationship.UniqueUserName NOT IN
(SELECT SMS_R_User.UniqueUserName
FROM SMS_R_User
JOIN SMS_CM_RES_COLL_CM00000 ON
SMS_CM_RES_COLL_CM00000.ResourceID = SMS_R_User.ResourceID)

Collection2 (devices with a primary user in the target group, also add Collection1 as a membership exclusion)

SELECT DISTINCT
SMS_R_SYSTEM.ResourceID,
SMS_R_SYSTEM.ResourceType,
SMS_R_SYSTEM.Name,
SMS_R_SYSTEM.SMSUniqueIdentifier,
SMS_R_SYSTEM.ResourceDomainORWorkgroup,
SMS_R_SYSTEM.Client,
SMS_UserMachineRelationship.UniqueUserName

FROM SMS_R_System
LEFT JOIN SMS_UserMachineRelationship ON
SMS_UserMachineRelationship.ResourceID = SMS_R_System.ResourceId
    
WHERE SMS_UserMachineRelationship.Types=1
AND SMS_UserMachineRelationship.IsActive=1
AND SMS_UserMachineRelationship.UniqueUserName IN
(SELECT SMS_R_User.UniqueUserName
FROM SMS_R_User
JOIN SMS_CM_RES_COLL_CM00000 ON
SMS_CM_RES_COLL_CM00000.ResourceID = SMS_R_User.ResourceID)