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:
Basically the system goes as follows:
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
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)