Need help with VBSscript 1

[scrapps]

Hey all, I'm trying to write a VBScript to capture information from about 5000 machines. I'm trying to get IP Address Model Number and Total Physical Memory but I'm new to this so I'm having troubles. I've pieced together the code below but the memory and model portion isn't working. The IP portion does seem to be working. Any help would be appreciated. thanks in advance.

On Error Resume Next

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 = "IP Address"
objExcel.Cells(1, 3).Value = "Total Physical Memory"
objExcel.Cells(1, 4).Value = "Model"

Set Fso = CreateObject("Scripting.FileSystemObject")
Set InputFile = fspenTextFile("PCName.Txt")

Do While Not (InputFile.atEndOfStream)
strComputer = InputFile.ReadLine

Set objWMIService = GetObject("winmgmts:\\" & strComputer & "\root\CIMV2")
Set colItems = objWMIService.ExecQuery( _
"Select IpAddress From Win32_NetworkAdapterConfiguration Where IPEnabled=TRUE")

if err.number=0 Then
ncol=0
For Each objItem in colItems
objExcel.Cells(intRow, 2+ncol).Value = join(objItem.IPAddress,",")
ncol=ncol+1
Next
else
objExcel.Cells(intRow, 2).Value = "not found"
end If
err.clear

objExcel.Cells(intRow, 1).Value = strComputer
intRow = intRow + 1

Set objWMIService = GetObject("winmgmts:\\" & strComputer & "\root\CIMV2")
Set colItems = objWMIService.ExecQuery("SELECT * FROM Win32_ComputerSystem", "WQL", _
wbemFlagReturnImmediately + wbemFlagForwardOnly)

For Each objItem In colItems
objExcel.Cells(intRow, 4).Value = join(objItem.Model,",")
intRow = intRow + 1
objExcel.Cells(intRow, 3).Value = Join(objItem.TotalPhysicalMemory,",")
intRow = intRow + 1

Next
Loop

objExcel.Range("A1:B1:C11").Select
objExcel.Selection.Interior.ColorIndex = 6
objExcel.Selection.Font.ColorIndex = 1
objExcel.Selection.Font.Bold = True
objExcel.Cells.EntireColumn.AutoFit

Set objWMIService = Nothing
Set colItems = Nothing
Set objExcel = Nothing

InputFile.close

set InputFile=nothing
set Fso=Nothing

Wscript.Echo "Done"

 

[tsuji]

This block.
[tt]
objExcel.Cells(intRow, 1).Value = strComputer
[red]'[/red]intRow = intRow + 1

Set objWMIService = GetObject("winmgmts:\\" & strComputer & "\root\CIMV2")
Set colItems = objWMIService.ExecQuery("SELECT * FROM Win32_ComputerSystem") [blue]'simply this suffice[/blue]

For Each objItem In colItems [blue]'only one item against all oddities[/blue]
objExcel.Cells(intRow, 4).Value = [blue]objItem.Model[/blue]
[red]'[/red]intRow = intRow + 1
objExcel.Cells(intRow, 3).Value = [blue]objItem.TotalPhysicalMemory[/blue]
[red]'[/red]intRow = intRow + 1

Next
[red]intRow = intRow + 1[/red]
Loop
[/tt]

 

[scrapps]

Thanks for your help.

 

[markdmac]

I would add that you probably want to convert to MB for the memory.

objExcel.Cells(intRow, 3).Value = objItem.TotalPhysicalMemory /1024\1024+1 & "MB"

I hope you find this post helpful.

Regards,

Mark

Check out my scripting solutions at

http://www.thespidersparlor.com/vbscript

Work SMARTER not HARDER. The Spider's Parlor's Admin Script Pack is a collection of Administrative scripts designed to make IT Administration easier! Save time, get more work done, get the Admin Script Pack.