We help IT Professionals succeed at work.

We've partnered with Certified Experts, Carl Webster and Richard Faulkner, to bring you a podcast all about Citrix Workspace, moving to the cloud, and analytics & intelligence. Episode 2 coming soon!Listen Now

x

Pinging script to be change to be able to display the ip address.

Medium Priority
597 Views
Last Modified: 2012-05-06
Hi,

Pinging script to be change to be able to display the ip address.
I want the ip even when ping success or failure. So i know all the ip's. In some cases the ip's would have gone expired. So this would help me find machines that may not be in network for a long time.

REgards
Sharath
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 = "Alive"
 
objExcel.Cells(1, 3).Value = "Dead"
 
 
Set Fso = CreateObject("Scripting.FileSystemObject")
 
Set InputFile = fso.OpenTextFile("servers.txt")
 
 
 
Do While Not (InputFile.atEndOfStream)
 
HostName = InputFile.ReadLine
 
 
 
Set WshShell = WScript.CreateObject("WScript.Shell")
 
Ping = WshShell.Run("ping -n 1 " & HostName, 0, True)
 
 
 
objExcel.Cells(intRow, 1).Value = HostName
 
 
 
Select Case Ping
 
Case 0 objExcel.Cells(intRow, 2).Value = "On Line"
 
Case 1 objExcel.Cells(intRow, 3).Value = "Off Line"
 
End Select
 
 
 
intRow = intRow + 1
 
Loop
 
 
 
objExcel.Range("A1:B1:C1").Select
 
objExcel.Selection.Interior.ColorIndex = 19
 
objExcel.Selection.Font.ColorIndex = 11
 
objExcel.Selection.Font.Bold = True
 
objExcel.Cells.EntireColumn.AutoFit

Open in new window

Comment
Watch Question

If I understand you correctly can't you update your servers.txt with the list of your IPs. The script will do the same thing but display the IPs instead of the host names.

servers.txt

10.119.228.1
10.119.228.2
10.119.228.3
10.119.228.4
etc
etc

Author

Commented:
No i will need the ip's from the names that i provide in the txt file.
Actually i dont have the ipaddresses and many times my query would be on names

Author

Commented:
No i will need the ip's from the names that i provide in the txt file.
Actually i dont have the ipaddresses and many times my query would be on names
Okay I think I see now. Try the following code below. I don't remember where I got it however it will read your servers.txt file resolve the IP and create a results.cvs file with the information. If a server is online it will write the IP in the B column if it is dead it will write Dead in the B column.
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objLogFile = objFSO.CreateTextFile("results.csv")
 
Set objComputers = objFSO.OpenTextFile("servers.txt")
Do While Not objComputers.AtEndOfStream
username = ""
strComputer = objComputers.ReadLine
 
Set objPing = GetObject("winmgmts:{impersonationLevel=impersonate}")._
ExecQuery ("select Replysize from Win32_PingStatus where address = '" & strComputer & "'")
 
For Each objStatus In objPing
If IsNull(objStatus.ReplySize) Then
 
objLogFile.WriteLine strComputer & ",DEAD" & ",NULL" & ",NULL"
 
Else
 
Set objWMIService = GetObject("winmgmts:" _
    & "{impersonationLevel=impersonate}!\\" & strComputer & "\root\cimv2")
 
Set colItems = objWMIService.ExecQuery _
    ("Select * From Win32_NetworkAdapterConfiguration Where IPEnabled = True")
 
Set colComputer = objWMIService.ExecQuery _
    ("Select * from Win32_ComputerSystem")
 
 
For Each objComputer in colComputer
username = objComputer.UserName
Next
 
for each objitem in colitems
 
strIPAddress = Join(objitem.IPAddress, ",")
 
objLogFile.WriteLine strComputer & "," & strIPAddress & "," & username & "," & objitem.MACAddress
 
Exit For
 
next
 
End If
Next
 
Loop
WScript.Echo "servers.txt is finished."

Open in new window

Author

Commented:
Colum C & D i just get NULL for all
I found the EE for the file I used. It may help as when I run it resolves correctly.
http://www.experts-exchange.com/Programming/Languages/Visual_Basic/Q_24090648.html

Author

Commented:
Hi yes i get the ip and mac. Can i get if the machine is live or not and if not live i will need the ip address of the machine

Author

Commented:
Any views on this.

