Don Hite at myITforum.com

VBS Script To Retrieve All Systems with Hardware Inventory Collected And Send To Excel

This VBS Script will take a ConfigMgr 2007 or SMS 2003 site server name and site code from input dialog boxes and write the All Systems with Hardware Inventory Collected information to an excel spreadsheet sorted alphabetically.

VBS Script:

strServer = InputBox ("Enter Site Server Name")

strDatabase = InputBox ("Enter Three Letter Site Code")

Set objExcel = CreateObject("Excel.Application")

objExcel.Visible = True

objExcel.Workbooks.Add

intRow = 2

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

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

objExcel.Cells(1, 3).Value = "Time Stamp"

Const adOpenStatic = 3

Const adLockOptimistic = 3

Set objConnection = CreateObject("ADODB.Connection")

objConnection.Open "Provider=SQLOLEDB;Data Source =" & strServer & ";" & _

"Trusted_Connection=Yes;Initial Catalog =SMS_" & strDatabase

Set objRecordSet = CreateObject("ADODB.Recordset")

objRecordSet.Open _

" Select Disc.Name0, Data.Domain0, Data.TimeKey" & _

" From System_Disc Disc" & _

" Join System_Data Data on Data.MachineID = Disc.ItemKey" _

, objConnection, adOpenStatic, adLockOptimistic

objRecordSet.MoveFirst

Do Until objRecordSet.EOF

objExcel.Cells(intRow, 1).Value = objRecordSet.Fields("Name0").Value

objExcel.Cells(intRow, 2).Value = objRecordSet.Fields("Domain0").Value

objExcel.Cells(intRow, 3).Value = objRecordSet.Fields("TimeKey").Value

objRecordSet.MoveNext

intRow = intRow + 1

Loop

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

objExcel.Selection.Font.ColorIndex = 11

objExcel.Selection.Font.Bold = True

objExcel.Cells.EntireColumn.AutoFit

Set objSheet = objExcel.ActiveWorkbook.Worksheets(1)

Set objRange = objExcel.Range("A1")

objRange.Sort objRange,1,,,,,,1

MsgBox "Done"