VBS-查找计算机所在的SCCM集合Id、名称和包名称?

如果我理解正确,您希望新记录拥有旧记录的所有成员资格。

在我看来,您可以只关注集合成员身份,只要集合相同,对集合的部署是包、应用程序还是更新都不重要。

因此,首先需要将设备名称转换为resourceid,因为它们是所有sccm表中的主键:

$WS = 'IN-00001236'
$resourceID = (Get-WmiObject -Class "SMS_R_System" -Filter "(Name = '$WS')" | Select-Object -last 1).ResourceID

一旦有了这些,您就可以获得所有集合成员

$collectionMembers = (Get-WmiObject -Class "SMS_FullCollectionMembership" -Filter "(ResourceID = '$resourceID')"  | where { $_.IsDirect -eq $true }).CollectionID

要删除记录,请使用

[void](Get-WmiObject -Class "SMS_R_System" -Filter "(ResourceID = '$resourceID')").Delete()

现在您必须将这些添加到新记录中(首先再次获取resourceID)

Foreach($CollID in $collectionMembers) {
    $CollectionQuery = Get-WmiObject -Class "SMS_Collection" -Filter "CollectionID = '$CollID'"
    $directRule = (Get-WmiObject -List -Class "SMS_CollectionRuleDirect").CreateInstance()
    $directRule.ResourceClassName = "SMS_R_System"
    $directRule.ResourceID = $newResourceID
    [void]$CollectionQuery.AddMemberShipRule($directRule)
}

如果要对循环中的多个设备使用此选项,只需使用 oldrecord,newrecord 使用导入

$records = Import-Csv <path to csv> -Header old,new

然后在所有代码外使用循环,如

foreach ($record in $records) {
  # inside here use $record.old or $record.new for the old/new names like
  # $WS = $record.old
}

现在,我不知道如果您只有一个脚本来完成这项工作,您是否还会需要excel文件,但如果确实需要,您可以使用export csv从powershell创建一个excel可读的csv(可能需要诸如-Delimiter“;”-NoTypeInformation-编码UTF8这样的选项以便于阅读)

理论上,您也可以在powershell中执行整个sql查询

$sqlText = "your query"
$SQLConnection = new-object System.Data.SqlClient.SQLConnection("Server=<sccmserver>;Database=<sccm db>;Integrated Security=SSPI")
$SQLConnection.Open();

$cmd = new-object System.Data.SqlClient.SqlCommand($sqlText, $SQLConnection);    
$reader = $cmd.ExecuteReader()

while ($reader.Read()) {
    $reader["<columnname>"] 
}

并在ps脚本中重新创建文件

最后,我完成了如下VBS。它工作得很好。

Dim connect, sql, resultSet, pth, txt

Set ObjFSO = CreateObject("Scripting.FileSystemObject")
Set connect = CreateObject("ADODB.Connection")
connect.ConnectionString = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=True;Initial Catalog=CM_CAS;Data Source=<SCCMServerIP>;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=<SCCMServerHostName>;Use Encryption for Data=False;Tag with column collation when possible=False;Trusted_Connection=True;"
connect.Open

sql="select CollectionID,CollectionName,packagename,<HostName> as MachineName from v_AdvertisementInfo where CollectionID in (select FCM.CollectionId from dbo.v_R_System r join dbo.v_FullCollectionMembership FCM on R.ResourceID = FCM.ResourceID join dbo.v_Collection C on C.CollectionID = FCM.CollectionID Where R.Name0 = 'HostName') and ProgramName not like '%Remove%'and ProgramName not like '%Un-Install%'and CollectionName not like '%Test%' and CollectionName not like '%temp%' union select CollectionID,CollectionName,ApplicationName,@machname as MachineName from v_ApplicationAssignment where CollectionID in (select FCM.CollectionId from dbo.v_R_System r join dbo.v_FullCollectionMembership FCM on R.ResourceID = FCM.ResourceID join dbo.v_Collection C on C.CollectionID = FCM.CollectionID Where R.Name0 = 'HostName') and CollectionName not like '%Test%' and CollectionName not like '%temp%'"

Set resultSet = connect.Execute(sql)

pth = "C:\test\test.csv"

Set txt = ObjFSO.CreateTextFile(pth, True)

On Error Resume Next
resultSet.MoveFirst
Do While Not resultSet.eof
  txt.WriteLine(resultSet(0) & "," & resultSet(1) & "," & resultSet(2))
  resultSet.MoveNext
Loop

resultSet.Close
connect.Close
Set connect = Nothing