Commented:
Hi Bsarath,

This will perform a DNS lookup on any offline computer,

if DNS resolves will log the entry with
NAME,IP,OFFLINE,OFFLINE  

if no dns record exists it will log as
NAME,FAILED,OFFLINE,OFFLINE




zf
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objLogFile = objFSO.CreateTextFile("results.csv")
 
Set objComputers = objFSO.OpenTextFile("servers.txt")
Do While Not objComputers.AtEndOfStream
username = ""
strComputer = objComputers.ReadLine
 
Set objPing = GetObject("winmgmts:{impersonationLevel=impersonate}")._
ExecQuery ("select Replysize from Win32_PingStatus where address = '" & strComputer & "'")
 
For Each objStatus In objPing
If IsNull(objStatus.ReplySize) Then
 
objLogFile.WriteLine strComputer & "," & DNSLookup(strComputer) & ",OFFLINE" & ",OFFLINE"
 
Else
 
Set objWMIService = GetObject("winmgmts:" _
    & "{impersonationLevel=impersonate}!\\" & strComputer & "\root\cimv2")
 
Set colItems = objWMIService.ExecQuery _
    ("Select * From Win32_NetworkAdapterConfiguration Where IPEnabled = True")
 
Set colComputer = objWMIService.ExecQuery _
    ("Select * from Win32_ComputerSystem")
 
 
For Each objComputer in colComputer
username = objComputer.UserName
Next
 
for each objitem in colitems
 
strIPAddress = Join(objitem.IPAddress, ",")
 
objLogFile.WriteLine strComputer & "," & strIPAddress & "," & username & "," & objitem.MACAddress
 
Exit For
 
next
 
End If
Next
 
Loop
 
 
WScript.Echo "servers.txt is finished."
 
WScript.Quit(0)
Function DNSLookup(sAlias)
  'This script is provided under the Creative Commons license located
  'at http://creativecommons.org/licenses/by-nc/2.5/ . It may not
  'be used for commercial purposes with out the expressed written consent
  'of NateRice.com
 
  If len(sAlias) = 0 Then
    DNSLookup = "Failed."
    Exit Function
  End If
 
  Const OpenAsDefault = -2
  Const FailIfNotExist = 0
  Const ForReading = 1
 
  Set oShell = CreateObject("WScript.Shell")
  Set oFSO = CreateObject("Scripting.FileSystemObject")
  sTemp = oShell.ExpandEnvironmentStrings("%TEMP%")
  sTempFile = sTemp & "\" & oFSO.GetTempName
 
  oShell.Run "%comspec% /c nslookup " & sAlias & ">" & sTempFile, 0, True
 
  Set fFile = oFSO.OpenTextFile(sTempFile, ForReading, FailIfNotExist, OpenAsDefault)
  sResults = fFile.ReadAll
  fFile.Close
  oFSO.DeleteFile (sTempFile)
 
  aIP = Split(sResults, "Address:")
 
  If UBound(aIP) < 2 Then
    DNSLookup = "Failed."
  Else
    aIPTemp = Split(aIP(2), Chr(13))
    DNSLookup = trim(aIPTemp(0))
  End If
 
  Set oShell = Nothing
  Set oFSO = Nothing
End Function

Open in new window

Author

Commented:
Thanks a lot... I get this

---------------------------
Windows Script Host
---------------------------
Script:      D:\Ping.vbs
Line:      19
Char:      1
Error:      Permission denied: 'GetObject'
Code:      800A0046
Source:       Microsoft VBScript runtime error

---------------------------
OK  
---------------------------

Can i get the headers in the row 1 of the CSV and open once the task is done. if possible can i get in excel as it was before. So i could see the progress...On the screen

Commented:
Only reason for the error would be running with an account without admin rights over the remotes.


added header row in code below.


zf

Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objLogFile = objFSO.CreateTextFile("results.csv")
 
Set objComputers = objFSO.OpenTextFile("servers.txt")
objLogFile.WriteLine"Computer Name,IP Address,Current User,MAC address"
Do While Not objComputers.AtEndOfStream
username = ""
strComputer = objComputers.ReadLine
 
Set objPing = GetObject("winmgmts:{impersonationLevel=impersonate}")._
ExecQuery ("select Replysize from Win32_PingStatus where address = '" & strComputer & "'")
 
For Each objStatus In objPing
If IsNull(objStatus.ReplySize) Then
 
