PowerShell查询SMS_CombinedDeviceResources报错，求问题原因分析

我拥有一个AD组，想要查询该组内每个成员当前登录的计算机，为此编写了如下PowerShell脚本：

$SCCMServer       = "sccmserver"
$SCCMSiteCode     = "TST"

$WMIParam         = @{ComputerName = $SCCMServer
                      NameSpace    = "root\sms\site_$SCCMSiteCode"}

$UsersMemberOfGroup = (Get-WMIObject @WMIParam -Query "select SMS_R_User.UniqueUserName from SMS_R_User where SMS_R_User.UserGroupName LIKE '%A_SQL_SERVER_MANAGEMENT_STUDIO'" | Select-Object -Property UniqueUserName)
ForEach ($UserMemberOfGroup in $UsersMemberOfGroup)
{
  $Computer = Get-WmiObject @WMIParam -Query "Select Name, CurrentLogonUser From  SMS_CombinedDeviceResources Where (Name like 'W10-%' and CurrentLogonUser = '$($UserMemberOfGroup.UniqueUserName)')"
  Write-Host "User $($UserMemberOfGroup.UniqueUserName) is logged on to $($Computer.Name)."
} 

该脚本执行时出现通用错误。

修改后的脚本不再报错，但运行速度极慢：

$SCCMServer       = "sccmserver"
$SCCMSiteCode     = "TST"

$WMIParam         = @{ComputerName = $SCCMServer
                      NameSpace    = "root\sms\site_$SCCMSiteCode"}

$UsersMemberOfGroup = (Get-WMIObject @WMIParam -Query "select SMS_R_User.UniqueUserName from SMS_R_User where SMS_R_User.UserGroupName LIKE '%A_SQL_SERVER_MANAGEMENT_STUDIO'" | Select-Object -Property UniqueUserName)
ForEach ($UserMemberOfGroup in $UsersMemberOfGroup)
{
  $Computer = Get-WmiObject @WMIParam -Query "Select Name, CurrentLogonUser From SMS_CombinedDeviceResources Where Name like 'W10-%'" | Where-Object {$_.CurrentLogonUser -eq $($UserMemberOfGroup.UniqueUserName)}
  Write-Host "User $($UserMemberOfGroup.UniqueUserName) is logged on to $($Computer.Name)."
} 

我的问题是：第一段脚本存在什么问题？恳请提供反馈建议。

第一段脚本的核心问题 #

1. WQL字符串拼接的语法错误
   当用户名包含单引号、空格等特殊字符时，直接拼接'$($UserMemberOfGroup.UniqueUserName)'会破坏WQL查询的语法结构。比如用户名是O'Neil，拼接后查询会变成CurrentLogonUser = 'O'Neil'，单引号未转义导致WQL解析失败，触发通用错误。

2. 未处理空引用情况
   如果用户当前没有登录任何符合W10-%规则的计算机，$Computer会返回$null，此时访问$Computer.Name会抛出空引用异常，这也是通用错误的常见来源。

3. 查询效率的潜在隐患
   循环内每次单独发起WMI查询会增加与SCCM服务器的交互次数，本身就存在效率低下的问题。

改进方案 #

1. 转义用户名中的特殊字符 #

按照WQL规则，单引号需要用两个单引号转义，避免破坏查询结构：

$escapedUserName = $UserMemberOfGroup.UniqueUserName -replace "'", "''"

将转义后的用户名代入WQL查询即可避免语法错误。

2. 处理空值与空引用 #

提前过滤用户列表中的空值，同时在输出前判断$Computer是否存在：

# 过滤空用户名
$UsersMemberOfGroup = (Get-WMIObject @WMIParam -Query "select SMS_R_User.UniqueUserName from SMS_R_User where SMS_R_User.UserGroupName LIKE '%A_SQL_SERVER_MANAGEMENT_STUDIO'" | Select-Object -ExpandProperty UniqueUserName) | Where-Object { $_ }

# 输出前判断是否存在登录机器
ForEach ($user in $UsersMemberOfGroup) {
  $escapedUserName = $user -replace "'", "''"
  $Computer = Get-WmiObject @WMIParam -Query "Select Name, CurrentLogonUser From SMS_CombinedDeviceResources Where (Name like 'W10-%' and CurrentLogonUser = '$escapedUserName')"
  if ($Computer) {
    Write-Host "User $user is logged on to $($Computer.Name)."
  } else {
    Write-Host "User $user is not logged on to any W10-* computer."
  }
}

3. 合并查询提升效率 #

将两次WMI查询合并为一次JOIN查询，减少与SCCM服务器的交互次数：

$SCCMServer       = "sccmserver"
$SCCMSiteCode     = "TST"

$WMIParam         = @{ComputerName = $SCCMServer
                      NameSpace    = "root\sms\site_$SCCMSiteCode"}

# 一次性查询所有目标用户及其登录的计算机
$query = @"
SELECT u.UniqueUserName, d.Name
FROM SMS_R_User u
JOIN SMS_CombinedDeviceResources d ON u.UniqueUserName = d.CurrentLogonUser
WHERE u.UserGroupName LIKE '%A_SQL_SERVER_MANAGEMENT_STUDIO'
AND d.Name LIKE 'W10-%'
"@

$results = Get-WmiObject @WMIParam -Query $query

foreach ($result in $results) {
    Write-Host "User $($result.UniqueUserName) is logged on to $($result.Name)."
}

# 补充显示未登录任何机器的用户
$allUsers = (Get-WMIObject @WMIParam -Query "select UniqueUserName from SMS_R_User where UserGroupName LIKE '%A_SQL_SERVER_MANAGEMENT_STUDIO'" | Select-Object -ExpandProperty UniqueUserName) | Where-Object { $_ }
$usersWithDevices = $results | Select-Object -ExpandProperty UniqueUserName -Unique
$usersWithoutDevices = $allUsers | Where-Object { $_ -notin $usersWithDevices }

foreach ($user in $usersWithoutDevices) {
    Write-Host "User $user is not logged on to any W10-* computer."
}

内容的提问来源于stack exchange，提问作者TheStingPilot