SCCM PatchMgmt - Custom Reports

Here are some SCCM Patch Managenment Reports which i've created for my customers

Maintenance/Patch - Windows and each Computer

view plainprint?

1. select   
2. v_Collection.Name as CollectionName,   
3. v_ServiceWindow.Description,   
4. v_FullCollectionMembership.Name as Computername    
5. from v_ServiceWindow   
6. inner join v_FullCollectionMembership on (v_FullCollectionMembership.CollectionID = v_ServiceWindow.CollectionID)   
7. inner join v_Collection on (v_Collection.CollectionID = v_FullCollectionMembership.CollectionID)   
8. order By v_Collection.Name   

select 
v_Collection.Name as CollectionName, 
v_ServiceWindow.Description, 
v_FullCollectionMembership.Name as Computername  
from v_ServiceWindow 
inner join v_FullCollectionMembership on (v_FullCollectionMembership.CollectionID = v_ServiceWindow.CollectionID) 
inner join v_Collection on (v_Collection.CollectionID = v_FullCollectionMembership.CollectionID) 
order By v_Collection.Name 

Required Updates for a specific Computer

view plainprint?

1. select   
2. v_R_System.name0 as 'Computername',   
3. v_UpdateInfo.Title as 'Updatename',   
4. v_StateNames.Statename   
5. from   
6. v_StateNames,   
7. v_Update_ComplianceStatusAll   
8. Inner Join v_R_System On (v_R_System.ResourceID = v_Update_ComplianceStatusAll.ResourceID)   
9. Inner Join v_UpdateInfo On (v_UpdateInfo.CI_ID = v_Update_ComplianceStatusAll.CI_ID)   
10. where   
11. v_StateNames.TopicType = 500 and   
12. v_StateNames.StateID = v_Update_ComplianceStatusAll.Status and   
13. v_R_System.name0 = @Computername and   
14. Statename = 'Update is required'   

select 
v_R_System.name0 as 'Computername', 
v_UpdateInfo.Title as 'Updatename', 
v_StateNames.Statename 
from 
v_StateNames, 
v_Update_ComplianceStatusAll 
Inner Join v_R_System On (v_R_System.ResourceID = v_Update_ComplianceStatusAll.ResourceID) 
Inner Join v_UpdateInfo On (v_UpdateInfo.CI_ID = v_Update_ComplianceStatusAll.CI_ID) 
where 
v_StateNames.TopicType = 500 and 
v_StateNames.StateID = v_Update_ComplianceStatusAll.Status and 
v_R_System.name0 = @Computername and 
Statename = 'Update is required' 

Count of Required Updates for all Computers

view plainprint?

1. select   
2. v_R_System.Name0 as 'Computername',   
3. Count(v_StateNames.Statename) as 'Required Updates'   
4. from   
5. v_StateNames,   
6. v_Update_ComplianceStatusAll   
7. Inner Join v_R_System On (v_R_System.ResourceID = v_Update_ComplianceStatusAll.ResourceID)   
8. Inner Join v_UpdateInfo On (v_UpdateInfo.CI_ID = v_Update_ComplianceStatusAll.CI_ID)   
9. where   
10. v_StateNames.TopicType = 500 and   
11. v_StateNames.StateID = v_Update_ComplianceStatusAll.Status and   
12. v_StateNames.Statename = 'Update is required'   
13. Group By v_R_System.Name0   

select 
v_R_System.Name0 as 'Computername', 
Count(v_StateNames.Statename) as 'Required Updates' 
from 
v_StateNames, 
v_Update_ComplianceStatusAll 
Inner Join v_R_System On (v_R_System.ResourceID = v_Update_ComplianceStatusAll.ResourceID) 
Inner Join v_UpdateInfo On (v_UpdateInfo.CI_ID = v_Update_ComplianceStatusAll.CI_ID) 
where 
v_StateNames.TopicType = 500 and 
v_StateNames.StateID = v_Update_ComplianceStatusAll.Status and 
v_StateNames.Statename = 'Update is required' 
Group By v_R_System.Name0 

Number of Computers requiring an Update

view plainprint?

1. select   
2. v_UpdateInfo.Title as 'Updatename',   
3. Count(v_R_System.name0) as 'Count of Computers'   
4. from   
5. v_StateNames,   
6. v_Update_ComplianceStatusAll   
7. Inner Join v_R_System On (v_R_System.ResourceID = v_Update_ComplianceStatusAll.ResourceID)   
8. Inner Join v_UpdateInfo On (v_UpdateInfo.CI_ID = v_Update_ComplianceStatusAll.CI_ID)   
9. where   
10. v_StateNames.TopicType = 500 and   
11. v_StateNames.StateID = v_Update_ComplianceStatusAll.Status and   
12. Statename = 'Update is required'   
13. Group By v_UpdateInfo.Title   
14. Order By 'Count of Computers' DESC   

select 
v_UpdateInfo.Title as 'Updatename', 
Count(v_R_System.name0) as 'Count of Computers' 
from 
v_StateNames, 
v_Update_ComplianceStatusAll 
Inner Join v_R_System On (v_R_System.ResourceID = v_Update_ComplianceStatusAll.ResourceID) 
Inner Join v_UpdateInfo On (v_UpdateInfo.CI_ID = v_Update_ComplianceStatusAll.CI_ID) 
where 
v_StateNames.TopicType = 500 and 
v_StateNames.StateID = v_Update_ComplianceStatusAll.Status and 
Statename = 'Update is required' 
Group By v_UpdateInfo.Title 
Order By 'Count of Computers' DESC 

Assinged number of Updates for each Computer

view plainprint?

1. select   
2. v_R_System.Name0 as Computername,   
3. Count(v_UpdateInfo.Title) as Updates  
4. from v_UpdateState_Combined  
5. inner join v_R_System on (v_R_System.ResourceID = v_UpdateState_Combined.ResourceID)   
6. inner join v_CIAssignmentToCI on (v_CIAssignmentToCI.CI_ID = v_UpdateState_Combined.CI_ID)   
7. inner join v_CIAssignment on (v_CIAssignment.AssignmentID = v_CIAssignmentToCI.AssignmentID)   
8. inner join v_UpdateInfo on (v_UpdateInfo.CI_ID = v_UpdateState_Combined.CI_ID)   
9. inner join v_StateNames on (v_StateNames.TopicType = v_UpdateState_Combined.StateType)   
10. where  
11. v_StateNames.StateID = v_UpdateState_Combined.StateID and  
12. v_StateNames.StateName not like 'Update is not required'  
13. Group by v_R_System.Name0  

select 
v_R_System.Name0 as Computername, 
Count(v_UpdateInfo.Title) as Updates
from v_UpdateState_Combined
inner join v_R_System on (v_R_System.ResourceID = v_UpdateState_Combined.ResourceID) 
inner join v_CIAssignmentToCI on (v_CIAssignmentToCI.CI_ID = v_UpdateState_Combined.CI_ID) 
inner join v_CIAssignment on (v_CIAssignment.AssignmentID = v_CIAssignmentToCI.AssignmentID) 
inner join v_UpdateInfo on (v_UpdateInfo.CI_ID = v_UpdateState_Combined.CI_ID) 
inner join v_StateNames on (v_StateNames.TopicType = v_UpdateState_Combined.StateType) 
where
v_StateNames.StateID = v_UpdateState_Combined.StateID and
v_StateNames.StateName not like 'Update is not required'
Group by v_R_System.Name0

Assinged Updates and current State for a specific Computer

view plainprint?

1. select   
2. v_R_System.Name0 as 'Computername',   
3. v_UpdateInfo.Title as 'UpdateDescription',   
4. v_StateNames.StateName as 'State'  
5. from v_UpdateState_Combined, v_CIAssignmentToCI,v_CIAssignment,  v_UpdateInfo,v_StateNames,  v_R_System  
6. where v_R_System.Name0 =  @MACHINENAME and  
7. v_UpdateState_Combined.ResourceID = v_R_System.ResourceID and  
8. v_UpdateState_Combined.CI_ID = v_CIAssignmentToCI.CI_ID and  
9. v_CIAssignmentToCI.AssignmentID = v_CIAssignment.AssignmentID and  
10. v_UpdateState_Combined.CI_ID = v_UpdateInfo.CI_ID and  
11. v_UpdateState_Combined.StateType = v_StateNames.TopicType and   
12. v_UpdateState_Combined.StateID = v_StateNames.StateID and  
13. v_StateNames.StateName not like 'Update is not required'  
14. Order By UpdateDescription  

select 
v_R_System.Name0 as 'Computername', 
v_UpdateInfo.Title as 'UpdateDescription', 
v_StateNames.StateName as 'State'
from v_UpdateState_Combined, v_CIAssignmentToCI,v_CIAssignment,  v_UpdateInfo,v_StateNames,  v_R_System
where v_R_System.Name0 =  @MACHINENAME and
v_UpdateState_Combined.ResourceID = v_R_System.ResourceID and
v_UpdateState_Combined.CI_ID = v_CIAssignmentToCI.CI_ID and
v_CIAssignmentToCI.AssignmentID = v_CIAssignment.AssignmentID and
v_UpdateState_Combined.CI_ID = v_UpdateInfo.CI_ID and
v_UpdateState_Combined.StateType = v_StateNames.TopicType and 
v_UpdateState_Combined.StateID = v_StateNames.StateID and
v_StateNames.StateName not like 'Update is not required'
Order By UpdateDescription

Assinged Updates in an UpdateList and current State for a specific Computer

view plainprint?

1. select distinct v_R_System.Name0,v_StateNames.StateName, v_UpdateInfo.Title  
2. from v_AuthListInfo,v_CIRelation,v_UpdateState_Combined,v_StateNames,v_UpdateInfo,v_R_System  
3. where v_AuthListInfo.CI_ID = v_CIRelation.FromCIID and  
4. v_CIRelation.ToCIID = v_UpdateInfo.CI_ID and  
5. v_UpdateState_Combined.CI_ID = v_CIRelation.ToCIID and  
6. v_StateNames.TopicType = v_UpdateState_Combined.StateType and  
7. v_StateNames.StateID = v_UpdateState_Combined.StateID and  
8. v_R_System.ResourceID = v_UpdateState_Combined.ResourceID and  
9. v_R_System.Name0 = @COMPUTERNAME and  
10. v_AuthListInfo.Title = @UPDATELIST and  
11. v_StateNames.StateName not like 'Update is not required'   

select distinct v_R_System.Name0,v_StateNames.StateName, v_UpdateInfo.Title
from v_AuthListInfo,v_CIRelation,v_UpdateState_Combined,v_StateNames,v_UpdateInfo,v_R_System
where v_AuthListInfo.CI_ID = v_CIRelation.FromCIID and
v_CIRelation.ToCIID = v_UpdateInfo.CI_ID and
v_UpdateState_Combined.CI_ID = v_CIRelation.ToCIID and
v_StateNames.TopicType = v_UpdateState_Combined.StateType and
v_StateNames.StateID = v_UpdateState_Combined.StateID and
v_R_System.ResourceID = v_UpdateState_Combined.ResourceID and
v_R_System.Name0 = @COMPUTERNAME and
v_AuthListInfo.Title = @UPDATELIST and
v_StateNames.StateName not like 'Update is not required' 

Number of assinged Updates in an UpdateList and current State for all Computers

view plainprint?