objLogFile.WriteLine strComputer & "," & DNSLookup(strComputer) & ",OFFLINE" & ",OFFLINE"
 
Else
 
Set objWMIService = GetObject("winmgmts:" _
    & "{impersonationLevel=impersonate}!\\" & strComputer & "\root\cimv2")
 
Set colItems = objWMIService.ExecQuery _
    ("Select * From Win32_NetworkAdapterConfiguration Where IPEnabled = True")
 
Set colComputer = objWMIService.ExecQuery _
    ("Select * from Win32_ComputerSystem")
 
 
For Each objComputer in colComputer
username = objComputer.UserName
Next
 
for each objitem in colitems
 
strIPAddress = Join(objitem.IPAddress, ",")
 
objLogFile.WriteLine strComputer & "," & strIPAddress & "," & username & "," & objitem.MACAddress
 
Exit For
 
next
 
End If
Next
 
Loop
 
 
WScript.Echo "servers.txt is finished."
 
WScript.Quit(0)
Function DNSLookup(sAlias)
  'This script is provided under the Creative Commons license located
  'at http://creativecommons.org/licenses/by-nc/2.5/ . It may not
  'be used for commercial purposes with out the expressed written consent
  'of NateRice.com
 
  If len(sAlias) = 0 Then
    DNSLookup = "Failed."
    Exit Function
  End If
 
  Const OpenAsDefault = -2
  Const FailIfNotExist = 0
  Const ForReading = 1
 
  Set oShell = CreateObject("WScript.Shell")
  Set oFSO = CreateObject("Scripting.FileSystemObject")
  sTemp = oShell.ExpandEnvironmentStrings("%TEMP%")
  sTempFile = sTemp & "\" & oFSO.GetTempName
 
  oShell.Run "%comspec% /c nslookup " & sAlias & ">" & sTempFile, 0, True
 
  Set fFile = oFSO.OpenTextFile(sTempFile, ForReading, FailIfNotExist, OpenAsDefault)
  sResults = fFile.ReadAll
  fFile.Close
  oFSO.DeleteFile (sTempFile)
 
  aIP = Split(sResults, "Address:")
 
  If UBound(aIP) < 2 Then
    DNSLookup = "Failed."
  Else
    aIPTemp = Split(aIP(2), Chr(13))
    DNSLookup = trim(aIPTemp(0))
  End If
 
  Set oShell = Nothing
  Set oFSO = Nothing
End Function

Open in new window

Author

Commented:
Thank you works perfect. If permission error can this be skipped. by logging the error in the csv. As i am running it from a Domain\Administrator.
How do i know if the machine is online or offline. Does the mac address mean its got from the machine directly?

Commented:
gimmie a few min.



zf

Author

Commented:
Hi Zoofan any help....
just a reminder....

Commented:
hey

i didn't use any above codes

i just added resolve function to your original code and it doesn't have a permission issue
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 = "IPAdress"
 
objExcel.Cells(1, 3).Value = "Alive"
 
objExcel.Cells(1, 4).Value = "Dead"
 
 
Set Fso = CreateObject("Scripting.FileSystemObject")
 
Set InputFile = fso.OpenTextFile("c:\servers.txt")
 
 
 
Do While Not (InputFile.atEndOfStream)
 
HostName = InputFile.ReadLine
 
 
 
Set WshShell = WScript.CreateObject("WScript.Shell")
 
Ping = WshShell.Run("ping -n 1 " & HostName, 0, True)
 
 
 
objExcel.Cells(intRow, 1).Value = HostName
 
objExcel.Cells(intRow, 2).Value = ResolveIP(HostName) 
 
Select Case Ping
 
Case 0 objExcel.Cells(intRow, 3).Value = "On Line"
 
Case 1 objExcel.Cells(intRow, 4).Value = "Off Line"
 
End Select
 
 
 
intRow = intRow + 1
 
Loop
 
 
 
objExcel.Range("A1:B1:C1:D1").Select
 
objExcel.Selection.Interior.ColorIndex = 19
 
objExcel.Selection.Font.ColorIndex = 11
 
objExcel.Selection.Font.Bold = True
 
