Transferring reports from your central site to your child site(s)



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

ImportReport.vb_


Comments (1)

  1. I was recently working on an issue involving the MMC hanging and pegging the CPU when trying to import