SCCM User Collection Query: user=inADGroup & software=Installed

Question

I would like to write a query for a user collection in SCCM. Our IT department would like to work with three different user collections per software package:

• [softwareName] - Installed
• [softwareName] - to Install
• [softwareName] - to Uninstall

Basically the system goes as follows:

• installed: contains users that are in the software AD group and have the software installed on their machine
• to Install: contains users that are in the software AD group but do not have the software installed yet ( the package will be assigned to this group)
• to Uninstall: contains users that have the software installed on their machine but are not in the AD group anymore.

I am aware that the install status can be tracked through "monitoring" but I would like to have a solution as stated above.

The Question: I am having trouble writing the query for the "installed" group. My code snippet is as follows:

select
SMS_R_USER.ResourceID,SMS_R_USER.ResourceType,SMS_R_USER.Name,SMS_R_USER.UniqueUserName,SMS_R_USER.WindowsNTDomain 
from 
SMS_R_User 
where 
SMS_R_User.SecurityGroupName = "DOMAIN\\GROUP" 

So far it is working, I now add the part for the Product-retrieval query:

and 
select
SMS_R_System_ADD_REMOVE_PROGRAMS.ProdID
from
SMS_R_System_ADD_REMOVE_PROGRAMS
where
SMS_R_System_ADD_REMOVE_PROGRAMS.ProdID 
like 
"{PRODUCTCODE}"

This part is not working, I am getting a "syntax error or unsupported in SCCM design view for queries". I am well aware that there might be a syntax error, but I also have the feeling the SMS_R_System is not supported in the user collection query design. I am new to SQL but I was hoping for a few tips. thanks!

EDIT: using SCCM 1710

Answer 1

Solved it myself:

USERS IN AD GROUP WITH SOFTWARE INSTALLED ON DEVICE

select
SMS_R_User.UniqueUserName, SMS_R_System.name,SMS_G_System_ADD_REMOVE_PROGRAMS.DisplayName, SMS_G_System_ADD_REMOVE_PROGRAMS.ProdID,SMS_G_System_ADD_REMOVE_PROGRAMS_64.DisplayName, SMS_G_System_ADD_REMOVE_PROGRAMS_64.ProdID
from SMS_R_System
INNER JOIN SMS_UserMachineRelationship ON SMS_UserMachineRelationship.ResourceId = SMS_R_System.ResourceId
INNER JOIN SMS_R_User ON SMS_UserMachineRelationship.UniqueUserName = SMS_R_User.UniqueUserName
LEFT OUTER JOIN SMS_G_System_ADD_REMOVE_PROGRAMS ON SMS_G_System_ADD_REMOVE_PROGRAMS.ResourceID = SMS_R_System.ResourceId AND SMS_G_System_ADD_REMOVE_PROGRAMS.ProdID = "{1441FE72-8D71-4B05-B89F-8C3DA0D2D3A6}"
LEFT OUTER JOIN SMS_G_System_ADD_REMOVE_PROGRAMS_64 ON SMS_G_System_ADD_REMOVE_PROGRAMS_64.ResourceID = SMS_R_System.ResourceId AND SMS_G_System_ADD_REMOVE_PROGRAMS_64.ProdID = "{1441FE72-8D71-4B05-B89F-8C3DA0D2D3A6}"
where (SMS_R_User.SecurityGroupName = "BAMBEL\\SCM_TST_BVO") 
AND 
SMS_UserMachineRelationship.Types = 1
AND 
( SMS_G_System_ADD_REMOVE_PROGRAMS_64.DisplayName <> "" OR SMS_G_System_ADD_REMOVE_PROGRAMS.DisplayName <> "" OR SMS_G_System_ADD_REMOVE_PROGRAMS_64.DisplayName <> NULL OR SMS_G_System_ADD_REMOVE_PROGRAMS.DisplayName <> NULL)

USERS IN AD GROUP WITH SOFTWARE NOT INSTALLED ON DEVICE

select
SMS_R_User.UniqueUserName, SMS_R_System.name,SMS_G_System_ADD_REMOVE_PROGRAMS.DisplayName, SMS_G_System_ADD_REMOVE_PROGRAMS.ProdID,SMS_G_System_ADD_REMOVE_PROGRAMS_64.DisplayName, SMS_G_System_ADD_REMOVE_PROGRAMS_64.ProdID
from SMS_R_System
INNER JOIN SMS_UserMachineRelationship ON SMS_UserMachineRelationship.ResourceId = SMS_R_System.ResourceId
INNER JOIN SMS_R_User ON SMS_UserMachineRelationship.UniqueUserName = SMS_R_User.UniqueUserName
LEFT OUTER JOIN SMS_G_System_ADD_REMOVE_PROGRAMS ON SMS_G_System_ADD_REMOVE_PROGRAMS.ResourceID = SMS_R_System.ResourceId AND SMS_G_System_ADD_REMOVE_PROGRAMS.ProdID = "{1441FE72-8D71-4B05-B89F-8C3DA0D2D3A6}"
LEFT OUTER JOIN SMS_G_System_ADD_REMOVE_PROGRAMS_64 ON SMS_G_System_ADD_REMOVE_PROGRAMS_64.ResourceID = SMS_R_System.ResourceId AND SMS_G_System_ADD_REMOVE_PROGRAMS_64.ProdID = "{1441FE72-8D71-4B05-B89F-8C3DA0D2D3A6}"
where (SMS_R_User.SecurityGroupName = "BAMBEL\\SCM_TST_BVO") 
AND 
SMS_UserMachineRelationship.Types = 1
AND 
( SMS_G_System_ADD_REMOVE_PROGRAMS_64.DisplayName IS NULL AND SMS_G_System_ADD_REMOVE_PROGRAMS.DisplayName IS NULL)