我想在SCCM中为用户集合编写一个查询。 我们的 IT 部门希望每个软件包使用三个不同的用户集合:
- [软件名称] - 已安装
- [软件名称] - 安装
- [软件名称] - 卸载
基本上系统如下:
- 已安装:包含软件 AD 组中的用户,并且其计算机上安装了该软件
- 安装:包含软件 AD 组中但尚未安装软件的用户(包将分配给此组)
- 卸载:包含在其计算机上安装了软件但不再位于 AD 组中的用户。
我知道可以通过"监控"跟踪安装状态,但我希望有如上所述的解决方案。
问题:我在为"已安装"组编写查询时遇到问题。我的代码片段如下:
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"
到目前为止,它正在工作,我现在为产品检索查询添加部分:
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}"
这部分不起作用,我收到"语法错误或 SCCM 设计视图中不支持查询"。我很清楚可能存在语法错误,但我也觉得用户集合查询设计不支持SMS_R_System。我是SQL的新手,但我希望得到一些提示。 谢谢!
编辑:使用SCCM 1710
自己解决了:
设备上安装了软件的 AD 组中的用户
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)
广告组中未在设备上安装软件的用户
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)