1. select  v_R_System.Name0 as Computername , v_StateNames.StateName ,count(v_UpdateInfo.Title) as UpdateCount, v_AuthListInfo.Title as Updatelist  
2. from v_AuthListInfo  
3. inner join v_CIRelation On (v_CIRelation.FromCIID = v_AuthListInfo.CI_ID)  
4. inner join v_UpdateInfo On (v_UpdateInfo.CI_ID = v_CIRelation.ToCIID)  
5. inner join v_CIAssignmentToCI on (v_CIAssignmentToCI.CI_ID = v_CIRelation.ToCIID)  
6. inner join v_CIAssignmentTargetedMachines on (v_CIAssignmentTargetedMachines.AssignmentID = v_CIAssignmentToCI.AssignmentID)  
7. inner join v_R_System on (v_R_System.ResourceID = v_CIAssignmentTargetedMachines.ResourceID)  
8. inner join v_UpdateState_Combined on (v_UpdateState_Combined.CI_ID = v_CIRelation.ToCIID)  
9. inner join v_StateNames on (v_StateNames.StateID = v_UpdateState_Combined.StateID)  
10. where v_AuthListInfo.Title = @UPDATELIST and  
11. v_UpdateState_Combined.ResourceID = v_R_System.ResourceID and  
12. v_StateNames.TopicType = v_UpdateState_Combined.StateType  
13. and v_StateNames.StateName not like 'Update is not required'  
14. group by v_R_System.Name0, v_StateNames.StateName, v_AuthListInfo.Title  
15. order by v_R_System.Name0  

select  v_R_System.Name0 as Computername , v_StateNames.StateName ,count(v_UpdateInfo.Title) as UpdateCount, v_AuthListInfo.Title as Updatelist
from v_AuthListInfo
inner join v_CIRelation On (v_CIRelation.FromCIID = v_AuthListInfo.CI_ID)
inner join v_UpdateInfo On (v_UpdateInfo.CI_ID = v_CIRelation.ToCIID)
inner join v_CIAssignmentToCI on (v_CIAssignmentToCI.CI_ID = v_CIRelation.ToCIID)
inner join v_CIAssignmentTargetedMachines on (v_CIAssignmentTargetedMachines.AssignmentID = v_CIAssignmentToCI.AssignmentID)
inner join v_R_System on (v_R_System.ResourceID = v_CIAssignmentTargetedMachines.ResourceID)
inner join v_UpdateState_Combined on (v_UpdateState_Combined.CI_ID = v_CIRelation.ToCIID)
inner join v_StateNames on (v_StateNames.StateID = v_UpdateState_Combined.StateID)
where v_AuthListInfo.Title = @UPDATELIST and
v_UpdateState_Combined.ResourceID = v_R_System.ResourceID and
v_StateNames.TopicType = v_UpdateState_Combined.StateType
and v_StateNames.StateName not like 'Update is not required'
group by v_R_System.Name0, v_StateNames.StateName, v_AuthListInfo.Title
order by v_R_System.Name0

Number of Updates in an Update Deployment

view plainprint?

1. select AssignmentName as Deployment,Count(CI_ID) as UpdateCount  
2. from v_CIAssignment   
3. Inner Join v_CIAssignmentToCI On (v_CIAssignmentToCI.AssignmentID = v_CIAssignment.AssignmentID)  
4. where AssignmentName Like 'Update%'   
5. Group By AssignmentName  
6. order By AssignmentName  

select AssignmentName as Deployment,Count(CI_ID) as UpdateCount
from v_CIAssignment 
Inner Join v_CIAssignmentToCI On (v_CIAssignmentToCI.AssignmentID = v_CIAssignment.AssignmentID)
where AssignmentName Like 'Update%' 
Group By AssignmentName
order By AssignmentName

All Updates in an Updatelist

view plainprint?

1. select distinct v_UpdateInfo.Title  
2. from v_AuthListInfo,v_CIRelation,v_UpdateInfo  
3. where v_AuthListInfo.CI_ID = v_CIRelation.FromCIID and  
4. v_CIRelation.ToCIID = v_UpdateInfo.CI_ID and  
5. v_AuthListInfo.Title = @UPDATELIST  
6. order by v_UpdateInfo.Title  

select distinct v_UpdateInfo.Title
from v_AuthListInfo,v_CIRelation,v_UpdateInfo
where v_AuthListInfo.CI_ID = v_CIRelation.FromCIID and
v_CIRelation.ToCIID = v_UpdateInfo.CI_ID and
v_AuthListInfo.Title = @UPDATELIST
order by v_UpdateInfo.Title

ConfigMgr Patch Installer

The ConfigMgr Patch Installer is a tool (vbscript) which can be used for installing updates which are advertised from SCCM. If the updates are advertised without a deadline. Especially it is useful for Windows Server 2008 Core edition as they do not have a GUI and so the updates can't be installed. My tool will query SCCM for each Update which is advertised to the System and will install it. The result can be displayed in a output logfile.

The Tool can be downloaded at systemcentercentral.com and winoneclick.com

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

vbScript - Migrate Advertisements from SMS to SCCM

This script will migrate all advertisements from on site sms/sccm to another one
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 AllPackages, objPackage  
10. Dim strProgramName, strCurPackageVersion, strCurpackageLanguage, strCurPackageManufacturer  
11. Dim Token, schedule, schedtype  
12. Dim Path  
13. Dim strNewCollectionsID, strNewPackageID, strNewContainerID  
14. Dim AllAdvertisements, objAdvertisement, newAdvertisement, Advertisement  
15. Dim objFSO : Set objFSO = CreateObject("Scripting.FileSystemObject")  
16. Dim objLogFile : Set objLogFile = objFSO.CreateTextFile("MigrateAdvertisements.log")  
17.   
18. Set objSourceWMIService = GetObject("winmgmts://" & strSourceServer & "\root\sms\site_" & strSourceSiteCode)  
19. Set objTargetWMIService = GetObject("winmgmts://" & strTargetServer & "\root\sms\site_" & strTargetSiteCode)  
20.   
21. 'Creating Container  
22. Call CreateContainer  
23.   
24. Set AllAdvertisements = objSourceWMIService.ExecQuery("Select * From SMS_Advertisement")   
25. For Each objAdvertisement In AllAdvertisements  
26.    
27.  strProgramName = objAdvertisement.ProgramName  
28.  objLogFile.WriteLine Now &" - The program " &strProgramName  &" is assigned to the Advertisement " &objAdvertisement.AdvertisementName  
29.      
30.  'Get Container ID of the Advertisement  
31.  strNewContainerID = GetNewContainerID(ContainerIDToName(GetContainerID(objAdvertisement.AdvertisementID)))  
32.  objLogFile.WriteLine Now &" - New ContainerID for Advertisement " &objAdvertisement.AdvertisementName &" is " &strNewContainerID  
33.    
34.  'Get New Collections ID  
35.  strNewCollectionsID = GetNewCollectionsID(CollectionIDToName(objAdvertisement.CollectionID))  
36.  objLogFile.WriteLine Now &" - The CollectionID " &strNewCollectionsID &" will be assigned to " &objAdvertisement.AdvertisementName  
37.    
38.  'Get New Package ID  
39.  strNewPackageID = GetNewPackageID(PackageIDToName(objAdvertisement.PackageID))  
40.  objLogFile.WriteLine Now &" - The PackageID  " &strNewPackageID &" will be assigned to  " &objAdvertisement.AdvertisementName  
41.    
42.  'Create Advertisement  
43.  If  strNewCollectionsID <> "" And  strProgramName <> "" And  strNewPackageID <> "" Then  
44.   Set newAdvertisement = objTargetWMIService.Get("SMS_Advertisement").SpawnInstance_()  
45.   Set lazyproperties = objSourceWMIService.get("sms_advertisement.advertisementid='" & objAdvertisement.advertisementid & "'")  
46.   
47.   newAdvertisement.assignedscheduleenabled = lazyproperties.assignedscheduleenabled  
48.   newAdvertisement.assignedscheduleisgmt = lazyproperties.assignedscheduleisgmt  
49.       newAdvertisement.expirationtimeenabled = lazyproperties.expirationtimeenabled  
50.       newAdvertisement.expirationtimeisgmt = lazyproperties.expirationtimeisgmt  
51.       newAdvertisement.assignedschedule = lazyproperties.assignedschedule  
52.       newAdvertisement.presenttimeisgmt=lazyproperties.presenttimeisgmt  
53.   
54.   newAdvertisement.AdvertisementName = objAdvertisement.AdvertisementName  
55.   newAdvertisement.AdvertFlags= objAdvertisement.AdvertFlags  
56.   newAdvertisement.CollectionID = strNewCollectionsID  
57.   newAdvertisement.DeviceFlags = DeviceFlags  
58.   newAdvertisement.comment = objAdvertisement.Comment   
59.   newAdvertisement.ExpirationTime= objAdvertisement.ExpirationTime  
60.   newAdvertisement.HierarchyPath = objAdvertisement.HierarchyPath  
61.   newAdvertisement.IncludeSubCollection = objAdvertisement.IncludeSubCollection  
62.   newAdvertisement.PackageID = strNewPackageID  
63.   newAdvertisement.PresentTime=objAdvertisement.PresentTime  
64.   newAdvertisement.PresentTimeEnabled=objAdvertisement.PresentTimeEnabled  
65.   newAdvertisement.ProgramName = strProgramName  
66.   newAdvertisement.RemoteClientFlags = objAdvertisement.RemoteClientFlags  
67.   newAdvertisement.Priority = objAdvertisement.Priority  
68.   newAdvertisement.TimeFlags = objAdvertisement.TimeFlags  
69.   Path = newAdvertisement.Put_  
70.     
71.   'Get automatically assigned Advertisement ID  
72.   Set Advertisement=objTargetWMIService.Get(Path)  
73.   'Moving the Advertisements into Container  
74.   Call MoveAdvertisementInToContainer(strNewContainerID, Advertisement.AdvertisementID)  
75.     
76.  Else  
77.   objLogFile.WriteLine Now &" - ERROR: Advertisement " &objAdvertisement.AdvertisementName  &" could not be migrated. Maybe a Collection, Package or Program does not exist"  
78.  End If  
79.    
80.  strProgramName = ""  
81.  strNewPackageID = ""  
82.  strNewCollectionsID = ""  
83.    
84. Next  
85. WScript.Echo "Done"  
86.    
87.    
88. Function CollectionIDToName(CollectionID)  
89. Dim AllCollections, objCollection  
90.   
91. Set AllCollections = objSourceWMIService.ExecQuery("SELECT * FROM SMS_Collection WHERE CollectionID='" &CollectionID &"'")   
92. For Each objCollection In AllCollections  
93.  CollectionIDToName = objCollection.Name  
94. Next  
95. End Function  
96.   
97. Function GetNewCollectionsID(CollectionName)  
98. Dim AllCollections, objCollection  
99.   
100. Set AllCollections = objTargetWMIService.ExecQuery("SELECT * FROM SMS_Collection WHERE Name='" &CollectionName &"'")   
101. For Each objCollection In AllCollections  
102.  GetNewCollectionsID = objCollection.CollectionID  
103. Next  
104. End Function  
105.    
106. Function PackageIDToName(PackageID)  
107. Dim AllPackages, objPackage  
108.   
109. Set AllPackages = objSourceWMIService.ExecQuery("SELECT * FROM SMS_Package WHERE PackageID='" &PackageID &"'")   
110. For Each objPackage In AllPackages  
111.  PackageIDToName = objPackage.Name  
112.  strCurPackageVersion = objPackage.Version  
113.  strCurPackageManufacturer = objPackage.Manufacturer  
114.  strCurpackageLanguage = objPackage.Language  
115. Next  
116. End Function  
117.   
118. Function GetNewPackageID(PackageName)  
119. Dim AllPackages, objPackage  
120.   
121. 'Make sure the Correct Package is choosen  
122. Set AllPackages = objTargetWMIService.ExecQuery("SELECT * FROM SMS_Package WHERE Name='" &PackageName _  
123.                    &"' AND Version='" &strCurPackageVersion _   
124.                    &"' AND Language='" &strCurpackageLanguage _   
125.                    &"' AND Manufacturer='" &strCurPackageManufacturer &"'")  
126.   
127. For Each objPackage In AllPackages  
128.  GetNewPackageID = objPackage.PackageID  
129. Next  
130. strCurPackageVersion = ""  
131. strCurpackageLanguage = ""  
132. strCurPackageManufacturer = ""  
133. End Function  
134.   
135. Sub CreateContainer  
136.   
137. Dim AllSourceContainer, objSourceContainer  
138. Dim objTargetContainer  
139.   
140.     Set AllSourceContainer = objSourceWMIService.ExecQuery("SELECT * FROM SMS_ObjectContainerNode WHERE ObjectType='3'")  
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 MoveAdvertisementInToContainer(ContainerID, AdvertisementID)  
157. Dim folderItem  
158.   
159. If ContainerID <> "" Then  
160.   
161.  objLogFile.WriteLine Now &" - Moving Advertisement " &AdvertisementID &" into Container " &ContainerID  
162.    
163.  Set folderItem = objTargetWMIService.Get("SMS_ObjectContainerItem").SpawnInstance_()  
164.  folderItem.InstanceKey = AdvertisementID  
165.  folderItem.ObjectType = "3"  
166.  folderItem.ContainerNodeID = ContainerID  
167.  folderItem.Put_  
168. Else  
169.  objLogFile.WriteLine Now &" - No need to move the Advertisement " &AdvertisementID &", because it's listed in the root"  
170. End If  
171.       
172. End Function  
173.   
174. Function GetContainerID(AdvertisementID)  
175. Dim AllContainers, objContainer  
176.   
177. Set AllContainers = objSourceWMIService.ExecQuery("SELECT * FROM SMS_ObjectContainerItem WHERE ObjectType='3' AND InstanceKey='" &AdvertisementID &"'")   
178. For Each objContainer In AllContainers  
179.  GetContainerID = objContainer.ContainerNodeID  
180. Next  
181.   
182. End Function  
183.   
184. Function ContainerIDToName(ContainerID)  
185. Dim AllContainers, objContainer  
186.   
187. Set AllContainers = objSourceWMIService.ExecQuery("SELECT * FROM SMS_ObjectContainerNode WHERE ObjectType='3' AND ContainerNodeID='" &ContainerID &"'")   
188. For Each objContainer In AllContainers  
189.  ContainerIDToName = objContainer.Name  
190. Next  
191.   
192. End Function  
193.   
194. Function GetNewContainerID(ContainerName)  
195. Dim AllSourceContainer, objSourceContainer  
196.    
197. Set AllSourceContainer = objTargetWMIService.ExecQuery("SELECT * FROM SMS_ObjectContainerNode WHERE ObjectType='3' and Name = '" &ContainerName &"'")  
198.   
199. For Each objSourceContainer In AllSourceContainer  
200.  GetNewContainerID = objSourceContainer.ContainerNodeID  
201. Next  
202. 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 AllPackages, objPackage
Dim strProgramName, strCurPackageVersion, strCurpackageLanguage, strCurPackageManufacturer
Dim Token, schedule, schedtype
Dim Path
Dim strNewCollectionsID, strNewPackageID, strNewContainerID
Dim AllAdvertisements, objAdvertisement, newAdvertisement, Advertisement
Dim objFSO : Set objFSO = CreateObject("Scripting.FileSystemObject")
Dim objLogFile : Set objLogFile = objFSO.CreateTextFile("MigrateAdvertisements.log")

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

