SMS 2003 Reroot orphaned collections
'Author Shaun Cassells
'Shaun dot cassells at gmail dot com
'3-19-2007
' This script creates an association in v_CollectToSubCollect table.
' Inputs required
' Server Name
' Server Site Code
' Parent Collection Name
' Collections to query to be made sub collections of parent
' Go or no go. Should be value of "Y" if you want it to go
'For command line input
if Wscript.Arguments.Count = "4" Then
SMSServer = WScript.Arguments(0)
SiteCode = WScript.Arguments(1)
ParentCollection = WScript.Arguments(2)
SubCollection = WScript.Arguments(3)
ElseIf WScript.Arguments.Count = "5" Then
SMSServer = WScript.Arguments(0)
SiteCode = WScript.Arguments(1)
ParentCollection = WScript.Arguments(2)
SubCollection = WScript.Arguments(3)
bDoIT = WScript.Arguments(4)
Else
Wscript.ECHO "*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*"
Wscript.ECHO "please Add the following 4 or 5 inputs"
Wscript.Echo "Note: following utility will only create a root if one does not exist."
Wscript.Echo " Will not change one if one already exists"
Wscript.Echo "*SMS Server"
Wscript.Echo "*SMS SiteCode"
Wscript.Echo "*Parent Collection Name"
Wscript.Echo "*Sub Collection Query"
Wscript.Echo " Ex. '01[0-9][0-9]2007' - will return all Jan 2007 collections"
Wscript.Echo "*Last input: either blank. or a 'Y' to actually reroot collection"
Wscript.ECHO "*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*"
WSCRIPT.QUIT
End If
Set SMSContext = CreateObject("WbemScripting.SWbemNamedValueSet")
'Create the context qualifer object
'Add the standard SMS context qualifiers to the context object
SMSContext.Add "LocaleID", "MS\1033"
Set WshNetwork = WScript.CreateObject("WScript.Network")
SMSContext.Add "MachineName", WshNetwork.ComputerName
SMSContext.Add "ApplicationName", "script DPAddAll"
'Connect WMI
Set objLocator = CreateObject("WbemScripting.SWbemLocator")
Set objWMIService = objLocator.ConnectServer(SMSServer, "root\SMS\site_" & SiteCode)
objWMIService.Security_.ImpersonationLevel = 3 ' impersonationLevel = Impersonate
'Connect SQL
Set cn = CreateObject("ADODB.Connection")
Set cmd = CreateObject("ADODB.Command")
constring = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=SMS_" & SiteCode & ";Data Source="& SMSServer
cn.ConnectionString = constring
cn.Open
Set cmd.ActiveConnection = cn
cmd.Prepared = True
cmd.CommandText = "SELECT DISTINCT CollectionID, Name FROM dbo.v_Collection where Name = '" & ParentCollection & "'"
Set rs = cmd.Execute
Do Until rs.EOF
wscript.echo "Root Package ID " & rs(0) & " " & rs(1)
cmd.CommandText = "SELECT DISTINCT CollectionID, Name FROM dbo.v_Collection where Name like '%" & SubCollection & "%'"
Set rSub = cmd.Execute
Do Until rSub.EOF
cmd.CommandText = "SELECT DISTINCT ParentCollectionID, SubCollectionID FROM dbo.v_CollectToSubCollect where parentCollectionID = '" & rs(0) & "' and subCollectionID = '" & rSub(0) & "'"
Set rExistSub = cmd.Execute
If not rExistSub.EOF Then
'Eventually need to add in code to get SMS_CollectionToSubCollection value if it already exists
'If it does exist and doesn't match change the parent value to match
'Didn't find this as nessecary during outtage March of 2007
'Do Until rExistSub.EOF
'wscript.echo "* Sub Collection ID's " & rSub(0) & " " & rSub(1)
'wscript.echo " * Parent Collection Exists " & rExistSub(0) & " " & rExistSub(1)
' rExistSub.movenext()
'Loop
Else
wscript.echo "* Sub Collection ID's " & rSub(0) & " " & rSub(1)
Set instCollToSubColl = objWMIService.Get("SMS_CollectToSubCollect").SpawnInstance_
instCollToSubColl.parentCollectionID = rs(0)
instCollToSubColl.subCollectionID = rSub(0)
If bDoIT = "Y" Then
instCollToSubColl.Put_
End If
End If
rSub.movenext()
Loop
'test = InputBox("Type 'DIE'","Kill script?","DIE")
'If test = "DIE" Then Wscript.Quit
rs.MoveNext
Loop
cn.close
Set cn = Nothing
Set cmd = Nothing
Set objLocator = Nothing
Set objWMIService = Nothing
Set instCollToSubColl = Nothing
Set SiteCode = nothing
Set SMSServer = nothing