I was recently working on an issue involving the MMC hanging and pegging the CPU when trying to import a large report that was exported from another site. I found the root cause and a KB will be released soon documenting the details for the issue. There wasn't a good workaround available (basically create a new report and copy/paste the SQL query into the new report instead of using the import/export report functionality) so I looked into ways to transfer a report programmatically.

Sure enough you can use the SMS_Report class to clone a report from one site to another using the SDK. This ended up being tricky because of some "lazy" properties in the SMS_Report class, such as the SQLQuery property. If you view an SMS_Report instance you will notice that this property is NULL, even though the SMS Admin console shows a valid SQL Query for that report. In order to actually view the contents of this property you have to explicitly get the SMS_Report instance from one site to another. Here is the vbscript I wrote to do this.

 

'Rslaten 09/08/2006
'Run locally on the central site server where the report exists

'1st parameter should be the report ID
'The remaining parameters should be the site codes of the...
'...child sites for which you want to import the report
'Example: cscript.exe ImportReport.vbs 166 PRI SEC CLD
'This will take report 166 and import it into the PRI, SEC, and CLD child sites

'Start Script
Main

Sub Main
    Dim oServices, iReportID, aSites
    
    'Make connection to WMI
    Set oServices = GetObject("winMgmts:" & GetSMSNameSpace())
    
    'Get Command Line Arguments
    iReportID = GetReportID()
    aSites = GetSiteNamespaces(oServices)
    
    'Make sure report exists
    If ValidateReport(oServices, iReportID) Then
        TransferReport oServices, iReportID, aSites
    Else
        WScript.Echo "Report ID " & iReportID & " is not a valid report ID"
        DisplayHelp()
        WScript.Quit
    End If

    Set oServices = Nothing
End Sub

Sub TransferReport(oWMI, iID, aSites)
    On Error Resume Next
    Dim oQuery, oReport, oServices, oSite, oNewReport, oLazySQLQuery
    
    'Get main report object
    Set oQuery = oWMI.ExecQuery("select * from SMS_Report where ReportID = " & iID)
    For each oReport in oQuery
        For each oSite in aSites
            'Connect to the remote server
            Set oServices = GetObject("winMgmts:" & oSite.SMSNameSpace)
            If Err.number <> 0 Then
                WScript.Echo "Error connecting to SMS namespace:" & oSite.SMSNameSpace
                WScript.Echo "Error = " & Err.number & " - " & Err.Description
                WScript.Echo "Will not transfer report to " & oSite.ServerName
            Else
                'Create a new report and clone it so it's identical to the original
                Set oNewReport = oServices.Get("SMS_Report").SpawnInstance_
                
                'Set properties
                oNewReport.Category = oReport.Category
                oNewReport.Comment = oReport.Comment
                oNewReport.DrillThroughColumns = oReport.DrillThroughColumns
                oNewReport.DrillThroughURL = oReport.DrillThroughURL
                oNewReport.GraphCaption = oReport.GraphCaption
                oNewReport.GraphType = oReport.GraphType
                oNewReport.GraphXCol = oReport.GraphXCol
                oNewReport.GraphYCol = oReport.GraphYCol
                oNewReport.Name = oReport.Name
                oNewReport.NumPrompts = oReport.NumPrompts
                oNewReport.RefreshInterval = oReport.RefreshInterval
                oNewReport.ReportParams = oReport.ReportParams
                oNewReport.StatusMessageDetailSource = oReport.StatusMessageDetailSource
                oNewReport.XColLabel = oReport.XColLabel
                oNewReport.YColLabel = oReport.YColLabel
                
                'Set lazy properties
                'Note, report ID's may not match for the 1st two properties
                oNewReport.DrillThroughReportID = oReport.DrillThroughReportID
                oNewReport.DrillThroughReportPath = oReport.DrillThroughReportPath
                oNewReport.MachineDetail = oReport.MachineDetail
                oNewReport.MachineSource = oReport.MachineSource
                Set oLazySQLQuery = oWMI.Get("SMS_Report.ReportID=" & iID)
                oNewReport.SQLQuery = oLazySQLQuery.SQLQuery
                
                'Write the instance to WMI
                oNewReport.Put_()
                
                If Err.number <> 0 Then
                    WScript.Echo "Report failed to transfer to site " & oSite.SiteCode
                    WScript.Echo "Error = " & Err.number & " - " & Err.Description
                Else
                    WScript.Echo "Report transfered to site " & oSite.SiteCode
                End If
                
            End If
        Next
    Next
    Set oServices = Nothing
    Set oLazySQLQuery = Nothing
End Sub

