I have a simple script that can create collections from reading an excel sheet. All you is a sheet with
A=Collection
B=Comment
C=GlobalGroup
first row is for titles.
Change the first four strxxx files with your environment settings.
Normally I use this script for creating advertisements, packages, etc. I've cleaned up it a little bit. It is still dirty, but it works.
I've trouble to run it on x64 win7, so I run it from XP x86.
script:
'On Error Resume Next
Option Explicit
strServerNaam= "SERVER04"
strSoftwareCollection="P0100173"
strDomain1="DOMAINNAME"
strQueryName = "AD Global Group Query"
'--------------------------------------------------------------------------------------------------------------------------------
Dim strExcelPath, objExcel, objSheet, intRow, objExcel2, objSheet2
Dim strA, strB, strC
Dim strQuery, strQueryName, strSoftwareCollection, strServerNaam, strPackageFolder, strAdvertisementFolder, strSetupRegel, strDomain1
Dim objUser, objFSO, initFSO, intErrorFound, objPath, strPath
Dim objOSDetails(1)
Dim objNewConsoleFolderItem, objNewConsoleadFolderItem, FolderIdentifier, adFolderIdentifier
Dim objFolder, objadFolder, objSWbemLocator, objSWbemServices, ProviderLoc, Location, Site, newPackage, path, package, varPackageID, id, strGUID, newProgram, AllDPs, DP, newDP, Collection, varCollectionID, objloc, objSMS, results, Loc, strGetLine, instCollection, clsQueryRule, instQueryRule
Dim newCollection, newAdvertisement
adFolderIdentifier=0
FolderIdentifier=0
Set objPath = CreateObject ("Scripting.FileSystemObject")
strPath = objPath.GetFolder(".")
Set ObjFSO = CreateObject("UserAccounts.CommonDialog")
ObjFSO.Filter = "Excel Files|*.xls|All Files|*.*"
ObjFSO.InitialDir = strPath
ObjFSO.FilterIndex = 3
InitFSO = ObjFSO.ShowOpen
If InitFSO = False Then
Wscript.Echo "Script Error: Install Excel"
Wscript.Quit
End If
' Spreadsheet file for import.
strExcelPath = ObjFSO.Filename
' Bind to Excel object.
On Error Resume Next
Set objExcel = CreateObject("Excel.Application")
If Err.Number <> 0 Then
On Error GoTo 0
Wscript.Echo "Excel not found."
Wscript.Quit
End If
On Error GoTo 0
' Open spreadsheet.
On Error Resume Next
objExcel.Workbooks.Open strExcelPath
If Err.Number <> 0 Then
On Error GoTo 0
Wscript.Echo "Spreadsheet could not be opened: " & strExcelPath
Wscript.Quit
End If
On Error GoTo 0
' Bind to worksheet.
Set objSheet = objExcel.ActiveWorkbook.Worksheets(1)
'eerste regel bevat kolomtitels begin bij regel 2
intRow = 2
REM A=Collection
REM B=Comment
REM C=GlobalGroup
Do While objSheet.Cells(intRow, 1).Value <> ""
strA = objSheet.Cells(intRow, 1).Value
strB = objSheet.Cells(intRow, 2).Value
strC = objSheet.Cells(intRow, 3).Value
'Controleer de input van de Excel file
IntErrorFound = 0
If strA = "" Then IntErrorFound = 1
'Connect to provider namespace for computer.
Set objSWbemLocator = CreateObject("WbemScripting.SWbemLocator")
Set objSWbemServices= objSWbemLocator.ConnectServer(strServerNaam, "root\sms")
Set ProviderLoc = objSWbemServices.InstancesOf("SMS_ProviderLocation")
For Each Location In ProviderLoc
If Location.ProviderForLocalSite = True Then
Set objSWbemServices = objSWbemLocator.ConnectServer _
(Location.Machine, "root\sms\site_" + Location.SiteCode)
Set Site = objSWbemServices.Get("SMS_Site='" & Location.SiteCode & "'")
End If
Next
'--------------------------------------------------------------------------------------------------------------------------------
'Maak de collection aan.
Set newCollection = objSWbemServices.Get("SMS_Collection").SpawnInstance_()
newCollection.Name = strA
newCollection.OwnedByThisSite = True
newCollection.comment = strB
path=newCollection.Put_
'Get collection ID
Set Collection=objSWbemServices.Get(path)
VarCollectionID= Collection.CollectionID
'Koppel Collection
Dim newCollectionRelation
Set newCollectionRelation = objSWbemServices.Get( "SMS_CollectToSubCollect" ).SpawnInstance_()
newCollectionRelation.parentCollectionID = strSoftwareCollection
newCollectionRelation.subCollectionID = VarCollectionID
newCollectionRelation.Put_
'--------------------------------------------------------------------------------------------------------------------------------
'AD Query
strQuery = "select SMS_R_SYSTEM.ResourceID,SMS_R_SYSTEM.ResourceType,SMS_R_SYSTEM.Name,SMS_R_SYSTEM.SMSUniqueIdentifier,SMS_R_SYSTEM.ResourceDomainORWorkgroup,SMS_R_SYSTEM.Client from SMS_R_System where SMS_R_System.SystemGroupName = ' "& strDomain1 & "\\" & strC & "'"
Set objLoc = CreateObject("WbemScripting.SWbemLocator")
Set objSMS = objLoc.ConnectServer(strServerNaam, "root\sms")
Set Results = objSMS.ExecQuery("SELECT * From SMS_ProviderLocation WHERE ProviderForLocalSite = true")
For Each Loc In Results
If Loc.ProviderForLocalSite = True Then
Set objSMS = objLoc.ConnectServer(Loc.Machine, "root\sms\site_" & Loc.SiteCode )
End If
Next
strGetLine = "SMS_Collection.CollectionID='" & VarCollectionID & "'"
Set instCollection = objSMS.Get("SMS_Collection.CollectionID='" & VarCollectionID & "'")
Set clsQueryRule = objSMS.Get("SMS_CollectionRuleQuery")
Set instQueryRule = clsQueryRule.SpawnInstance_
instQueryRule.QueryExpression = strQuery
instQueryRule.RuleName = strQueryName
instCollection.AddMembershipRule instQueryRule
intRow = intRow + 1
Loop
'--------------------------------------------------------------------------------------------------------------------------------
'Close the workbook.
objExcel.ActiveWorkbook.Close
'Quit Excel input
objExcel.Application.Quit
'Clean up.
Set objUser = Nothing
Set objExcel = Nothing
Set objSheet = Nothing
Wscript.Echo "Done!."