'Creating Container
Call CreateContainer

Set AllAdvertisements = objSourceWMIService.ExecQuery("Select * From SMS_Advertisement") 
For Each objAdvertisement In AllAdvertisements
 
 strProgramName = objAdvertisement.ProgramName
 objLogFile.WriteLine Now &" - The program " &strProgramName  &" is assigned to the Advertisement " &objAdvertisement.AdvertisementName
   
 'Get Container ID of the Advertisement
 strNewContainerID = GetNewContainerID(ContainerIDToName(GetContainerID(objAdvertisement.AdvertisementID)))
 objLogFile.WriteLine Now &" - New ContainerID for Advertisement " &objAdvertisement.AdvertisementName &" is " &strNewContainerID
 
 'Get New Collections ID
 strNewCollectionsID = GetNewCollectionsID(CollectionIDToName(objAdvertisement.CollectionID))
 objLogFile.WriteLine Now &" - The CollectionID " &strNewCollectionsID &" will be assigned to " &objAdvertisement.AdvertisementName
 
 'Get New Package ID
 strNewPackageID = GetNewPackageID(PackageIDToName(objAdvertisement.PackageID))
 objLogFile.WriteLine Now &" - The PackageID  " &strNewPackageID &" will be assigned to  " &objAdvertisement.AdvertisementName
 
 'Create Advertisement
 If  strNewCollectionsID <> "" And  strProgramName <> "" And  strNewPackageID <> "" Then
  Set newAdvertisement = objTargetWMIService.Get("SMS_Advertisement").SpawnInstance_()
  Set lazyproperties = objSourceWMIService.get("sms_advertisement.advertisementid='" & objAdvertisement.advertisementid & "'")

  newAdvertisement.assignedscheduleenabled = lazyproperties.assignedscheduleenabled
  newAdvertisement.assignedscheduleisgmt = lazyproperties.assignedscheduleisgmt
      newAdvertisement.expirationtimeenabled = lazyproperties.expirationtimeenabled
      newAdvertisement.expirationtimeisgmt = lazyproperties.expirationtimeisgmt
      newAdvertisement.assignedschedule = lazyproperties.assignedschedule
      newAdvertisement.presenttimeisgmt=lazyproperties.presenttimeisgmt

  newAdvertisement.AdvertisementName = objAdvertisement.AdvertisementName
  newAdvertisement.AdvertFlags= objAdvertisement.AdvertFlags
  newAdvertisement.CollectionID = strNewCollectionsID
  newAdvertisement.DeviceFlags = DeviceFlags
  newAdvertisement.comment = objAdvertisement.Comment 
  newAdvertisement.ExpirationTime= objAdvertisement.ExpirationTime
  newAdvertisement.HierarchyPath = objAdvertisement.HierarchyPath
  newAdvertisement.IncludeSubCollection = objAdvertisement.IncludeSubCollection
  newAdvertisement.PackageID = strNewPackageID
  newAdvertisement.PresentTime=objAdvertisement.PresentTime
  newAdvertisement.PresentTimeEnabled=objAdvertisement.PresentTimeEnabled
  newAdvertisement.ProgramName = strProgramName
  newAdvertisement.RemoteClientFlags = objAdvertisement.RemoteClientFlags
  newAdvertisement.Priority = objAdvertisement.Priority
  newAdvertisement.TimeFlags = objAdvertisement.TimeFlags
  Path = newAdvertisement.Put_
  
  'Get automatically assigned Advertisement ID
  Set Advertisement=objTargetWMIService.Get(Path)
  'Moving the Advertisements into Container
  Call MoveAdvertisementInToContainer(strNewContainerID, Advertisement.AdvertisementID)
  
 Else
  objLogFile.WriteLine Now &" - ERROR: Advertisement " &objAdvertisement.AdvertisementName  &" could not be migrated. Maybe a Collection, Package or Program does not exist"
 End If
 
 strProgramName = ""
 strNewPackageID = ""
 strNewCollectionsID = ""
 
Next
WScript.Echo "Done"
 
 
Function CollectionIDToName(CollectionID)
Dim AllCollections, objCollection

Set AllCollections = objSourceWMIService.ExecQuery("SELECT * FROM SMS_Collection WHERE CollectionID='" &CollectionID &"'") 
For Each objCollection In AllCollections
 CollectionIDToName = objCollection.Name
Next
End Function

Function GetNewCollectionsID(CollectionName)
Dim AllCollections, objCollection

Set AllCollections = objTargetWMIService.ExecQuery("SELECT * FROM SMS_Collection WHERE Name='" &CollectionName &"'") 
For Each objCollection In AllCollections
 GetNewCollectionsID = objCollection.CollectionID
Next
End Function
 
Function PackageIDToName(PackageID)
Dim AllPackages, objPackage

Set AllPackages = objSourceWMIService.ExecQuery("SELECT * FROM SMS_Package WHERE PackageID='" &PackageID &"'") 
For Each objPackage In AllPackages
 PackageIDToName = objPackage.Name
 strCurPackageVersion = objPackage.Version
 strCurPackageManufacturer = objPackage.Manufacturer
 strCurpackageLanguage = objPackage.Language
Next
End Function

Function GetNewPackageID(PackageName)
Dim AllPackages, objPackage

'Make sure the Correct Package is choosen
Set AllPackages = objTargetWMIService.ExecQuery("SELECT * FROM SMS_Package WHERE Name='" &PackageName _
                   &"' AND Version='" &strCurPackageVersion _ 
                   &"' AND Language='" &strCurpackageLanguage _ 
                   &"' AND Manufacturer='" &strCurPackageManufacturer &"'")

For Each objPackage In AllPackages
 GetNewPackageID = objPackage.PackageID
Next
strCurPackageVersion = ""
strCurpackageLanguage = ""
strCurPackageManufacturer = ""
End Function

Sub CreateContainer

Dim AllSourceContainer, objSourceContainer
Dim objTargetContainer

    Set AllSourceContainer = objSourceWMIService.ExecQuery("SELECT * FROM SMS_ObjectContainerNode WHERE ObjectType='3'")
    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 MoveAdvertisementInToContainer(ContainerID, AdvertisementID)
Dim folderItem

If ContainerID <> "" Then

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

Function GetContainerID(AdvertisementID)
Dim AllContainers, objContainer

Set AllContainers = objSourceWMIService.ExecQuery("SELECT * FROM SMS_ObjectContainerItem WHERE ObjectType='3' AND InstanceKey='" &AdvertisementID &"'") 
For Each objContainer In AllContainers
 GetContainerID = objContainer.ContainerNodeID
Next

End Function

Function ContainerIDToName(ContainerID)
Dim AllContainers, objContainer

Set AllContainers = objSourceWMIService.ExecQuery("SELECT * FROM SMS_ObjectContainerNode WHERE ObjectType='3' 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='3' and Name = '" &ContainerName &"'")

For Each objSourceContainer In AllSourceContainer
 GetNewContainerID = objSourceContainer.ContainerNodeID
Next
End Function

vbScript - Migrate Packages from SMS to SCCM

This script will migrate all packages from on site sms/sccm to another one
Be careful: Never run the script in your production until you've tested it!!!

view plainprint?

