• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 532
  • Last Modified:

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

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

0
bsharath
Asked:
bsharath
  • 12
  • 5
  • 3
  • +1
1 Solution
 
JustWorkingCommented:
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
0
 
bsharathAuthor 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
0
 
bsharathAuthor 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
0
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

 
JustWorkingCommented:
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

0
 
bsharathAuthor Commented:
Colum C & D i just get NULL for all
0
 
JustWorkingCommented:
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
0
 
bsharathAuthor 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
0
 
bsharathAuthor Commented:
Any views on this.
0
 
zoofanCommented:
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

0
 
bsharathAuthor 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
0
 
zoofanCommented:
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

0
 
bsharathAuthor 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?
0
 
zoofanCommented:
gimmie a few min.



zf
0
 
bsharathAuthor Commented:
Hi Zoofan any help....
just a reminder....
0
 
yehudahaCommented:
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

0
 
bsharathAuthor 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
0
 
yehudahaCommented:
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

0
 
bsharathAuthor Commented:
In colum "D" i get Offline. Can that also be removed
0
 
yehudahaCommented:
does my above code works ?

colum was there it's not my addon

but you want to remove dead colum completly ?
0
 
bsharathAuthor 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
0
 
yehudahaCommented:
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

0
 
bsharathAuthor Commented:
Thank U... :-)))
0
 
yehudahaCommented:
no problem :-)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

  • 12
  • 5
  • 3
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now