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

Question

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.

to get help with your code, show you code. How do you ping 200 workstations? Do you have their names in a file and look for the IP's or do you ping all IP's and want to know, what hostnames come back? — Stephan, Jun 19 '19 at 17:54
@stephan As of Right now I have a batch file like this. ping CSDMCAM383xxx >> file.csv it returns the full Ping result like this. Pinging CSDMCAM5304xxx [10.203.90.xxx] with 32 bytes of data: Reply from 10.203.90.xxx: bytes=32 time=144ms TTL=120 Ping statistics for 10.203.90.xxx: Packets: Sent = 4, Received = 4, Lost = 0 (0% loss), So I know the Hostname and the IP that it should be I would like to just pull out CSDMCAM5304xxx | 10.203.90.xxx If it could also show me Failed/Timedout/pass That would be great. Thank you very much — Jason, Jun 19 '19 at 19:29

Stephan · Accepted Answer · 2019-06-20T10:59:09.163

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

So just to make sure I'm understanding would your third line look like this for me using the Hostnames I have. `for %%h in (localhost CSDMCAM5463xxx CSDMCAM5304xxx CSDMCAM5304xxx) do call :getData %%h goto :eof` Sorry still trying to figure out how to use the correct markdown. - How can I tell this to export to a .csv. where can I add >>file.csv — Jason, Jun 19 '19 at 20:54
For 200 hosts, with this method you will hit some hard limit (max line length). If you have them in a file (one per line): `for /f %%h in (file.txt) do call :getdata %%h`. For export: `echo %result% >> file.csv`. — Stephan, Jun 19 '19 at 21:24
@Stephen This is great! Thank you. One last comment. using this method it runs very slow took about 30 sec-1 min for 1 hostname. Going on 13 min right now for 7 out of 13 on my test. - Is there a way to speed it up? — Jason, Jun 19 '19 at 21:49
@Jason it's nearly midnight at my corner of the world. Let me sleep a bit and think about it tomorrow `:O` — Stephan, Jun 19 '19 at 21:51
@stephen Thank you, No rush, Appreciate you helping out with this — Jason, Jun 19 '19 at 21:56
thank you, I will give this a shot tomorrow when I get in. Appreciate you taking the time to put this together. — Jason, Jun 21 '19 at 04:16

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

1 Answers1