1. 'Option Explicit  
2. On Error Resume Next  
3. Dim strSourceServer : strSourceServer = "SMSServer"  
4. Dim strTargetServer : strTargetServer = "SCCMServer"  
5. Dim strSourceSiteCode : strSourceSiteCode = "000"  
6. Dim strTargetSiteCode : strTargetSiteCode = "000"  
7. Dim strNewSourcePath : strNewSourcePath = "SCCMServer"  
8. Dim strOldSourcePath : strOldSourcePath = "SMSServer"  
9. Dim objSourceWMIService, objTargetWMIService  
10. Dim objPackage, AllPackages  
11. Dim strContainerID, strContainerName  
12. Dim bolScheduleAvailable : bolScheduleAvailable = False  
13. Dim Token, schedule, schedtype  
14. Dim strPkgManufacturer, strPkgName, strPkgVersion, strPkgLanguage, strPkgDescription, strPkgSource, strPkgSourceFlag  
15. Dim strPkgExtendedData, strPkgExtendedDataSize, strPkgForcedDisconnectDelay, strPkgForcedDisconnectEnabled  
16. Dim strPkgForcedDisconnectRetries, strPkgIcon, strPkgiconSize,strPkgIgnoreAddressSchedule,strPkgFlags, strPkgPriority  
17. Dim strPkgMIFName, strPkgMIFPublisher, srtPktMIFVersion, strPkgMIFFilename, strPkgPreferedAddressType  
18. Dim strPkgRefreshSourceFlag, strPkgShareName, strPkgShareType, strPkgSourceData, strPkgSourceVersion  
19. Dim strNewPackageID  
20. Dim objFSO : Set objFSO = CreateObject("Scripting.FileSystemObject")  
21. Dim objLogFile : Set objLogFile = objFSO.CreateTextFile("MigratePackages.log")  
22.   
23. Set objSourceWMIService = GetObject("winmgmts://" & strSourceServer & "\root\sms\site_" & strSourceSiteCode)  
24. Set objTargetWMIService = GetObject("winmgmts://" & strTargetServer & "\root\sms\site_" & strTargetSiteCode)  
25.   
26. objLogFile.WriteLine Now &" - Migrating Packages from " &strSourceServer &" to " &strTargetServer  
27.   
28. 'First create all Containers  
29. Call CreateContainer  
30.   
31. 'Loop trought all packages exsisting on the source server  
32. Set AllPackages = objSourceWMIService.ExecQuery("SELECT * FROM SMS_Package")   
33. For Each objPackage In AllPackages  
34.    
35.  objLogFile.WriteLine Now &" - Migrating Package " &objPackage.PackageID  
36.    
37.  'Get Package settings  
38.  strPkgName = objPackage.Name  
39.  strPkgManufacturer = objPackage.Manufacturer  
40.  strPkgVersion = objPackage.Version  
41.  strPkgLanguage = objPackage.Language  
42.  strPkgDescription = objPackage.Description  
43.  strPkgSource = objPackage.PkgSourcePath  
44.  strPkgSourceFlag = objPackage.PkgSourceFlag  
45.  strPkgExtendedData = objPackage.extendedData  
46.  strPkgExtendedDataSize = objPackage.extendedDataSize  
47.  strPkgForcedDisconnectDelay = objPackage.forcedDisconnectDelay  
48.  strPkgForcedDisconnectEnabled = objPackage.forcedDisconnectEnabled  
49.  strPkgForcedDisconnectRetries = objPackage.forcedDisconnectNumretries  
50.  strPkgIcon = objPackage.Icon  
51.  strPkgiconSize = objPackage.IconSize  
52.  strPkgIgnoreAddressSchedule = objPackage.IgnoreAddressSchedule  
53.  strPkgMIFFilename = objPackage.MifFileName  
54.  strPkgMIFName = objPackage.MifName  
55.  strPkgMIFPublisher = objPackage.MifPublisher  
56.  srtPktMIFVersion = objPackage.MifVersion  
57.  strPkgFlags = objPackage.PKGFlags  
58.  strPkgPreferedAddressType = objPackage.PreferredAddressType  
59.  strPkgPriority = objPackage.Priority  
60.  strPkgRefreshSourceFlag = objPackage.RefreshPkgSourceFlag  
61.  strPkgShareName = objPackage.ShareName  
62.  strPkgShareType = objPackage.ShareType  
63.  strPkgSourceData = objPackage.SourceDate  
64.  strPkgSourceVersion = objPackage.SourceVersion   
65.    
66.  'Get Package Refresh Schedule  
67.  Set objPackage = objSourceWMIService.Get("SMS_Package='" & objPackage.PackageID & "'" )   
68.  On Error Resume Next  
69.  Set schedule = objPackage.RefreshSchedule(0)  
70.  On Error GoTo 0  
71.    
72.    
73.  If err.number = 424 Then  
74.      objLogFile.WriteLine Now &" - Package has no Schedule  " &objPackage.PackageID  
75.  ElseIf err.number <> 0 Then  
76.   objLogFile.WriteLine Now &" - An Error (" &err.number  &") occured while getting the Refresh Schedule from " &objPackage.PackageID  
77.  Else  
78.   On Error Resume Next  
79.   schedtype=Schedule.Path_.Class  
80.     
81.   Select Case (schedtype)  
82.    Case "SMS_ST_RecurInterval"  
83.      Set Token = objTargetWMIService.Get("SMS_ST_RecurInterval").SpawnInstance_()  
84.      If schedule.MinuteSpan <> 0 Then Token.MinuteSpan = schedule.MinuteSpan  
85.      If schedule.HourSpan <> 0 Then Token.HourSpan = schedule.HourSpan  
86.      If schedule.DaySpan <> 0 Then Token.DaySpan = schedule.DaySpan  
87.      If schedule.MinuteDuration <> 0 Then Token.MinuteDuration = schedule.MinuteDuration  
88.      If schedule.HourDuration <> 0 Then Token.HourDuration = schedule.HourDuration  
89.      If schedule.DayDuration <> 0 Then Token.DayDuration = schedule.DayDuration  
90.      Token.StartTime = schedule.StartTime  
91.        
92.      bolScheduleAvailable = True  
93.      objLogFile.WriteLine Now &" - SMS_ST_RecurInterval Schedule available " &objPackage.PackageID  
94.        
95.    Case "SMS_ST_RecurWeekly"  
96.      Set Token = objTargetWMIService.Get("SMS_ST_RecurWeekly").SpawnInstance_()  
97.      If schedule.MinuteDuration <> 0 Then Token.MinuteDuration = schedule.MinuteDuration  
98.      If schedule.HourDuration <> 0 Then Token.HourDuration = schedule.HourDuration  
99.      If schedule.DayDuration <> 0 Then Token.DayDuration = schedule.DayDuration  
100.      If schedule.ForNumberOfWeeks <> 0 Then Token.ForNumberOfWeeks = schedule.ForNumberOfWeeks  
101.      If schedule.Day <> 0 Then Token.Day = schedule.Day  
102.      Token.StartTime = schedule.StartTime  
103.        
104.      bolScheduleAvailable = True  
105.      objLogFile.WriteLine Now &" - SMS_ST_RecurWeekly Schedule available " &objPackage.PackageID  
106.        
107.    Case "SMS_ST_RecurMonthlyByDate"  
108.      Set Token = objTargetWMIService.Get("SMS_ST_RecurMonthlyByDate").SpawnInstance_()  
109.      If schedule.MinuteDuration <> 0 Then Token.MinuteDuration = schedule.MinuteDuration  
110.      If schedule.HourDuration <> 0 Then Token.HourDuration = schedule.HourDuration  
111.      If schedule.DayDuration <> 0 Then Token.DayDuration = schedule.DayDuration  
112.      If schedule.ForNumberOfMonths <> 0 Then Token.ForNumberOfMonths = schedule.ForNumberOfMonths  
113.      If schedule.MonthDay <> 0 Then Token.MonthDay = schedule.MonthDay  
114.      Token.StartTime = schedule.StartTime  
115.        
116.     bolScheduleAvailable = True  
117.     objLogFile.WriteLine Now &" - SMS_ST_RecurMonthlyByDate Schedule available " &objPackage.PackageID  
118.       
119.    Case "SMS_ST_RecurMonthlyByWeekday"  
120.      Set Token = objTargetWMIService.Get("SMS_ST_RecurMonthlyByWeekday").SpawnInstance_()  
121.      If schedule.MinuteDuration <> 0 Then Token.MinuteDuration = schedule.MinuteDuration  
122.      If schedule.HourDuration <> 0 Then Token.HourDuration = schedule.HourDuration  
123.      If schedule.DayDuration <> 0 Then Token.DayDuration = schedule.DayDuration  
124.      If schedule.ForNumberOfMonths <> 0 Then Token.ForNumberOfMonths = schedule.ForNumberOfMonths  
125.      If schedule.WeekOrder <> 0 Then Token.WeekOrder = schedule.WeekOrder  
126.   
127.      Token.StartTime = schedule.StartTime  
128.     objLogFile.WriteLine Now &" - SMS_ST_RecurMonthlyByWeekday Schedule available " &objPackage.PackageID  
129.        
130.     bolScheduleAvailable = True  
131.   
132.    Case "SMS_ST_NonRecurring"  
133.      Set Token = objTargetWMIService.Get("SMS_ST_NonRecurring").SpawnInstance_()  
134.      If schedule.MinuteDuration <> 0 Then Token.MinuteDuration = schedule.MinuteDuration  
135.      If schedule.HourDuration <> 0 Then Token.HourDuration = schedule.HourDuration  
136.      If schedule.DayDuration <> 0 Then Token.DayDuration = schedule.DayDuration  
137.      Token.StartTime = schedule.StartTime  
138.        
139.     bolScheduleAvailable = True  
140.     objLogFile.WriteLine Now &" - SMS_ST_NonRecurring Schedule available " &objPackage.PackageID  
141.        
142.     Case Else  
143.     bolScheduleAvailable = False  
144.     objLogFile.WriteLine Now &" - Unknown Schedule (skipping) " &objPackage.PackageID  
145.   End Select  
146.        
147.    End If  
148.    
149.   
150.  'Get the container id of the package  
151.  strContainerID = GetContainerID(objPackage.PackageID)  
152.    
153.  'Get the containers name of the package  
154.  strContainerName = ContainerIDToName(strContainerID)  
155.  objLogFile.WriteLine Now &" - Old Container of the Package is " &strContainerName  
156.    
157.  'Get the new containers id  
158.  strNewContainerID = GetNewContainerID(strContainerName)  
159.  objLogFile.WriteLine Now &" - New Container ID of the Package is " &strNewContainerID  
160.   
161.  'Create the Package and get the new Package ID  
162.  strNewPackageID = CreatePackage()  
163.    
164.  Call CreatePrograms(objPackage.PackageID, strNewPackageID)  
165.    
166.  'Move the Package into the Container  
167.  Call MovePackageInToContainer(strNewContainerID, strNewPackageID)  
168.    
169.   
170. Next  
171.   
172. WScript.Echo "Done"  
173. objLogFile.WriteLine Now &" - Finish"  
174.    
175.   
176. Sub CreateContainer  
177.   
178. Dim AllSourceContainer, objSourceContainer  
179. Dim objTargetContainer  
180.   
181.     Set AllSourceContainer = objSourceWMIService.ExecQuery("SELECT * FROM SMS_ObjectContainerNode WHERE ObjectType='2'")  
182.     For Each objSourceContainer In AllSourceContainer  
183.       
184.      objLogFile.WriteLine Now &" - Migrating Container " &objSourceContainer.Name  
185.         Set objTargetContainer = objTargetWMIService.Get("SMS_ObjectContainerNode").SpawnInstance_()  
186.      objTargetContainer.Name = objSourceContainer.Name  
187.      objTargetContainer.ObjectType = objSourceContainer.ObjectType  
188.      objTargetContainer.ParentContainerNodeID = objSourceContainer.ParentContainerNodeID  
189.      On Error Resume Next  
190.      objTargetContainer.Put_  
191.      On Error GoTo 0  
192.       
193.     Next  
194.       
195. End Sub  
196.   
197. Function GetContainerID(PackageID)  
198. Dim AllContainers, objContainer  
199.   
200. Set AllContainers = objSourceWMIService.ExecQuery("SELECT * FROM SMS_ObjectContainerItem WHERE ObjectType='2' AND InstanceKey='" &PackageID &"'")   
201. For Each objContainer In AllContainers  
202.  GetContainerID = objContainer.ContainerNodeID  
203. Next  
204.   
205. End Function  
206.   
207. Function GetNewContainerID(ContainerName)  
208. Dim AllSourceContainer, objSourceContainer  
209.    
210. Set AllSourceContainer = objTargetWMIService.ExecQuery("SELECT * FROM SMS_ObjectContainerNode WHERE ObjectType='2' and Name = '" &ContainerName &"'")  
211.   
212. For Each objSourceContainer In AllSourceContainer  
213.  GetNewContainerID = objSourceContainer.ContainerNodeID  
214. Next  
215.   
216. End Function  
217.   
218. Function ContainerIDToName(ContainerID)  
219. Dim AllContainers, objContainer  
220.   
221. Set AllContainers = objSourceWMIService.ExecQuery("SELECT * FROM SMS_ObjectContainerNode WHERE ObjectType='2' AND ContainerNodeID='" &ContainerID &"'")   
222. For Each objContainer In AllContainers  
223.  ContainerIDToName = objContainer.Name  
224. Next  
225.   
226. End Function  
227.   
228. Function CreatePackage()  
229. Dim newPackage, PKGSourcePath  
230. Dim Package  
231.   
232.  objLogFile.WriteLine Now &" - Creating the Package " &strPkgManufacturer &" " &strPkgName &" " &strPkgVersion &" " &strPkgLanguage  
233.   
234.     Set newPackage = objTargetWMIService.Get("SMS_Package").SpawnInstance_  
235.   
236.     newPackage.Name = strPkgName  
237.  newPackage.Description = strPkgDescription   
238.  newPackage.extendedData = strPkgExtendedData  
239.  newPackage.extendedDataSize = strPkgExtendedDataSize   
240.  newPackage.forcedDisconnectDelay = strPkgForcedDisconnectDelay  
241.  newPackage.forcedDisconnectEnabled = strPkgForcedDisconnectEnabled  
242.  newPackage.forcedDisconnectNumretries = strPkgForcedDisconnectRetries  
243.  newPackage.Icon = strPkgIcon  
244.  newPackage.IconSize =  strPkgiconSize  
245.  newPackage.IgnoreAddressSchedule = strPkgIgnoreAddressSchedule  
246.  newPackage.Language = strPkgLanguage   
247.  newPackage.Manufacturer = strPkgManufacturer  
248.  newPackage.MifFileName = strPkgMIFFilename  
249.  newPackage.MifName = strPkgMIFName  
250.  newPackage.MifPublisher = strPkgMIFPublisher  
251.  newPackage.MifVersion = srtPktMIFVersion  
252.  newPackage.PKGFlags = strPkgFlags  
253.  newPackage.PkgSourceFlag = strPkgSourceFlag  
254.  PKGSourcePath= Replace(Lcase(strPkgSource),Lcase(strOldSourcePath),Lcase(strNewSourcePath))   
255.  newPackage.PkgSourcePath = PKGSourcePath  
256.  newPackage.PreferredAddressType = strPkgPreferedAddressType  
257.  newPackage.Priority = strPkgPriority  
258.  newPackage.RefreshPkgSourceFlag =  strPkgRefreshSourceFlag  
259.  newPackage.ShareName =  strPkgShareName  
260.  newPackage.ShareType =  strPkgShareType  
261.  newPackage.SourceDate = strPkgSourceData  
262.  newPackage.SourceVersion= strPkgSourceVersion  
263.  newPackage.Version = strPkgVersion   
264.    
265.  'Create Refresh Schedule for Package  
266.  If bolScheduleAvailable = True Then  
267.   newPackage.RefreshSchedule = Array(Token)  
268.  End If  
269.   
270.     Path = newPackage.Put_  
271.       
272.  'Get automatically assigned package ID  
273.  Set Package=objTargetWMIService.Get(Path)  
274.  CreatePackage = Package.PackageID  
275.   
276.   
277. 'Clean Variables  
278. On Error Resume Next  
279. Set Token = Nothing  
280. Set schedule = Nothing  
281. Set newPackage = Nothing  
282. bolScheduleAvailable = False  
283. schedtype = ""  
284.   
285. End Function  
286.   
287. Function MovePackageInToContainer(ContainerID, PackageID)  
288. Dim folderItem  
289.   
290. If ContainerID <> "" Then  
291.   
292.  objLogFile.WriteLine Now &" - Moving Package " &PackageID &" into Container " &ContainerID  
293.    
294.  Set folderItem = objTargetWMIService.Get("SMS_ObjectContainerItem").SpawnInstance_()  
295.  folderItem.InstanceKey = PackageID  
296.  folderItem.ObjectType = "2"  
297.  folderItem.ContainerNodeID = ContainerID  
298.  folderItem.Put_  
299. Else  
300.  objLogFile.WriteLine Now &" - No need to move the Package " &PackageID &", because it's listed in the root"  
301. End If  
302.       
303. End Function  
304.   
305. Function CreatePrograms(OldPackageID, NewPackageID)  
306. Dim AllPrograms, objProgram  
307. Dim newProgram  
308.   
309. Set AllPrograms = objSourceWMIService.ExecQuery("SELECT * FROM SMS_Program WHERE PackageID='" &OldPackageID &"'")  
310. If Not AllPrograms Is Nothing Then  
311.  For Each objProgram In AllPrograms  
312.    
313.   objLogFile.WriteLine Now &" - Creating the Program " &objProgram.ProgramName &" in Package " &OldPackageID  
314.    
315.   Set newProgram = objTargetWMIService.Get("SMS_Program").SpawnInstance_()   
316.   newProgram.CommandLine = objProgram.commandline  
317.   newProgram.Comment = objProgram.comment  
318.   newProgram.Description = objProgram.description  
319.   newProgram.Deviceflags = objProgram.deviceflags  
320.   newProgram.DiskSpaceReq = objProgram.DiskSpaceReq  
321.   newProgram.DriveLetter = objProgram.DriveLetter  
322.   newProgram.Duration = objProgram.Duration  
323.   newProgram.extendedData = objProgram.extendedData  
324.   newProgram.extendedDataSize = objProgram.extendedDataSize  
325.   newProgram.icon = objProgram.icon  
326.   newProgram.iconsize = objProgram.iconSize  
327.   newProgram.MSIFilePath = objProgram.MSIFilePath  
328.   newProgram.MSIProductID = objProgram.MSIProductID  
329.   newProgram.PackageID = NewPackageID  
330.   newProgram.ProgramFlags = objProgram.ProgramFlags  
331.   newProgram.ProgramName = objProgram.ProgramName  
332.   newProgram.RemovalKey = objProgram.RemovalKey  
333.   newProgram.Requirements = objProgram.Requirements  
334.   newProgram.SupportedOperatingSystems = objProgram.SupportedOperatingSystems  
335.   newProgram.WorkingDirectory = objProgram.WorkingDirectory  
336.    On Error Resume Next  
337.    newProgram.Put_  
338.  Next  
339. End If  
340.   
341. End Function  

