?
Solved

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

Posted on 2009-02-18
23
Medium Priority
?
516 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

0
Comment
Question by:bsharath
  • 12
  • 5
  • 3
  • +1
23 Comments
 
LVL 8

Expert Comment

by:JustWorking
ID: 23673841
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
 
LVL 11

Author Comment

by:bsharath
ID: 23677363
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
 
LVL 11

Author Comment

by:bsharath
ID: 23677364
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 8

Expert Comment

by:JustWorking
ID: 23677596
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
 
LVL 11

Author Comment

by:bsharath
ID: 23677640
Colum C & D i just get NULL for all
0
 
LVL 8

Expert Comment

by:JustWorking
ID: 23678103
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
 
LVL 11

Author Comment

by:bsharath
ID: 23678373
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
 
LVL 11

Author Comment

by:bsharath
ID: 23885859
Any views on this.
0
 
LVL 12

Expert Comment

by:zoofan
ID: 23931370
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
 
LVL 11

Author Comment

by:bsharath
ID: 23931746
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
 
LVL 12

Expert Comment

by:zoofan
ID: 23932138
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
 
LVL 11

Author Comment

by:bsharath
ID: 23932226
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
 
LVL 12

Expert Comment

by:zoofan
ID: 23932282
gimmie a few min.



zf
0
 
LVL 11

Author Comment

by:bsharath
ID: 23968720
Hi Zoofan any help....
just a reminder....
0
 
LVL 14

Expert Comment

by:yehudaha
ID: 24278795
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
 
LVL 11

Author Comment

by:bsharath
ID: 24279120
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
 
LVL 14

Expert Comment

by:yehudaha
ID: 24279228
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
 
LVL 11

Author Comment

by:bsharath
ID: 24279314
In colum "D" i get Offline. Can that also be removed
0
 
LVL 14

Expert Comment

by:yehudaha
ID: 24279399
does my above code works ?

colum was there it's not my addon

but you want to remove dead colum completly ?
0
 
LVL 11

Author Comment

by:bsharath
ID: 24279718
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
 
LVL 14

Accepted Solution

by:
yehudaha earned 2000 total points
ID: 24279813
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
 
LVL 11

Author Comment

by:bsharath
ID: 24279896
Thank U... :-)))
0
 
LVL 14

Expert Comment

by:yehudaha
ID: 24280113
no problem :-)
0

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Not long ago I saw a question in the VB Script forum that I thought would not take much time. You can read that question (Question ID  (http://www.experts-exchange.com/Programming/Languages/Visual_Basic/VB_Script/Q_28455246.html)28455246) Here (http…
A quick Powershell script I wrote to find old program installations and check versions of a specific file across the network.
The viewer will learn how to count occurrences of each item in an array.
In this fourth video of the Xpdf series, we discuss and demonstrate the PDFinfo utility, which retrieves the contents of a PDF's Info Dictionary, as well as some other information, including the page count. We show how to isolate the page count in a…
Suggested Courses
Course of the Month14 days, 11 hours left to enroll

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question