Don Hite at myITforum.com

VBS Script To Emulate The SMS Consoles All Systems Collection Information And Send To Excel

This VBS script will take an SMS site server name as well as its site code via input dialog boxes and will write the following results to an excel spreadsheet based on the information contained in the SMS admin console’s “All Systems” Collection (Sms_Cm_Res_Coll_Sms00001): Name, Resource Class, Domain, Site Code, Client, Assigned, Type, Obsolete and Active.

VBS Script:

strComputer = InputBox ("Enter SMS Server Name")

strSiteCode = InputBox ("Enter Site Code")

Set objExcel = CreateObject("Excel.Application")

objExcel.Visible = True

objExcel.Workbooks.Add

intRow = 2

objExcel.Cells(1, 1).Value = "Name"

objExcel.Cells(1, 2).Value = "Resource Class"

objExcel.Cells(1, 3).Value = "Domain"

objExcel.Cells(1, 4).Value = "Site Code"

objExcel.Cells(1, 5).Value = "Client"

objExcel.Cells(1, 6).Value = "Assigned"

objExcel.Cells(1, 7).Value = "Type"

objExcel.Cells(1, 8).Value = "Obsolete"

objExcel.Cells(1, 9).Value = "Active"

Set objWMIService = GetObject("winmgmts://" & strComputer & "\root\sms\site_" & strSiteCode)

Set colItems = objWMIService.ExecQuery("Select * from Sms_Cm_Res_Coll_Sms00001")

For Each objItem in colItems

Select Case objItem.IsClient

Case True objIsClient = "Yes"

Case False objIsClient = "No"

End Select

Select Case objItem.IsAssigned

Case True objIsAssigned = "Yes"

Case False objIsAssigned = "No"

End Select

Select Case objItem.ClientType

Case 0 objClientType = "Legacy"

Case 1 objClientType = "Advanced"

Case 3 objClientType = "Device"

End Select

Select Case objItem.ResourceType

Case 3 objResourceType = "User Group"

Case 4 objResourceType = "User"

Case 5 objResourceType = "System"

End Select

Select Case objItem.IsObsolete

Case True objIsObsolete = "Yes"

Case False objIsObsolete = "No"

End Select

Select Case objItem.IsActive

Case True objIsActive = "Yes"

Case False objIsActive = "No"

End Select

objExcel.Cells(intRow, 1).Value = objItem.Name

objExcel.Cells(intRow, 2).Value = objResourceType

objExcel.Cells(intRow, 3).Value = objItem.Domain

objExcel.Cells(intRow, 4).Value = objItem.SiteCode

objExcel.Cells(intRow, 5).Value = objIsClient

objExcel.Cells(intRow, 6).Value = objIsAssigned

objExcel.Cells(intRow, 7).Value = objClientType

objExcel.Cells(intRow, 8).Value = objIsObsolete

objExcel.Cells(intRow, 9).Value = objIsActive

intRow = intRow + 1

Next

objExcel.Range("A1:I1").Select

objExcel.Selection.Interior.ColorIndex = 19

objExcel.Selection.Font.ColorIndex = 11

objExcel.Selection.Font.Bold = True

objExcel.Cells.EntireColumn.AutoFit

MsgBox "Done"