'Option Explicit
On Error Resume Next
Dim strSourceServer : strSourceServer = "SMSServer"
Dim strTargetServer : strTargetServer = "SCCMServer"
Dim strSourceSiteCode : strSourceSiteCode = "000"
Dim strTargetSiteCode : strTargetSiteCode = "000"
Dim strNewSourcePath : strNewSourcePath = "SCCMServer"
Dim strOldSourcePath : strOldSourcePath = "SMSServer"
Dim objSourceWMIService, objTargetWMIService
Dim objPackage, AllPackages
Dim strContainerID, strContainerName
Dim bolScheduleAvailable : bolScheduleAvailable = False
Dim Token, schedule, schedtype
Dim strPkgManufacturer, strPkgName, strPkgVersion, strPkgLanguage, strPkgDescription, strPkgSource, strPkgSourceFlag
Dim strPkgExtendedData, strPkgExtendedDataSize, strPkgForcedDisconnectDelay, strPkgForcedDisconnectEnabled
Dim strPkgForcedDisconnectRetries, strPkgIcon, strPkgiconSize,strPkgIgnoreAddressSchedule,strPkgFlags, strPkgPriority
Dim strPkgMIFName, strPkgMIFPublisher, srtPktMIFVersion, strPkgMIFFilename, strPkgPreferedAddressType
Dim strPkgRefreshSourceFlag, strPkgShareName, strPkgShareType, strPkgSourceData, strPkgSourceVersion
Dim strNewPackageID
Dim objFSO : Set objFSO = CreateObject("Scripting.FileSystemObject")
Dim objLogFile : Set objLogFile = objFSO.CreateTextFile("MigratePackages.log")

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

objLogFile.WriteLine Now &" - Migrating Packages from " &strSourceServer &" to " &strTargetServer

'First create all Containers
Call CreateContainer

'Loop trought all packages exsisting on the source server
Set AllPackages = objSourceWMIService.ExecQuery("SELECT * FROM SMS_Package") 
For Each objPackage In AllPackages
 
 objLogFile.WriteLine Now &" - Migrating Package " &objPackage.PackageID
 
 'Get Package settings
 strPkgName = objPackage.Name
 strPkgManufacturer = objPackage.Manufacturer
 strPkgVersion = objPackage.Version
 strPkgLanguage = objPackage.Language
 strPkgDescription = objPackage.Description
 strPkgSource = objPackage.PkgSourcePath
 strPkgSourceFlag = objPackage.PkgSourceFlag
 strPkgExtendedData = objPackage.extendedData
 strPkgExtendedDataSize = objPackage.extendedDataSize
 strPkgForcedDisconnectDelay = objPackage.forcedDisconnectDelay
 strPkgForcedDisconnectEnabled = objPackage.forcedDisconnectEnabled
 strPkgForcedDisconnectRetries = objPackage.forcedDisconnectNumretries
 strPkgIcon = objPackage.Icon
 strPkgiconSize = objPackage.IconSize
 strPkgIgnoreAddressSchedule = objPackage.IgnoreAddressSchedule
 strPkgMIFFilename = objPackage.MifFileName
 strPkgMIFName = objPackage.MifName
 strPkgMIFPublisher = objPackage.MifPublisher
 srtPktMIFVersion = objPackage.MifVersion
 strPkgFlags = objPackage.PKGFlags
 strPkgPreferedAddressType = objPackage.PreferredAddressType
 strPkgPriority = objPackage.Priority
 strPkgRefreshSourceFlag = objPackage.RefreshPkgSourceFlag
 strPkgShareName = objPackage.ShareName
 strPkgShareType = objPackage.ShareType
 strPkgSourceData = objPackage.SourceDate
 strPkgSourceVersion = objPackage.SourceVersion 
 
 'Get Package Refresh Schedule
 Set objPackage = objSourceWMIService.Get("SMS_Package='" & objPackage.PackageID & "'" ) 
 On Error Resume Next
 Set schedule = objPackage.RefreshSchedule(0)
 On Error GoTo 0
 
 
 If err.number = 424 Then
     objLogFile.WriteLine Now &" - Package has no Schedule  " &objPackage.PackageID
 ElseIf err.number <> 0 Then
  objLogFile.WriteLine Now &" - An Error (" &err.number  &") occured while getting the Refresh Schedule from " &objPackage.PackageID
 Else
  On Error Resume Next
  schedtype=Schedule.Path_.Class
  
  Select Case (schedtype)
   Case "SMS_ST_RecurInterval"
     Set Token = objTargetWMIService.Get("SMS_ST_RecurInterval").SpawnInstance_()
     If schedule.MinuteSpan <> 0 Then Token.MinuteSpan = schedule.MinuteSpan
     If schedule.HourSpan <> 0 Then Token.HourSpan = schedule.HourSpan
     If schedule.DaySpan <> 0 Then Token.DaySpan = schedule.DaySpan
     If schedule.MinuteDuration <> 0 Then Token.MinuteDuration = schedule.MinuteDuration
     If schedule.HourDuration <> 0 Then Token.HourDuration = schedule.HourDuration
     If schedule.DayDuration <> 0 Then Token.DayDuration = schedule.DayDuration
     Token.StartTime = schedule.StartTime
     
     bolScheduleAvailable = True
     objLogFile.WriteLine Now &" - SMS_ST_RecurInterval Schedule available " &objPackage.PackageID
     
   Case "SMS_ST_RecurWeekly"
     Set Token = objTargetWMIService.Get("SMS_ST_RecurWeekly").SpawnInstance_()
     If schedule.MinuteDuration <> 0 Then Token.MinuteDuration = schedule.MinuteDuration
     If schedule.HourDuration <> 0 Then Token.HourDuration = schedule.HourDuration
     If schedule.DayDuration <> 0 Then Token.DayDuration = schedule.DayDuration
     If schedule.ForNumberOfWeeks <> 0 Then Token.ForNumberOfWeeks = schedule.ForNumberOfWeeks
     If schedule.Day <> 0 Then Token.Day = schedule.Day
     Token.StartTime = schedule.StartTime
     
     bolScheduleAvailable = True
     objLogFile.WriteLine Now &" - SMS_ST_RecurWeekly Schedule available " &objPackage.PackageID
     
   Case "SMS_ST_RecurMonthlyByDate"
     Set Token = objTargetWMIService.Get("SMS_ST_RecurMonthlyByDate").SpawnInstance_()
     If schedule.MinuteDuration <> 0 Then Token.MinuteDuration = schedule.MinuteDuration
     If schedule.HourDuration <> 0 Then Token.HourDuration = schedule.HourDuration
     If schedule.DayDuration <> 0 Then Token.DayDuration = schedule.DayDuration
     If schedule.ForNumberOfMonths <> 0 Then Token.ForNumberOfMonths = schedule.ForNumberOfMonths
     If schedule.MonthDay <> 0 Then Token.MonthDay = schedule.MonthDay
     Token.StartTime = schedule.StartTime
     
    bolScheduleAvailable = True
    objLogFile.WriteLine Now &" - SMS_ST_RecurMonthlyByDate Schedule available " &objPackage.PackageID
    
   Case "SMS_ST_RecurMonthlyByWeekday"
     Set Token = objTargetWMIService.Get("SMS_ST_RecurMonthlyByWeekday").SpawnInstance_()
     If schedule.MinuteDuration <> 0 Then Token.MinuteDuration = schedule.MinuteDuration
     If schedule.HourDuration <> 0 Then Token.HourDuration = schedule.HourDuration
     If schedule.DayDuration <> 0 Then Token.DayDuration = schedule.DayDuration
     If schedule.ForNumberOfMonths <> 0 Then Token.ForNumberOfMonths = schedule.ForNumberOfMonths
     If schedule.WeekOrder <> 0 Then Token.WeekOrder = schedule.WeekOrder

     Token.StartTime = schedule.StartTime
    objLogFile.WriteLine Now &" - SMS_ST_RecurMonthlyByWeekday Schedule available " &objPackage.PackageID
     
    bolScheduleAvailable = True

   Case "SMS_ST_NonRecurring"
     Set Token = objTargetWMIService.Get("SMS_ST_NonRecurring").SpawnInstance_()
     If schedule.MinuteDuration <> 0 Then Token.MinuteDuration = schedule.MinuteDuration
     If schedule.HourDuration <> 0 Then Token.HourDuration = schedule.HourDuration
     If schedule.DayDuration <> 0 Then Token.DayDuration = schedule.DayDuration
     Token.StartTime = schedule.StartTime
     
    bolScheduleAvailable = True
    objLogFile.WriteLine Now &" - SMS_ST_NonRecurring Schedule available " &objPackage.PackageID
     
    Case Else
    bolScheduleAvailable = False
    objLogFile.WriteLine Now &" - Unknown Schedule (skipping) " &objPackage.PackageID
  End Select
     
   End If
 

 'Get the container id of the package
 strContainerID = GetContainerID(objPackage.PackageID)
 
 'Get the containers name of the package
 strContainerName = ContainerIDToName(strContainerID)
 objLogFile.WriteLine Now &" - Old Container of the Package is " &strContainerName
 
 'Get the new containers id
 strNewContainerID = GetNewContainerID(strContainerName)
 objLogFile.WriteLine Now &" - New Container ID of the Package is " &strNewContainerID

 'Create the Package and get the new Package ID
 strNewPackageID = CreatePackage()
 
 Call CreatePrograms(objPackage.PackageID, strNewPackageID)
 
 'Move the Package into the Container
 Call MovePackageInToContainer(strNewContainerID, strNewPackageID)
 

