Don Hite at myITforum.com

Sending All Systems Collection Information To Excel Using Wildcards For Machine Names

This VBS script will take an SMS site server name, site code and a partial machine name where you specify a part of the machine name(s) you want to enumerate via a series of 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.

Note: Change the line that reads: Where Name Like '" & strResource & "%" & "'") To your specified wildcard string (%) string.

The script below will list information about all of your machines where the name is XXX<Something>. For example if you want to gather information for all of your SMS clients where the machine names are <Something>XXX<Something> change the line to read: Where Name Like '" & "%" & strResource & "%" & "'") and so on.

VBS Script:

strComputer = InputBox ("Enter SMS Server Name")

strSiteCode = InputBox ("Enter Site Code")

strResource = InputBox ("Enter Machine Name")

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 Where Name Like '" & 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

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"