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!!!

view plainprint?

1. On Error Resume Next  
2. Option Explicit  
3.   
4. Dim strSourceServer : strSourceServer = "SMSServer"  
5. Dim strTargetServer : strTargetServer = "SCCMServer"  
6. Dim strSourceSiteCode : strSourceSiteCode = "000"  
7. Dim strTargetSiteCode : strTargetSiteCode = "000"  
8. Dim objSourceWMIService, objTargetWMIService  
9. dim AllReports, objReport, newReport  
10. Dim AllContainers, objContainer  
11. Dim objLazyProperties, Path, Report  
12. Dim strNewContainerID  
13. Dim AllParameters, objParameter  
14. Dim intContainerObjectType : intContainerObjectType = 8  
15. Dim objReportParams, strReportParamClass, newReportParam  
16. Dim bolReportParamsAvailable : bolReportParamsAvailable = False  
17. Dim intReportParameterCount : intReportParameterCount = 0  
18. Dim arrTempParam()  
19.   
20.   
21. Dim objFSO : Set objFSO = CreateObject("Scripting.FileSystemObject")  
22. Dim objLogFile : Set objLogFile = objFSO.CreateTextFile("MigrateReports.log")  
23.   
24. Set objSourceWMIService = GetObject("winmgmts://" & strSourceServer & "\root\sms\site_" & strSourceSiteCode)  
25. Set objTargetWMIService = GetObject("winmgmts://" & strTargetServer & "\root\sms\site_" & strTargetSiteCode)  
26.   
27. 'Creating all Containers  
28. Call CreateContainer  
29.   
30. 'Only Reports in SubContainers  
31. 'Set AllContainers = objSourceWMIService.ExecQuery("SELECT * FROM SMS_ObjectContainerItem WHERE ObjectType='" &intContainerObjectType &"'")   
32. 'For Each objContainer In AllContainers  
33.    
34.  'Set AllReports = objSourceWMIService.ExecQuery("SELECT * FROM SMS_Report WHERE SecurityKey='" &objContainer.InstanceKey &"'")  
35.  Set AllReports = objSourceWMIService.ExecQuery("SELECT * FROM SMS_Report")  
36.    
37.  For Each objReport In AllReports  
38.    
39.   'Check if there are Report parameter  
40.   Set objReport = objSourceWMIService.Get("SMS_Report.ReportID=" & objReport.ReportID)     
41.   On Error Resume Next  
42.     
43.   'For each Parameter in the Report  
44.   For i = 0 To UBound(objReport.ReportParams)  
45.    Set objReportParams = objReport.ReportParams(i)  
46.    strReportParamClass=objReportParams.Path_.Class  
47.    If strReportParamClass = "SMS_ReportParameter" Then  
48.     bolReportParamsAvailable = True  
49.     ReDim Preserve arrTempParam(i)  
50.   
51.     Set newReportParam = objSourceWMIService.Get("SMS_ReportParameter").SpawnInstance_  
52.     newReportParam.VariableName = objReportParams.VariableName  
53.     newReportParam.PromptText = objReportParams.PromptText  
54.     newReportParam.DefaultValue = objReportParams.DefaultValue  
55.     newReportParam.AllowEmpty = objReportParams.AllowEmpty  
56.     newReportParam.SampleValueSQL = objReportParams.SampleValueSQL  
57.       
58.     Set arrTempParam(i) = newReportParam  
59.     objLogFile.WriteLine Now &" - Report Parameter available " &objReport.Name  
60.       
61.    Else  
62.     bolReportParamsAvailable = False  
63.     objLogFile.WriteLine Now &" - No Report Parameter (skipping) " &objReport.Name  
64.     End If  
65.   Next  
66.   On Error GoTo 0  
67.     
68.     
69.   For Each item In arrTempParam  
70.    WScript.Echo item.VariableName  
71.   Next  
72.    
73.   Set newReport = objTargetWMIService.Get("SMS_Report").SpawnInstance_  
74.       
75.     newReport.Category = objReport.Category  
76.    newReport.Comment = objReport.Comment  
77.    newReport.DrillThroughColumns = objReport.DrillThroughColumns  
78.    newReport.DrillThroughURL = objReport.DrillThroughURL  
79.    newReport.GraphCaption = objReport.GraphCaption  
80.    newReport.GraphType = objReport.GraphType  
81.    newReport.GraphXCol = objReport.GraphXCol  
82.    newReport.GraphYCol = objReport.GraphYCol  
83.    newReport.Name = "NEW " &objReport.Name  
84.    newReport.NumPrompts = objReport.NumPrompts  
85.    newReport.RefreshInterval = objReport.RefreshInterval  
86.     
87.    If bolReportParamsAvailable = true Then   
88.     newReport.ReportParams = arrTempParam  
89.       
90.     bolReportParamsAvailable = False  
91.     Set objReportParams = Nothing  
92.     Set newReportParam = Nothing  
93.     strReportParamClass = ""  
94.   End If  
95.      
96.    newReport.StatusMessageDetailSource = objReport.StatusMessageDetailSource  
97.    newReport.XColLabel = objReport.XColLabel  
98.    newReport.YColLabel = objReport.YColLabel  
99.    newReport.DrillThroughReportID = objReport.DrillThroughReportID  
100.    newReport.DrillThroughReportPath = objReport.DrillThroughReportPath  
101.    newReport.MachineDetail = objReport.MachineDetail  
102.    newReport.MachineSource = objReport.MachineSource  
103.     
104.   'Get SQL Query of the Report and Count the Report Parameter  
105.   Set objLazyProperties = objSourceWMIService.Get("SMS_Report.ReportID=" &objReport.ReportID)  
106.   intReportParameterCount = Len(objLazyProperties.SQLQuery) - Len(Replace(objLazyProperties.SQLQuery, "@", ""))  
107.   newReport.SQLQuery = objLazyProperties.SQLQuery  
108.     
109.    If objLazyProperties Is Nothing Then  
110.     objLogFile.WriteLine Now &" - Error: Cannot read SQLQuery from Report " &objReport.Name &". Skipping this Report"  
111.    Else  
112.     objLogFile.WriteLine Now &" - Creating Report "  &objReport.Name  
113.       
114.     'Write the instance to WMI  
115.     Path = newReport.Put_()  
116.      
117.     'Get automatically assigned package ID  
118.     'Set Report=objTargetWMIService.Get(Path)  
119.        
120.     'Getting new Container ID  
121.     'strNewContainerID = GetNewContainerID(ContainerIDToName( objContainer.ContainerNodeID))  
122.      
123.     'Moving the Report into the Container  
124.     'Call MoveReportInToContainer(strNewContainerID, Report.SecurityKey)  
125.       
126.     'Clean up  
127.     Set objLazyProperties = Nothing   
128.    End If  
129.   
130.  Next  
131. 'Next  
132.   
133. WScript.Echo "Done"  
134.   
135. Sub CreateContainer  
136.   
137. Dim AllSourceContainer, objSourceContainer  
138. Dim objTargetContainer  
139.   
140.     Set AllSourceContainer = objSourceWMIService.ExecQuery("SELECT * FROM SMS_ObjectContainerNode WHERE ObjectType='" &intContainerObjectType &"'")  
141.     For Each objSourceContainer In AllSourceContainer  
142.     
143.      objLogFile.WriteLine Now &" - Migrating Container " &objSourceContainer.Name  
144.         Set objTargetContainer = objTargetWMIService.Get("SMS_ObjectContainerNode").SpawnInstance_()  
145.      objTargetContainer.Name = objSourceContainer.Name  
146.      objTargetContainer.ObjectType = objSourceContainer.ObjectType  
147.      objTargetContainer.ParentContainerNodeID = objSourceContainer.ParentContainerNodeID  
148.      On Error Resume Next  
149.      objTargetContainer.Put_  
150.      On Error GoTo 0  
151.       
152.     Next  
153.       
154. End Sub  
155.   
156. Function ContainerIDToName(ContainerID)  
157. Dim AllContainers, objContainer  
158.   
159. Set AllContainers = objSourceWMIService.ExecQuery("SELECT * FROM SMS_ObjectContainerNode WHERE ObjectType='" &intContainerObjectType &"' AND ContainerNodeID='" &ContainerID &"'")   
160. For Each objContainer In AllContainers  
161.  ContainerIDToName = objContainer.Name  
162. Next  
163.   
164. End Function  
165.   
166. Function GetNewContainerID(ContainerName)  
167. Dim AllSourceContainer, objSourceContainer  
168.    
169. Set AllSourceContainer = objTargetWMIService.ExecQuery("SELECT * FROM SMS_ObjectContainerNode WHERE ObjectType='" &intContainerObjectType &"' and Name = '" &ContainerName &"'")  
170.   
171. For Each objSourceContainer In AllSourceContainer  
172.  GetNewContainerID = objSourceContainer.ContainerNodeID  
173. Next  
174.   
175. End Function  
176.   
177. Function MoveReportInToContainer(ContainerID, ReportID)  
178. Dim folderItem  
179.   
180. If ContainerID <> "" Then  
181.   
182.  objLogFile.WriteLine Now &" - Moving Report " &ReportID &" into Container " &ContainerID  
183.    
184.  Set folderItem = objTargetWMIService.Get("SMS_ObjectContainerItem").SpawnInstance_()  
185.  folderItem.InstanceKey = ReportID  
186.  folderItem.ObjectType = "8"  
187.  folderItem.ContainerNodeID = ContainerID  
188.  folderItem.Put_  
189. Else  
190.  objLogFile.WriteLine Now &" - No need to move the Report " &ReportID &", because it's listed in the root"  
191. End If  
192.       
193. End Function  

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