Please check my script...Trying to get IP Address

[99ls1z]

I am trying to create an excel spreadsheet with two columns. Computer name and Ip address. I have a list of computer names in a text file "test.txt". The script is working but when it reaches a computer name that can't be accessed it quits or skips it. I would like for it to still list the computer name and for the ip address put "not found". I can't get this to work. Here is my script.


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"

Set Fso = CreateObject("Scripting.FileSystemObject")
Set InputFile = fspenTextFile("test.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")
For Each objItem in colItems
objExcel.Cells(intRow, 2).Value = objItem.IPAddress
Next

Set objWMIService = GetObject("winmgmts:\\" & strComputer & "\root\CIMV2")
Set colItems = objWMIService.ExecQuery("Select * From Win32_ComputerSystem")
For Each objItem in colItems
objExcel.Cells(intRow, 1).Value = objItem.Name
intRow = intRow + 1
Next

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

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

Wscript.Echo "Done

 

[tsuji]

This is how it be done with your sketch.
[tt]
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"

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

[blue]if err.number=0 then[/blue]
For Each objItem in colItems
objExcel.Cells(intRow, 2).Value = objItem.IPAddress
Next
[blue]else
objExcel.Cells(intRow, 2).Value = "not found"
end if
err.clear[/blue]

[blue]'functionally these two are enough, the rest is needless
objExcel.Cells(intRow, 1).Value = strComputer
intRow = intRow + 1
[/blue]
'Set objWMIService = GetObject("winmgmts:\\" & strComputer & "\root\CIMV2")
'Set colItems = objWMIService.ExecQuery("Select * From Win32_ComputerSystem")
'For Each objItem in colItems
'objExcel.Cells(intRow, 1).Value = objItem.Name
'intRow = intRow + 1 [blue]'logically wrong position[/blue]
'Next

loop

[blue]'move down to here[/blue]
objExcel.Range("A1:B1").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

[blue]'this close more material amongst all the cleaning up
Input.close[/blue]

set InputFile=nothing
set Fso=nothing

Wscript.Echo "Done"
[/tt]

 

[99ls1z]

Thanks. It looks like this is working.

 

[tsuji]

my typo here.
>[self]Input.close
[tt]InputFile.close[/tt]

 

[vajpowb]

I have tried this script because it is exactly what I have been looking for...trying to accomplish etc.

However, I am getting 0.0.0.0 when I know the client is online. The only modification I did to the script was to change the name of the input text file and fix the InputFile.Close command as posted.

Any clue why I would be having issues?

Thx.

 

[vajpowb]

Oh..also, is there anyway to get the site name also and create a column for that.

 

[guitarzan]

> I am getting 0.0.0.0 when I know the client is online
Can you ping the client? Can you ping by computername? Does it have a firewall?

> is there anyway to get the site name
What do you mean by "site name"? What site?

> also and create a column for that
Adding a column is easy, just follow the logic of the original script.

 

[tsuji]

I did not look into the script from the object implementation perspective but purely from the syntactic perspective. So thanks vajpowb for re-opening this thread.

I've taken another look into the script and sure it is very much defective. This is a revision that will display all the physical data interested.
[tt]
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"

Set Fso = CreateObject("Scripting.FileSystemObject")
Set InputFile = fspenTextFile("test.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
[blue]ncol=0[/blue]
For Each objItem in colItems
objExcel.Cells(intRow, 2[blue]+ncol[/blue]).Value = [blue]join(objItem.IPAddress,",")[/blue]
[blue]ncol=ncol+1[/blue]
Next
else
objExcel.Cells(intRow, 2).Value = "not found"
end if
err.clear

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

loop

objExcel.Range("A1:B1").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"
[/tt]

 

[vajpowb]

Thanks Tsuji..works great!

For site name I was looking for:

Set objADSysInfo = CreateObject("ADSystemInfo")

WScript.Echo "Current site name: " & objADSysInfo.SiteName

except for remote pc.

 

[dm4ever]

To get the IP address I would use ping...this way you know the active address being used.

WinXP or better (note: the target machine does not need to be WinXP or better; just the local machine from which the script is being run from)

WScript.Echo ResolveIP("[URL unfurl="true"]www.google.com")[/URL]

Function ResolveIP(strComputer)
	Dim wmiQuery : wmiQuery = "Select * From Win32_PingStatus Where Address = '" & strComputer & "'"
	
	Dim objWMIService : Set objWMIService = GetObject("winmgmts:\\.\root\cimv2")
	Dim objPing : Set objPing = objWMIService.ExecQuery(wmiQuery)
	Dim objStatus
	ResolveIP = "Computer is Unreachable!"
	For Each objStatus In objPing
		If objStatus.Statuscode = 0 Then ResolveIP = objStatus.ProtocolAddress
	Next
End Function

Win2k

WScript.Echo ResolveIP("[URL unfurl="true"]www.google.com")[/URL]

Function ResolveIP(computerName)
	Dim objShell  :  Set objShell = CreateObject("WScript.Shell")
	Dim objExec   :  Set objExec = objShell.Exec("ping " & computerName & " -n 1")
	Dim strOutput : strOutput = objExec.StdOut.ReadAll
	Dim RegEx     :  Set RegEx = New RegExp
	RegEx.Pattern = "Pinging\s.+\s\[(\d{1,3}\.\d{1,3}\.\d{1,3}\.\d{1,3})\]"
	RegEx.IgnoreCase = True
	RegEx.Global = True
	ResolveIP = "Computer is unreachable/invalid!"
	If RegEx.Test(strOutput) Then ResolveIP = RegEx.Execute(strOutput)(0).Submatches(0)
End Function

--------------------------------------------------------------------------------
dm4ever
My philosophy: K.I.S.S - Keep It Simple Stupid