Next

WScript.Echo "Done"
objLogFile.WriteLine Now &" - Finish"
 

Sub CreateContainer

Dim AllSourceContainer, objSourceContainer
Dim objTargetContainer

    Set AllSourceContainer = objSourceWMIService.ExecQuery("SELECT * FROM SMS_ObjectContainerNode WHERE ObjectType='2'")
    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 GetContainerID(PackageID)
Dim AllContainers, objContainer

Set AllContainers = objSourceWMIService.ExecQuery("SELECT * FROM SMS_ObjectContainerItem WHERE ObjectType='2' AND InstanceKey='" &PackageID &"'") 
For Each objContainer In AllContainers
 GetContainerID = objContainer.ContainerNodeID
Next

End Function

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

For Each objSourceContainer In AllSourceContainer
 GetNewContainerID = objSourceContainer.ContainerNodeID
Next

End Function

Function ContainerIDToName(ContainerID)
Dim AllContainers, objContainer

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

End Function

Function CreatePackage()
Dim newPackage, PKGSourcePath
Dim Package

 objLogFile.WriteLine Now &" - Creating the Package " &strPkgManufacturer &" " &strPkgName &" " &strPkgVersion &" " &strPkgLanguage

    Set newPackage = objTargetWMIService.Get("SMS_Package").SpawnInstance_

    newPackage.Name = strPkgName
 newPackage.Description = strPkgDescription 
 newPackage.extendedData = strPkgExtendedData
 newPackage.extendedDataSize = strPkgExtendedDataSize 
 newPackage.forcedDisconnectDelay = strPkgForcedDisconnectDelay
 newPackage.forcedDisconnectEnabled = strPkgForcedDisconnectEnabled
 newPackage.forcedDisconnectNumretries = strPkgForcedDisconnectRetries
 newPackage.Icon = strPkgIcon
 newPackage.IconSize =  strPkgiconSize
 newPackage.IgnoreAddressSchedule = strPkgIgnoreAddressSchedule
 newPackage.Language = strPkgLanguage 
 newPackage.Manufacturer = strPkgManufacturer
 newPackage.MifFileName = strPkgMIFFilename
 newPackage.MifName = strPkgMIFName
 newPackage.MifPublisher = strPkgMIFPublisher
 newPackage.MifVersion = srtPktMIFVersion
 newPackage.PKGFlags = strPkgFlags
 newPackage.PkgSourceFlag = strPkgSourceFlag
 PKGSourcePath= Replace(Lcase(strPkgSource),Lcase(strOldSourcePath),Lcase(strNewSourcePath)) 
 newPackage.PkgSourcePath = PKGSourcePath
 newPackage.PreferredAddressType = strPkgPreferedAddressType
 newPackage.Priority = strPkgPriority
 newPackage.RefreshPkgSourceFlag =  strPkgRefreshSourceFlag
 newPackage.ShareName =  strPkgShareName
 newPackage.ShareType =  strPkgShareType
 newPackage.SourceDate = strPkgSourceData
 newPackage.SourceVersion= strPkgSourceVersion
 newPackage.Version = strPkgVersion 
 
 'Create Refresh Schedule for Package
 If bolScheduleAvailable = True Then
  newPackage.RefreshSchedule = Array(Token)
 End If

    Path = newPackage.Put_
    
 'Get automatically assigned package ID
 Set Package=objTargetWMIService.Get(Path)
 CreatePackage = Package.PackageID


'Clean Variables
On Error Resume Next
Set Token = Nothing
Set schedule = Nothing
Set newPackage = Nothing
bolScheduleAvailable = False
schedtype = ""

End Function

Function MovePackageInToContainer(ContainerID, PackageID)
Dim folderItem

If ContainerID <> "" Then

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

Function CreatePrograms(OldPackageID, NewPackageID)
Dim AllPrograms, objProgram
Dim newProgram

Set AllPrograms = objSourceWMIService.ExecQuery("SELECT * FROM SMS_Program WHERE PackageID='" &OldPackageID &"'")
If Not AllPrograms Is Nothing Then
 For Each objProgram In AllPrograms
 
  objLogFile.WriteLine Now &" - Creating the Program " &objProgram.ProgramName &" in Package " &OldPackageID
 
  Set newProgram = objTargetWMIService.Get("SMS_Program").SpawnInstance_() 
  newProgram.CommandLine = objProgram.commandline
  newProgram.Comment = objProgram.comment
  newProgram.Description = objProgram.description
  newProgram.Deviceflags = objProgram.deviceflags
  newProgram.DiskSpaceReq = objProgram.DiskSpaceReq
  newProgram.DriveLetter = objProgram.DriveLetter
  newProgram.Duration = objProgram.Duration
  newProgram.extendedData = objProgram.extendedData
  newProgram.extendedDataSize = objProgram.extendedDataSize
  newProgram.icon = objProgram.icon
  newProgram.iconsize = objProgram.iconSize
  newProgram.MSIFilePath = objProgram.MSIFilePath
  newProgram.MSIProductID = objProgram.MSIProductID
  newProgram.PackageID = NewPackageID
  newProgram.ProgramFlags = objProgram.ProgramFlags
  newProgram.ProgramName = objProgram.ProgramName
  newProgram.RemovalKey = objProgram.RemovalKey
  newProgram.Requirements = objProgram.Requirements
  newProgram.SupportedOperatingSystems = objProgram.SupportedOperatingSystems
  newProgram.WorkingDirectory = objProgram.WorkingDirectory
   On Error Resume Next
   newProgram.Put_
 Next
End If

End Function

vbScript - Migrate Collections from SMS to SCCM

This script will migrate all collections from on site sms/sccm to another one
Be careful: Never run the script in your production until you've tested it!!!

view plainprint?