objExcel.Cells.EntireColumn.AutoFit
 
 
 
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 = "\[(\d{1,3}\.\d{1,3}\.\d{1,3}\.\d{1,3})\]"
   RegEx.Global = True
   If RegEx.Test(strOutput) Then
       ResolveIP = RegEx.Execute(strOutput)(0).Submatches(0)
   Else
       ResolveIP = "IP Address could not be resolved."
   End If
End Function

Open in new window

Author

Commented:
Works fine but my issue is to now if there ia a blank row in txt file
i get this
IP Address could not be resolved.

can i get the row blank in excel

Commented:
try this
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 = "IPAdress"
 
objExcel.Cells(1, 3).Value = "Alive"
 
objExcel.Cells(1, 4).Value = "Dead"
 
 
Set Fso = CreateObject("Scripting.FileSystemObject")
 
Set InputFile = fso.OpenTextFile("c:\servers.txt")
 
 
 
Do While Not (InputFile.atEndOfStream)
 
HostName = InputFile.ReadLine
 
 
 
Set WshShell = WScript.CreateObject("WScript.Shell")
 
Ping = WshShell.Run("ping -n 1 " & HostName, 0, True)
 
 
 
objExcel.Cells(intRow, 1).Value = HostName
 
If hostname = "" Then
objExcel.Cells(intRow, 2).Value = ""
Else 
objExcel.Cells(intRow, 2).Value = ResolveIP(HostName) 
End if
 
Select Case Ping
 
Case 0 objExcel.Cells(intRow, 3).Value = "On Line"
 
Case 1 objExcel.Cells(intRow, 4).Value = "Off Line"
 
End Select
 
 
 
intRow = intRow + 1
 
Loop
 
 
 
objExcel.Range("A1:B1:C1:D1").Select
 
objExcel.Selection.Interior.ColorIndex = 19
 
objExcel.Selection.Font.ColorIndex = 11
 
objExcel.Selection.Font.Bold = True
 
objExcel.Cells.EntireColumn.AutoFit
 
 
 
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 = "\[(\d{1,3}\.\d{1,3}\.\d{1,3}\.\d{1,3})\]"
   RegEx.Global = True
   If RegEx.Test(strOutput) Then
       ResolveIP = RegEx.Execute(strOutput)(0).Submatches(0)
   Else
       ResolveIP = "IP Address could not be resolved."
   End If
End Function

Open in new window

Author

Commented:
In colum "D" i get Offline. Can that also be removed

Commented:
does my above code works ?

colum was there it's not my addon

but you want to remove dead colum completly ?

Author

Commented:
No not remove
When there is a blank row in the txt file.
it gets the data to excel as offline
just for these blank rows i want the offline to be removed
Commented:
here
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 = "IPAdress"
 
objExcel.Cells(1, 3).Value = "Alive"
 
objExcel.Cells(1, 4).Value = "Dead"
 
 
Set Fso = CreateObject("Scripting.FileSystemObject")
 
Set InputFile = fso.OpenTextFile("c:\servers.txt")
 
 
 
Do While Not (InputFile.atEndOfStream)
 
HostName = InputFile.ReadLine
 
If Not HostName = "" then 
 
Set WshShell = WScript.CreateObject("WScript.Shell")
 
Ping = WshShell.Run("ping -n 1 " & HostName, 0, True)
 
 
 
objExcel.Cells(intRow, 1).Value = HostName
 
objExcel.Cells(intRow, 2).Value = ResolveIP(HostName) 
 
Select Case Ping
 
Case 0 objExcel.Cells(intRow, 3).Value = "On Line"
 
Case 1 objExcel.Cells(intRow, 4).Value = "Off Line"
 
End Select
 
 
 
intRow = intRow + 1
 
End if
 
Loop
 
 
 
objExcel.Range("A1:B1:C1:D1").Select
 
objExcel.Selection.Interior.ColorIndex = 19
 
objExcel.Selection.Font.ColorIndex = 11
 
objExcel.Selection.Font.Bold = True
 
objExcel.Cells.EntireColumn.AutoFit
 
 
 
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 = "\[(\d{1,3}\.\d{1,3}\.\d{1,3}\.\d{1,3})\]"
   RegEx.Global = True
   If RegEx.Test(strOutput) Then
       ResolveIP = RegEx.Execute(strOutput)(0).Submatches(0)
   Else
       ResolveIP = "IP Address could not be resolved."
   End If
End Function

Open in new window

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts

Author

Commented:
Thank U... :-)))

Commented:
no problem :-)
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.