Search code examples
excelcmdippinghostname

When using Ping how can i export only the Hostname and IP to excel or csv

new here and hoping someone might be able to help me with this.

I have approximately 200 workstations that have had DHCP reservations completed, I would like to Ping them all and return only the Hostname, IP. If possible to get timed out or, failed also thats a plus but not a necessity.

I want to preface I am no coder. what I have done is create a Bat file to do the mass ping but i am not sure and have not been able to find a solution to export only the Hostname and IP.

I have looked around here and google and found a few VB scripts but I don't understand enough to manipulate them for this need.

I'd like to be able to see something in excel similar to this

testhostname | 192.168.1.1 | Failed/Timedout/pass

Thank you for any assistance you can provide here.

Solution

  • Here is a subroutine (:getData) and an example how to use it.

    There is no easy way to get all the desired data in one for loop (probably doable somehow, but not worth the trouble), so I solved that with two separate pings.

    localhost and google should return successful, hp is up but doesn't reply to ping and xxxxxxx.zzz does not exist (yet)

    @echo off
setlocal 
for %%h in (localhost www.google.com www.hp.com xxxxxxx.zzz) do call :getData %%h
goto :eof

:getData
set "ip=unresolved"
set "ms=unreachable"
for /f "tokens=2 delims=[]" %%a in ('ping -4 -n 1 %1') do set "ip=%%a"
for /f "tokens=3 delims==<" %%a in ('ping -4 -n 4 %1^|find "TTL="') do set "ms=%%a"
set "result=%1^|%ip%^|%ms%"
set "result=%result: TTL=%"
set "result=%result:||=|unknown|%
echo %result%

    Output:

    localhost|127.0.0.1|1ms
www.google.com|172.217.22.100|13ms
www.hp.com|15.73.200.24|unreachable
xxxxxxx.zzz|unresolved|unreachable

    So as you mentioned, that takes a while with 200 hosts. We can speed it up by running the different hosts simultaneously (that's the biggest change and also the biggest effect). I also reduced Waittime from standard 1000ms down to 200ms and the number of pings from 4 to 2 (I wouldn't reduce that any further because sometimes a package gets lost and cause a false negative). Also with that many of hosts, you will hit the max line length with this method. Better keep the hostnames in a file (one per line) (hostnames.txt):

    @echo off
setlocal 
if not "%~1" == "" goto :getData
for /f %%h in (hostnames.txt) do start /min "pinging %%~h" /min "%~f0" %%~h
echo waiting for result.csv to fill...
REM because some of the started processes are still running
timeout 5
goto :eof

:getData
set "ip=unresolved"
set "ms=unreachable"
for /f "tokens=2 delims=[]" %%a in ('ping -4 -n 1 -w 200 %~1') do set "ip=%%a"
for /f "tokens=3 delims==<" %%a in ('ping -4 -n 2 -w 200 %~1^|find "TTL="') do set "ms=%%a"
set "result=%~1^|%ip%^|%ms%"
set "result=%result: TTL=%"
echo %result%>>"%~dp0result.csv"
exit