1. Option Explicit  
2. 'On Error Resume Next  
3. Dim strSourceServer : strSourceServer = "SMSServer"  
4. Dim strTargetServer : strTargetServer = "SCCMServer"  
5. Dim strSourceSiteCode : strSourceSiteCode = "000"  
6. Dim strTargetSiteCode : strTargetSiteCode = "000"  
7. dim intRuleCounter : intRuleCounter = 0  
8. dim strRuleName(100)  
9. Dim strRuleQuery(100)  
10. Dim strLimitToColl(100)  
11. Dim strCollectionRefType  
12. Dim strCollectionRefShd  
13. Dim Token, schedule, schedtype  
14. Dim bolScheduleAvailable : bolScheduleAvailable = False  
15. Dim objSourceWMIService, objTargetWMIService  
16. Dim AllCollections, objCollection  
17. Dim strCollectionName, strCollectionComment  
18. Dim AllParentCollections, objParentCollections, strParentCollectionName, strNewParentCollectionID  
19. Dim objRules, RuleSet  
20. Dim bolLimitCollExisting : bolLimitCollExisting = False  
21. Dim objFSO : Set objFSO = CreateObject("Scripting.FileSystemObject")  
22. Dim objLogFile : Set objLogFile = objFSO.CreateTextFile("MigrateCollections.log")  
23.   
24. Set objSourceWMIService = GetObject("winmgmts://" & strSourceServer & "\root\sms\site_" & strSourceSiteCode)  
25. Set objTargetWMIService = GetObject("winmgmts://" & strTargetServer & "\root\sms\site_" & strTargetSiteCode)  
26.   
27. objLogFile.WriteLine Now &" - Migrating Collections from " &strSourceServer &" to " &strTargetServer  
28.   
29. 'Loop trought all collections exsisting on the source server  
30. Set AllCollections = objSourceWMIService.ExecQuery("SELECT * FROM SMS_Collection WHERE NAME NOT LIKE 'All%' AND NAME NOT LIKE 'Root%'")   
31. For Each objCollection In AllCollections  
32.  strCollectionName = objCollection.Name  
33.  strCollectionComment = objCollection.Comment  
34.    
35.  objLogFile.WriteLine Now &" - Now migrating the Collection  " &objCollection.Name  
36.   
37.  Set objCollection = objSourceWMIService.Get("SMS_Collection='" & objCollection.CollectionID & "'" )   
38.    
39.  'Get Collection schedule  
40.  On Error Resume Next  
41.  Set schedule=objCollection.RefreshSchedule(0)  
42.  On Error GoTo 0  
43.    
44.  If err.number = 424 Then  
45.      objLogFile.WriteLine Now &" - Collection has no Schedule  " &objCollection.Name  
46.  ElseIf err.number <> 0 Then  
47.   objLogFile.WriteLine Now &" - An Error (" &err.number  &") occured while getting the Update Schedule from " &objCollection.Name  
48.  Else  
49.   On Error Resume Next  
50.   schedtype=Schedule.Path_.Class  
51.     
52.   Select Case (schedtype)  
53.    Case "SMS_ST_RecurInterval"  
54.      Set Token = objTargetWMIService.Get("SMS_ST_RecurInterval").SpawnInstance_()  
55.      If schedule.MinuteSpan <> 0 Then Token.MinuteSpan = schedule.MinuteSpan  
56.      If schedule.HourSpan <> 0 Then Token.HourSpan = schedule.HourSpan  
57.      If schedule.DaySpan <> 0 Then Token.DaySpan = schedule.DaySpan  
58.      If schedule.MinuteDuration <> 0 Then Token.MinuteDuration = schedule.MinuteDuration  
59.      If schedule.HourDuration <> 0 Then Token.HourDuration = schedule.HourDuration  
60.      If schedule.DayDuration <> 0 Then Token.DayDuration = schedule.DayDuration  
61.      Token.StartTime = schedule.StartTime  
62.        
63.      bolScheduleAvailable = True  
64.      objLogFile.WriteLine Now &" - SMS_ST_RecurInterval Schedule available " &objCollection.Name  
65.        
66.    Case "SMS_ST_RecurWeekly"  
67.      Set Token = objTargetWMIService.Get("SMS_ST_RecurWeekly").SpawnInstance_()  
68.      If schedule.MinuteDuration <> 0 Then Token.MinuteDuration = schedule.MinuteDuration  
69.      If schedule.HourDuration <> 0 Then Token.HourDuration = schedule.HourDuration  
70.      If schedule.DayDuration <> 0 Then Token.DayDuration = schedule.DayDuration  
71.      If schedule.ForNumberOfWeeks <> 0 Then Token.ForNumberOfWeeks = schedule.ForNumberOfWeeks  
72.      If schedule.Day <> 0 Then Token.Day = schedule.Day  
73.      Token.StartTime = schedule.StartTime  
74.        
75.      bolScheduleAvailable = True  
76.      objLogFile.WriteLine Now &" - SMS_ST_RecurWeekly Schedule available " &objCollection.Name  
77.        
78.    Case "SMS_ST_RecurMonthlyByDate"  
79.      Set Token = objTargetWMIService.Get("SMS_ST_RecurMonthlyByDate").SpawnInstance_()  
80.      If schedule.MinuteDuration <> 0 Then Token.MinuteDuration = schedule.MinuteDuration  
81.      If schedule.HourDuration <> 0 Then Token.HourDuration = schedule.HourDuration  
82.      If schedule.DayDuration <> 0 Then Token.DayDuration = schedule.DayDuration  
83.      If schedule.ForNumberOfMonths <> 0 Then Token.ForNumberOfMonths = schedule.ForNumberOfMonths  
84.      If schedule.MonthDay <> 0 Then Token.MonthDay = schedule.MonthDay  
85.      Token.StartTime = schedule.StartTime  
86.        
87.     bolScheduleAvailable = True  
88.     objLogFile.WriteLine Now &" - SMS_ST_RecurMonthlyByDate Schedule available " &objCollection.Name  
89.       
90.    Case "SMS_ST_RecurMonthlyByWeekday"  
91.      Set Token = objTargetWMIService.Get("SMS_ST_RecurMonthlyByWeekday").SpawnInstance_()  
92.      If schedule.MinuteDuration <> 0 Then Token.MinuteDuration = schedule.MinuteDuration  
93.      If schedule.HourDuration <> 0 Then Token.HourDuration = schedule.HourDuration  
94.      If schedule.DayDuration <> 0 Then Token.DayDuration = schedule.DayDuration  
95.      If schedule.ForNumberOfMonths <> 0 Then Token.ForNumberOfMonths = schedule.ForNumberOfMonths  
96.      If schedule.WeekOrder <> 0 Then Token.WeekOrder = schedule.WeekOrder  
97.   
98.      Token.StartTime = schedule.StartTime  
99.     objLogFile.WriteLine Now &" - SMS_ST_RecurMonthlyByWeekday Schedule available " &objCollection.Name  
100.        
101.     bolScheduleAvailable = True  
102.   
103.    Case "SMS_ST_NonRecurring"  
104.      Set Token = objTargetWMIService.Get("SMS_ST_NonRecurring").SpawnInstance_()  
105.      If schedule.MinuteDuration <> 0 Then Token.MinuteDuration = schedule.MinuteDuration  
106.      If schedule.HourDuration <> 0 Then Token.HourDuration = schedule.HourDuration  
107.      If schedule.DayDuration <> 0 Then Token.DayDuration = schedule.DayDuration  
108.      Token.StartTime = schedule.StartTime  
109.        
110.     bolScheduleAvailable = True  
111.     objLogFile.WriteLine Now &" - SMS_ST_NonRecurring Schedule available " &objCollection.Name  
112.        
113.     Case Else  
114.     bolScheduleAvailable = False  
115.     objLogFile.WriteLine Now &" - Unknown Schedule (skipping) " &objCollection.Name  
116.   End Select  
117.        
118.    End If  
119.    
120.  'Get Collection Rules  
121.   If Not IsNull(objCollection.CollectionRules) Then  
122.    objRules = objCollection.CollectionRules  
123.   
124.    For Each RuleSet In objRules  
125.     intRuleCounter = intRuleCounter + 1  
126.     strRuleName(intRuleCounter) = RuleSet.Rulename  
127.     strRuleQuery(intRuleCounter) = RuleSet.QueryExpression  
128.     If RuleSet.LimitToCollectionID <> "" Then  
129.       strLimitToColl(intRuleCounter) = LimitColID  
130.     End If  
131.   
132.     objLogFile.WriteLine Now &" - Collection Rule will be migrated " &strRuleName(intRuleCounter)  
133.    Next  
134.   Else  
135.    'WScript.Echo "Collection without Rules: " &strCollectionName  
136.    set strRuleName(0) = Nothing  
137.    Set strRuleName(0) = Nothing  
138.    intRuleCounter = 0  
139.     objLogFile.WriteLine Now &" - No Collection Rule available"  
140.   End If  
141.    
142.    
143.   'Get Parent Collection  
144.  Set AllParentCollections = objSourceWMIService.ExecQuery("SELECT parentCollectionID FROM SMS_CollectToSubCollect WHERE subCollectionID = '" &objCollection.CollectionID &"'")  
145.  For Each objParentCollections In AllParentCollections  
146.   'Determine the parent collections name  
147.   strParentCollectionName = GetParentCollectionName(objParentCollections.parentCollectionID)  
148.   objLogFile.WriteLine Now &" - Parent Collections name is " &strParentCollectionName  
149.     
150.   'Determine the parent collections id on the target server  
151.   strNewParentCollectionID = GetNewParentCollectionID(strParentCollectionName)  
152.   objLogFile.WriteLine Now &" - New Parent Collections ID is " &strNewParentCollectionID  
153.  Next  
154.   
155.  'Create the new collection  
156.  Call CreateCollection(strCollectionName, strCollectionComment, strNewParentCollectionID)  
157.    
158. Next  
159.   
160.   
161. Function GetParentCollectionName(CollectionID)  
162.    
163. Set AllParentCollections = objSourceWMIService.ExecQuery("SELECT Name FROM SMS_Collection WHERE CollectionID = '" &CollectionID &"'")  
164.   
165. For Each objParentCollections In AllParentCollections  
166.  GetParentCollectionName = objParentCollections.Name  
167. Next  
168.   
169. End Function  
170.   
171. Function GetNewParentCollectionID(CollectionName)  
172.    
173. Set AllParentCollections = objTargetWMIService.ExecQuery("SELECT CollectionID FROM SMS_Collection WHERE Name = '" &CollectionName &"'")  
174. For Each objParentCollections In AllParentCollections  
175.  GetNewParentCollectionID = objParentCollections.CollectionID  
176. Next  
177.   
178. End Function  
179.   
180. Function CreateCollection(CollectionName, CollectionComment, ParentCollectionID)  
181.   
182. Dim newCollection  
183. Dim collectionPath  
184. Dim newSubCollectToSubCollect  
185. Dim queryRule, newQueryRule  
186. Dim newCollectionRule  
187. Dim i  
188.   
189. ' Create the collection.  
190. Set newCollection = objTargetWMIService.Get("SMS_Collection").SpawnInstance_  
191. newCollection.Comment = CollectionComment  
192. newCollection.Name = CollectionName  
193. newCollection.OwnedByThisSite = True  
194. objLogFile.WriteLine Now &" - Collection created " &CollectionName  
195.   
196. 'Create Update Schedule for Collection  
197. If bolScheduleAvailable = True Then  
198.  newCollection.RefreshSchedule = Array(Token)  
199.  newCollection.RefreshType = 2  
200. End If  
201. objLogFile.WriteLine Now &" - Configured Collection Schedule " &CollectionName  
202.    
203. Set collectionPath = newCollection.Put_      
204.       
205. 'Set Parent Collection  
206. Set newSubCollectToSubCollect = objTargetWMIService.Get("SMS_CollectToSubCollect").SpawnInstance_  
207. newSubCollectToSubCollect.parentCollectionID = ParentCollectionID  
208. newSubCollectToSubCollect.subCollectionID = CStr(collectionPath.Keys("CollectionID"))  
209. newSubCollectToSubCollect.Put_  
210. objLogFile.WriteLine Now &" - Configured th Parent Collection " &CollectionName  
211.   
212. 'Create Query Memebership Rule  
213. Set queryRule = objTargetWMIService.Get("SMS_CollectionRuleQuery")  
214. Set newCollection = objTargetWMIService.Get(collectionPath.RelPath)  
215.           
216. For i = 1 To intRuleCounter  
217.  'Create the query rule.  
218.  Set newQueryRule = QueryRule.SpawnInstance_  
219.  newQueryRule.QueryExpression = strRuleQuery(i)  
220.  newQueryRule.RuleName = strRuleName(i)  
221.  If strLimitToColl(i) <> "" Then newQueryRule.LimitToCollectionID = strLimitToColl(i)  
222.  'Add the new query rule to a variable.  
223.  Set newCollectionRule = newQueryRule  
224.  'Add the rules to the collection.  
225.  newCollection.AddMembershipRule newCollectionRule  
226. Next  
227. objLogFile.WriteLine Now &" - Added Collection Rules " &CollectionName  
228.     
229. 'Call RequestRefresh to initiate the collection evaluator.  
230. newCollection.RequestRefresh False  
231.   
232. 'Clean Variables  
233. On Error Resume Next  
234. Set Token = Nothing  
235. Set schedule = Nothing  
236. Set newCollection = Nothing  
237. bolScheduleAvailable = False  
238. schedtype = ""  
239. intRuleCounter = 0  
240. On Error GoTo 0  
241.   
242. End Function  
243. objLogFile.WriteLine Now &" - Finish"  
244. WScript.Echo "Done"  

Option Explicit
'On Error Resume Next
Dim strSourceServer : strSourceServer = "SMSServer"
Dim strTargetServer : strTargetServer = "SCCMServer"
Dim strSourceSiteCode : strSourceSiteCode = "000"
Dim strTargetSiteCode : strTargetSiteCode = "000"
dim intRuleCounter : intRuleCounter = 0
dim strRuleName(100)
Dim strRuleQuery(100)
Dim strLimitToColl(100)
Dim strCollectionRefType
Dim strCollectionRefShd
Dim Token, schedule, schedtype
Dim bolScheduleAvailable : bolScheduleAvailable = False
Dim objSourceWMIService, objTargetWMIService
Dim AllCollections, objCollection
Dim strCollectionName, strCollectionComment
Dim AllParentCollections, objParentCollections, strParentCollectionName, strNewParentCollectionID
Dim objRules, RuleSet
Dim bolLimitCollExisting : bolLimitCollExisting = False
Dim objFSO : Set objFSO = CreateObject("Scripting.FileSystemObject")
Dim objLogFile : Set objLogFile = objFSO.CreateTextFile("MigrateCollections.log")

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

objLogFile.WriteLine Now &" - Migrating Collections from " &strSourceServer &" to " &strTargetServer

'Loop trought all collections exsisting on the source server
Set AllCollections = objSourceWMIService.ExecQuery("SELECT * FROM SMS_Collection WHERE NAME NOT LIKE 'All%' AND NAME NOT LIKE 'Root%'") 
For Each objCollection In AllCollections
 strCollectionName = objCollection.Name
 strCollectionComment = objCollection.Comment
 
 objLogFile.WriteLine Now &" - Now migrating the Collection  " &objCollection.Name

 Set objCollection = objSourceWMIService.Get("SMS_Collection='" & objCollection.CollectionID & "'" ) 
 
 'Get Collection schedule
 On Error Resume Next
 Set schedule=objCollection.RefreshSchedule(0)
 On Error GoTo 0
 
 If err.number = 424 Then
     objLogFile.WriteLine Now &" - Collection has no Schedule  " &objCollection.Name
 ElseIf err.number <> 0 Then
  objLogFile.WriteLine Now &" - An Error (" &err.number  &") occured while getting the Update Schedule from " &objCollection.Name
 Else
  On Error Resume Next
  schedtype=Schedule.Path_.Class
  
  Select Case (schedtype)
   Case "SMS_ST_RecurInterval"
     Set Token = objTargetWMIService.Get("SMS_ST_RecurInterval").SpawnInstance_()
     If schedule.MinuteSpan <> 0 Then Token.MinuteSpan = schedule.MinuteSpan
     If schedule.HourSpan <> 0 Then Token.HourSpan = schedule.HourSpan
     If schedule.DaySpan <> 0 Then Token.DaySpan = schedule.DaySpan
     If schedule.MinuteDuration <> 0 Then Token.MinuteDuration = schedule.MinuteDuration
     If schedule.HourDuration <> 0 Then Token.HourDuration = schedule.HourDuration
     If schedule.DayDuration <> 0 Then Token.DayDuration = schedule.DayDuration
     Token.StartTime = schedule.StartTime
     
     bolScheduleAvailable = True
     objLogFile.WriteLine Now &" - SMS_ST_RecurInterval Schedule available " &objCollection.Name
     
   Case "SMS_ST_RecurWeekly"
     Set Token = objTargetWMIService.Get("SMS_ST_RecurWeekly").SpawnInstance_()
     If schedule.MinuteDuration <> 0 Then Token.MinuteDuration = schedule.MinuteDuration
     If schedule.HourDuration <> 0 Then Token.HourDuration = schedule.HourDuration
     If schedule.DayDuration <> 0 Then Token.DayDuration = schedule.DayDuration
     If schedule.ForNumberOfWeeks <> 0 Then Token.ForNumberOfWeeks = schedule.ForNumberOfWeeks
     If schedule.Day <> 0 Then Token.Day = schedule.Day
     Token.StartTime = schedule.StartTime
     
     bolScheduleAvailable = True
     objLogFile.WriteLine Now &" - SMS_ST_RecurWeekly Schedule available " &objCollection.Name
     
   Case "SMS_ST_RecurMonthlyByDate"
     Set Token = objTargetWMIService.Get("SMS_ST_RecurMonthlyByDate").SpawnInstance_()
     If schedule.MinuteDuration <> 0 Then Token.MinuteDuration = schedule.MinuteDuration
     If schedule.HourDuration <> 0 Then Token.HourDuration = schedule.HourDuration
     If schedule.DayDuration <> 0 Then Token.DayDuration = schedule.DayDuration
     If schedule.ForNumberOfMonths <> 0 Then Token.ForNumberOfMonths = schedule.ForNumberOfMonths
     If schedule.MonthDay <> 0 Then Token.MonthDay = schedule.MonthDay
     Token.StartTime = schedule.StartTime
     
    bolScheduleAvailable = True
    objLogFile.WriteLine Now &" - SMS_ST_RecurMonthlyByDate Schedule available " &objCollection.Name
    
   Case "SMS_ST_RecurMonthlyByWeekday"
     Set Token = objTargetWMIService.Get("SMS_ST_RecurMonthlyByWeekday").SpawnInstance_()
     If schedule.MinuteDuration <> 0 Then Token.MinuteDuration = schedule.MinuteDuration
     If schedule.HourDuration <> 0 Then Token.HourDuration = schedule.HourDuration
     If schedule.DayDuration <> 0 Then Token.DayDuration = schedule.DayDuration
     If schedule.ForNumberOfMonths <> 0 Then Token.ForNumberOfMonths = schedule.ForNumberOfMonths
     If schedule.WeekOrder <> 0 Then Token.WeekOrder = schedule.WeekOrder

     Token.StartTime = schedule.StartTime
    objLogFile.WriteLine Now &" - SMS_ST_RecurMonthlyByWeekday Schedule available " &objCollection.Name
     
    bolScheduleAvailable = True

   Case "SMS_ST_NonRecurring"
     Set Token = objTargetWMIService.Get("SMS_ST_NonRecurring").SpawnInstance_()
     If schedule.MinuteDuration <> 0 Then Token.MinuteDuration = schedule.MinuteDuration
     If schedule.HourDuration <> 0 Then Token.HourDuration = schedule.HourDuration
     If schedule.DayDuration <> 0 Then Token.DayDuration = schedule.DayDuration
     Token.StartTime = schedule.StartTime
     
    bolScheduleAvailable = True
    objLogFile.WriteLine Now &" - SMS_ST_NonRecurring Schedule available " &objCollection.Name
     
    Case Else
    bolScheduleAvailable = False
    objLogFile.WriteLine Now &" - Unknown Schedule (skipping) " &objCollection.Name
  End Select
     
   End If
 
 'Get Collection Rules
  If Not IsNull(objCollection.CollectionRules) Then
   objRules = objCollection.CollectionRules

   For Each RuleSet In objRules
    intRuleCounter = intRuleCounter + 1
    strRuleName(intRuleCounter) = RuleSet.Rulename
    strRuleQuery(intRuleCounter) = RuleSet.QueryExpression
    If RuleSet.LimitToCollectionID <> "" Then
      strLimitToColl(intRuleCounter) = LimitColID
    End If

    objLogFile.WriteLine Now &" - Collection Rule will be migrated " &strRuleName(intRuleCounter)
   Next
  Else
   'WScript.Echo "Collection without Rules: " &strCollectionName
   set strRuleName(0) = Nothing
   Set strRuleName(0) = Nothing
   intRuleCounter = 0
    objLogFile.WriteLine Now &" - No Collection Rule available"
  End If
 
 
  'Get Parent Collection
 Set AllParentCollections = objSourceWMIService.ExecQuery("SELECT parentCollectionID FROM SMS_CollectToSubCollect WHERE subCollectionID = '" &objCollection.CollectionID &"'")
 For Each objParentCollections In AllParentCollections
  'Determine the parent collections name
  strParentCollectionName = GetParentCollectionName(objParentCollections.parentCollectionID)
  objLogFile.WriteLine Now &" - Parent Collections name is " &strParentCollectionName
  
  'Determine the parent collections id on the target server
  strNewParentCollectionID = GetNewParentCollectionID(strParentCollectionName)
  objLogFile.WriteLine Now &" - New Parent Collections ID is " &strNewParentCollectionID
 Next

 'Create the new collection
 Call CreateCollection(strCollectionName, strCollectionComment, strNewParentCollectionID)
 
Next


Function GetParentCollectionName(CollectionID)
 
Set AllParentCollections = objSourceWMIService.ExecQuery("SELECT Name FROM SMS_Collection WHERE CollectionID = '" &CollectionID &"'")

For Each objParentCollections In AllParentCollections
 GetParentCollectionName = objParentCollections.Name
Next

End Function

Function GetNewParentCollectionID(CollectionName)
 
Set AllParentCollections = objTargetWMIService.ExecQuery("SELECT CollectionID FROM SMS_Collection WHERE Name = '" &CollectionName &"'")
For Each objParentCollections In AllParentCollections
 GetNewParentCollectionID = objParentCollections.CollectionID
Next

End Function

Function CreateCollection(CollectionName, CollectionComment, ParentCollectionID)

Dim newCollection
Dim collectionPath
Dim newSubCollectToSubCollect
Dim queryRule, newQueryRule
Dim newCollectionRule
Dim i

' Create the collection.
Set newCollection = objTargetWMIService.Get("SMS_Collection").SpawnInstance_
newCollection.Comment = CollectionComment
newCollection.Name = CollectionName
newCollection.OwnedByThisSite = True
objLogFile.WriteLine Now &" - Collection created " &CollectionName

'Create Update Schedule for Collection
If bolScheduleAvailable = True Then
 newCollection.RefreshSchedule = Array(Token)
 newCollection.RefreshType = 2
End If
objLogFile.WriteLine Now &" - Configured Collection Schedule " &CollectionName
 
Set collectionPath = newCollection.Put_    
    
'Set Parent Collection
Set newSubCollectToSubCollect = objTargetWMIService.Get("SMS_CollectToSubCollect").SpawnInstance_
newSubCollectToSubCollect.parentCollectionID = ParentCollectionID
newSubCollectToSubCollect.subCollectionID = CStr(collectionPath.Keys("CollectionID"))
newSubCollectToSubCollect.Put_
objLogFile.WriteLine Now &" - Configured th Parent Collection " &CollectionName

'Create Query Memebership Rule
Set queryRule = objTargetWMIService.Get("SMS_CollectionRuleQuery")
Set newCollection = objTargetWMIService.Get(collectionPath.RelPath)
        
For i = 1 To intRuleCounter
 'Create the query rule.
 Set newQueryRule = QueryRule.SpawnInstance_
 newQueryRule.QueryExpression = strRuleQuery(i)
 newQueryRule.RuleName = strRuleName(i)
 If strLimitToColl(i) <> "" Then newQueryRule.LimitToCollectionID = strLimitToColl(i)
 'Add the new query rule to a variable.
 Set newCollectionRule = newQueryRule
 'Add the rules to the collection.
 newCollection.AddMembershipRule newCollectionRule
Next
objLogFile.WriteLine Now &" - Added Collection Rules " &CollectionName
  
'Call RequestRefresh to initiate the collection evaluator.
newCollection.RequestRefresh False

'Clean Variables
On Error Resume Next
Set Token = Nothing
Set schedule = Nothing
Set newCollection = Nothing
bolScheduleAvailable = False
schedtype = ""
intRuleCounter = 0
On Error GoTo 0

End Function
objLogFile.WriteLine Now &" - Finish"
WScript.Echo "Done"

SCCM SQL Query - Get all Computer objects which are not member of a specific collection and it's subcollections

This SQL query will return each computer which is not member of your defined collection and it's subcollections

view plainprint?

1. select Name0 From v_R_System Where v_R_System.Name0 not in(  
2. select  v_R_System.Name0 from v_R_System inner join v_ClientCollectionMembers on (v_R_System.ResourceID = v_ClientCollectionMembers.ResourceID)  
3. inner join v_CollectToSubCollect on (v_CollectToSubCollect.subCollectionID = v_ClientCollectionMembers.CollectionID)  
4. Where v_CollectToSubCollect.parentCollectionID = 'YourCollectionID')  
5. order By Name0  

select Name0 From v_R_System Where v_R_System.Name0 not in(
select  v_R_System.Name0 from v_R_System inner join v_ClientCollectionMembers on (v_R_System.ResourceID = v_ClientCollectionMembers.ResourceID)
inner join v_CollectToSubCollect on (v_CollectToSubCollect.subCollectionID = v_ClientCollectionMembers.CollectionID)
Where v_CollectToSubCollect.parentCollectionID = 'YourCollectionID')
order By Name0

It's also possible with muliple start collection

view plainprint?

1. select Name0 From v_R_System Where v_R_System.Name0 not in(  
2. select  v_R_System.Name0 from v_R_System inner join v_ClientCollectionMembers on (v_R_System.ResourceID = v_ClientCollectionMembers.ResourceID)  
3. inner join v_CollectToSubCollect on (v_CollectToSubCollect.subCollectionID = v_ClientCollectionMembers.CollectionID)  
4. Where v_CollectToSubCollect.parentCollectionID In('YourCollectionID1','YourCollectionID2','YourCollectionID3'))  
5. Order By Name0  

select Name0 From v_R_System Where v_R_System.Name0 not in(
select  v_R_System.Name0 from v_R_System inner join v_ClientCollectionMembers on (v_R_System.ResourceID = v_ClientCollectionMembers.ResourceID)
inner join v_CollectToSubCollect on (v_CollectToSubCollect.subCollectionID = v_ClientCollectionMembers.CollectionID)
Where v_CollectToSubCollect.parentCollectionID In('YourCollectionID1','YourCollectionID2','YourCollectionID3'))
Order By Name0