Function ValidateReport(oWMI, iID)
    On Error Resume Next
    Dim oQuery, oItem
    Set oQuery = oWMI.ExecQuery("select * from SMS_Report where ReportID = " & iID)
    For Each oItem in oQuery
        WScript.Echo "Report to transfer: " & oItem.Name
        ValidateReport = TRUE
    Next
    Set oQuery = Nothing
End Function

Function GetReportID()
    On Error Resume Next
    Dim iID
    iID = WScript.Arguments(0)
    If Err.number <> 0 Then
        WScript.Echo "Failed to get report ID from command line!"
        DisplayHelp()
        WScript.Quit
    Else
        GetReportID = iID
    End If
End Function

Function GetSiteNameSpaces(oWMI)
    On Error Resume Next
    Dim aArg, oSites, oSite, i, oSiteServer, sNameSpace
    Dim aAllData(), aSiteServerNames(), aArgs()
    
    'Get child site codes off command line
    If WScript.Arguments.Count < 2 Then
        WScript.Echo "Failed to get child site codes from command line!"
        DisplayHelp()
        WScript.Quit
    End if
    
    For i = 1 to WScript.Arguments.Count - 1
        ReDim Preserve aArgs(i-1)
        aArgs(i-1) = WScript.Arguments(i)
    Next
    
    'Make sure we actually got some child site codes
    If i <= 1 Then
        WScript.Echo "Failed to get child site codes from command line"
        DisplayHelp()
        WScript.Quit
    End If
    
    'Query WMI to get SMS server names for each site code
    i = 0
    Set oSites = oWMI.ExecQuery("select * from SMS_Site")
    For each oSite in oSites
        For each aArg in aArgs
            If UCASE(aArg) = UCASE(oSite.SiteCode) Then
                Set oSiteObject = New ChildSite
                oSiteObject.SiteCode = oSite.SiteCode
                oSiteObject.ServerName = oSite.ServerName
                ReDim Preserve aSiteServerNames(i)
                Set aSiteServerNames(i) = oSiteObject
                i = i + 1
            End If
        Next
    Next
    
    'Connect to each site server to get the SMS namespace
    i = 0
    For each oSiteServer in aSiteServerNames
        sNameSpace = GetRemoteSMSNameSpace(oSiteServer.ServerName)
        If sNameSpace <> FALSE Then
            Set oSiteObject = New ChildSite
            oSiteObject.SiteCode = oSiteServer.SiteCode
            oSiteObject.ServerName = oSiteServer.ServerName
            oSiteObject.SMSNameSpace = sNameSpace
            ReDim Preserve aAllData(i)
            Set aAllData(i) = oSiteObject
            i = i + 1
        End If
    Next
    
    GetSiteNameSpaces = aAllData
    
    Set oSites = Nothing
    Set oSite = Nothing
    Set oSiteServer = Nothing
End Function

Function GetSMSNameSpace()
    On Error Resume Next
    Dim colNameSpaceQuery, refitem, refWMI
    Set refWMI = GetObject("winMgmts:\root\sms")
    If Err.number <> 0 Then
        WScript.Echo "Error connecting to SMS namespace"
        DisplayHelp()
        WScript.Quit
    End If
    Set colNameSpaceQuery = refWMI.ExecQuery("select * from SMS_ProviderLocation")
    For Each refitem in colNameSpaceQuery
        GetSMSNameSpace = refitem.NamespacePath
    Next
    Set colNameSpaceQuery = Nothing
    Set refitem = Nothing
    Set refWMI = Nothing
End Function

Function GetRemoteSMSNameSpace(sServer)
    On Error Resume Next
    Dim colNameSpaceQuery, refitem, refWMI
    Set refWMI = GetObject("winMgmts:\\" & sServer & "\root\sms")
    If Err.number <> 0 Then
        WScript.Echo "Error connecting to SMS namespace on " & sServer
        WScript.Echo "Error = " & Err.number & " - " & Err.Description
        WScript.Echo "Will not transfer report to " &sServer
        GetRemoteSMSNameSpace = FALSE
    End If
    Set colNameSpaceQuery = refWMI.ExecQuery("select * from SMS_ProviderLocation")
    For Each refitem in colNameSpaceQuery
        GetRemoteSMSNameSpace = refitem.NamespacePath
    Next
    Set colNameSpaceQuery = Nothing
    Set refitem = Nothing
    Set refWMI = Nothing
End Function

Sub DisplayHelp()
    WScript.Echo "Syntax for ImportReport.vbs (must be run on parent site server)"
    WScript.Echo "cscript.exe ImportReport.vbs <ReportID> <ChildSiteCode(s)>"
    WScript.Echo "Example: cscript.exe ImportReport.vbs 166 PRI SEC CLD"
End Sub

Class ChildSite
    Public SiteCode
    Public ServerName
    Public SMSNamespace
End Class