8/20/2010

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

No comments: