vbScript - Migrate Reports from SMS to SCCM

This script will migrate all advertisements from on site sms/sccm to another one. It should also migrate the prompts in the report. But this is a very tricky part
Be careful: Never run the script in your production until you've tested it!!!

On Error Resume Next
Option Explicit

Dim strSourceServer : strSourceServer = "SMSServer"
Dim strTargetServer : strTargetServer = "SCCMServer"
Dim strSourceSiteCode : strSourceSiteCode = "000"
Dim strTargetSiteCode : strTargetSiteCode = "000"
Dim objSourceWMIService, objTargetWMIService
dim AllReports, objReport, newReport
Dim AllContainers, objContainer
Dim objLazyProperties, Path, Report
Dim strNewContainerID
Dim AllParameters, objParameter
Dim intContainerObjectType : intContainerObjectType = 8
Dim objReportParams, strReportParamClass, newReportParam
Dim bolReportParamsAvailable : bolReportParamsAvailable = False
Dim intReportParameterCount : intReportParameterCount = 0
Dim arrTempParam()


Dim objFSO : Set objFSO = CreateObject("Scripting.FileSystemObject")
Dim objLogFile : Set objLogFile = objFSO.CreateTextFile("MigrateReports.log")

Set objSourceWMIService = GetObject("winmgmts://" & strSourceServer & "\root\sms\site_" & strSourceSiteCode)
Set objTargetWMIService = GetObject("winmgmts://" & strTargetServer & "\root\sms\site_" & strTargetSiteCode)

'Creating all Containers
Call CreateContainer

'Only Reports in SubContainers
'Set AllContainers = objSourceWMIService.ExecQuery("SELECT * FROM SMS_ObjectContainerItem WHERE ObjectType='" &intContainerObjectType &"'") 
'For Each objContainer In AllContainers
 
 'Set AllReports = objSourceWMIService.ExecQuery("SELECT * FROM SMS_Report WHERE SecurityKey='" &objContainer.InstanceKey &"'")
 Set AllReports = objSourceWMIService.ExecQuery("SELECT * FROM SMS_Report")
 
 For Each objReport In AllReports
 
  'Check if there are Report parameter
  Set objReport = objSourceWMIService.Get("SMS_Report.ReportID=" & objReport.ReportID)   
  On Error Resume Next
  
  'For each Parameter in the Report
  For i = 0 To UBound(objReport.ReportParams)
   Set objReportParams = objReport.ReportParams(i)
   strReportParamClass=objReportParams.Path_.Class
   If strReportParamClass = "SMS_ReportParameter" Then
    bolReportParamsAvailable = True
    ReDim Preserve arrTempParam(i)

    Set newReportParam = objSourceWMIService.Get("SMS_ReportParameter").SpawnInstance_
    newReportParam.VariableName = objReportParams.VariableName
    newReportParam.PromptText = objReportParams.PromptText
    newReportParam.DefaultValue = objReportParams.DefaultValue
    newReportParam.AllowEmpty = objReportParams.AllowEmpty
    newReportParam.SampleValueSQL = objReportParams.SampleValueSQL
    
    Set arrTempParam(i) = newReportParam
    objLogFile.WriteLine Now &" - Report Parameter available " &objReport.Name
    
   Else
    bolReportParamsAvailable = False
    objLogFile.WriteLine Now &" - No Report Parameter (skipping) " &objReport.Name
    End If
  Next
  On Error GoTo 0
  
  
  For Each item In arrTempParam
   WScript.Echo item.VariableName
  Next
 
  Set newReport = objTargetWMIService.Get("SMS_Report").SpawnInstance_
    
    newReport.Category = objReport.Category
   newReport.Comment = objReport.Comment
   newReport.DrillThroughColumns = objReport.DrillThroughColumns
   newReport.DrillThroughURL = objReport.DrillThroughURL
   newReport.GraphCaption = objReport.GraphCaption
   newReport.GraphType = objReport.GraphType
   newReport.GraphXCol = objReport.GraphXCol
   newReport.GraphYCol = objReport.GraphYCol
   newReport.Name = "NEW " &objReport.Name
   newReport.NumPrompts = objReport.NumPrompts
   newReport.RefreshInterval = objReport.RefreshInterval
  
   If bolReportParamsAvailable = true Then 
    newReport.ReportParams = arrTempParam
    
    bolReportParamsAvailable = False
    Set objReportParams = Nothing
    Set newReportParam = Nothing
    strReportParamClass = ""
  End If
   
   newReport.StatusMessageDetailSource = objReport.StatusMessageDetailSource
   newReport.XColLabel = objReport.XColLabel
   newReport.YColLabel = objReport.YColLabel
   newReport.DrillThroughReportID = objReport.DrillThroughReportID
   newReport.DrillThroughReportPath = objReport.DrillThroughReportPath
   newReport.MachineDetail = objReport.MachineDetail
   newReport.MachineSource = objReport.MachineSource
  
  'Get SQL Query of the Report and Count the Report Parameter
  Set objLazyProperties = objSourceWMIService.Get("SMS_Report.ReportID=" &objReport.ReportID)
  intReportParameterCount = Len(objLazyProperties.SQLQuery) - Len(Replace(objLazyProperties.SQLQuery, "@", ""))
  newReport.SQLQuery = objLazyProperties.SQLQuery
  
   If objLazyProperties Is Nothing Then
    objLogFile.WriteLine Now &" - Error: Cannot read SQLQuery from Report " &objReport.Name &". Skipping this Report"
   Else
    objLogFile.WriteLine Now &" - Creating Report "  &objReport.Name
    
    'Write the instance to WMI
    Path = newReport.Put_()
   
    'Get automatically assigned package ID
    'Set Report=objTargetWMIService.Get(Path)
     
    'Getting new Container ID
    'strNewContainerID = GetNewContainerID(ContainerIDToName( objContainer.ContainerNodeID))
   
    'Moving the Report into the Container
    'Call MoveReportInToContainer(strNewContainerID, Report.SecurityKey)
    
    'Clean up
    Set objLazyProperties = Nothing 
   End If

 Next
