Don Hite at myITforum.com

Sending All Systems Collection Information From A List Of Machines 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) from a text file: Name, Resource Class, Domain, Site Code, Client, Assigned, Type, Obsolete and Active.

Note: Change the line that reads: Set InputFile = fso.OpenTextFile("MachineList.Txt") to your text file name as needed.

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 Fso = CreateObject("Scripting.FileSystemObject")

Set InputFile = fso.OpenTextFile("MachineList.Txt")

Do While Not (InputFile.atEndOfStream)

strResource = InputFile.ReadLine

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

Set colItems = objWMIService.ExecQuery("Select * from Sms_Cm_Res_Coll_Sms00001 Where Name ='" & strResource & "'")

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

Loop

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"