'Next

WScript.Echo "Done"

Sub CreateContainer

Dim AllSourceContainer, objSourceContainer
Dim objTargetContainer

    Set AllSourceContainer = objSourceWMIService.ExecQuery("SELECT * FROM SMS_ObjectContainerNode WHERE ObjectType='" &intContainerObjectType &"'")
    For Each objSourceContainer In AllSourceContainer
  
     objLogFile.WriteLine Now &" - Migrating Container " &objSourceContainer.Name
        Set objTargetContainer = objTargetWMIService.Get("SMS_ObjectContainerNode").SpawnInstance_()
     objTargetContainer.Name = objSourceContainer.Name
     objTargetContainer.ObjectType = objSourceContainer.ObjectType
     objTargetContainer.ParentContainerNodeID = objSourceContainer.ParentContainerNodeID
     On Error Resume Next
     objTargetContainer.Put_
     On Error GoTo 0
    
    Next
    
End Sub

Function ContainerIDToName(ContainerID)
Dim AllContainers, objContainer

Set AllContainers = objSourceWMIService.ExecQuery("SELECT * FROM SMS_ObjectContainerNode WHERE ObjectType='" &intContainerObjectType &"' AND ContainerNodeID='" &ContainerID &"'") 
For Each objContainer In AllContainers
 ContainerIDToName = objContainer.Name
Next

End Function

Function GetNewContainerID(ContainerName)
Dim AllSourceContainer, objSourceContainer
 
Set AllSourceContainer = objTargetWMIService.ExecQuery("SELECT * FROM SMS_ObjectContainerNode WHERE ObjectType='" &intContainerObjectType &"' and Name = '" &ContainerName &"'")

For Each objSourceContainer In AllSourceContainer
 GetNewContainerID = objSourceContainer.ContainerNodeID
Next

End Function

Function MoveReportInToContainer(ContainerID, ReportID)
Dim folderItem

If ContainerID <> "" Then

 objLogFile.WriteLine Now &" - Moving Report " &ReportID &" into Container " &ContainerID
 
 Set folderItem = objTargetWMIService.Get("SMS_ObjectContainerItem").SpawnInstance_()
 folderItem.InstanceKey = ReportID
 folderItem.ObjectType = "8"
 folderItem.ContainerNodeID = ContainerID
 folderItem.Put_
Else
 objLogFile.WriteLine Now &" - No need to move the Report " &ReportID &", because it's listed in the root"
End If
    
End Function