Solved

Traceroute Help

Posted on 2006-11-07
10
646 Views
Last Modified: 2010-04-10
I am using traceroute to count hops between servers which it does just fine, what i am looking to do is write a script to output the last line/hop of the traceroute output to an excel file, is ther an easy way to do this?

Tracing route to AT1006.corpnet1.com

over a maximum of 30 hops:[xxx.xxx.xxx.xxx]

  1    <1 ms    <1 ms    <1 ms  xxx.xxx.xxx.xxx

  2    <1 ms    <1 ms    <1 ms  xxx.xxx.xxx.xxx

  3    <1 ms    <1 ms    <1 ms  xxx.xxx.xxx.xxx

  4     1 ms     1 ms    <1 ms  xxx.xxx.xxx.xxx

  5    <1 ms    <1 ms    <1 ms  servername [xxx.xxx.xxx.xxx]

  6    38 ms    41 ms    39 ms  servername [xxx.xxx.xxx.xxx]  e.g. this is the only line that i want to export to excel.

There are 40 servers and i can traceroute them all in a batch file but all i need is the last line exported.


Thanks and regards.

dcreedon

Alan
0
Comment
Question by:dcreedon
10 Comments
 
LVL 14

Expert Comment

by:inbarasan
ID: 17888374
Dear dcreedon,
You can write a batch file with Tracert command to the servers. Execute the Batch file and pipe it to a file with csv ext. Try to open the file in Excel it will work

test.bat>test.csv

Test.bat should contain the tracert commands.

Hope it helps you

Cheers!
0
 
LVL 16

Expert Comment

by:btassure
ID: 17888598
I would agree, dump that into excel and from there you can start setting up the data links. If the routes are generally going to be different lengths it might be worth nesting some if functions.
EG:

a1 traceip1 1.1.1.1
a2 traceip2 43.1.1.1.
a3 traceip3 2.2.24.1
a4 traceip4 2.2.45.1
a5
a6 =if(a4>0,a4,if(a3>0,a3,if(a2>0,a2,if(a1>0,a1,0))))

This will display the last know IP even if they are different lengths every time.
0
 
LVL 43

Expert Comment

by:Steve Knight
ID: 17889067
You could write a batch file as follows.  This takes a list of addresses - either ip or hostname, whatever works on the tracert line, and runs a tracert then picks out the last line that works into a CSV file.  Is that what you want?

Steve

@echo off
set logfile=c:\logfile.csv
set addresses=c:\addresses.txt
IF NOT EXIST %addresses% echo %addresses file not found.  This should have each IP or hostname, one per line & goto end

echo Computer,hops,time 1,time 2,time 3, IP Address > %logfile%

for /f "tokens=1" %%y in (%addresses%) do call :process %%y
goto end

:process

echo Tracert to %1
set num= & set ms1= & set ms2= & set ms3= & set IP=
for /f "tokens=1,2,4,6,8 delims= " %%a in ('tracert -h 20 -d %1 ^| find /i "ms"') do set num=%%a & set ms1=%%b & set ms2=%%c & set ms3=%%d & set IP=%%e

echo %1,%num%,^%ms1%,^%ms2%,^%ms3%,%ip%
echo %1,%num%,^%ms1%,^%ms2%,^%ms3%,%ip% >> %logfile%

:end

0
 
LVL 24

Expert Comment

by:SunBow
ID: 17889871
Simpler and quicker I think overall is to simply copy the 40 to a new file. This can be done directly off the dos window, or by redirecting it over to a text file, such as log.txt. Once you have that then you can decide on whether or not to invest in Excel

If you then want his to be a daily job, then my vote is to place everything that is output into a single flle (trace.log). Process the log with a language that cna identify strings, to pull out the 40 or so lines desired by parsing. Do not count on results always being on same line number, you already list twice the hops that are needed.
0
VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

 

Author Comment

by:dcreedon
ID: 17890383
Hi Guys,

Thanks for all the help so far, but maybe i didn't explain myself properly before. I have a batch file thats tracerts 40 servers all at once. The ouptut gets sent to a text file and i can import that to excel and manually take the last lines out of each servers traceroute and copy into another excel sheet. Very tedious is it not. What i want to do is have a script or something that will pull the last line out of each servers traceroute (Either on the text file or excel) and put it into an excel file. This would save a lot of time. I know this can be difficult as each trace route is different on the text file, different hops, TTLs IP's etc but if this is possible any help would be greatly apprcieated.

Thanks Guys.
0
 
LVL 43

Expert Comment

by:Steve Knight
ID: 17890434
Hmm, just checking. Did my post go through?  Looks like it :-)

Have you tried the batch file I posted above.  That is what it does.  It takes the list of servers from the file addresses.txt, one entered per line, runs a tracert then collects that data for you into a CSV...

It does do it one after another... is that an issue?

Steve
0
 

Author Comment

by:dcreedon
ID: 17890846
Hi Steve,

I ran the script and and all it did was create the excel file with no content. Can't see why it's doing that.

Thanks.
0
 
LVL 43

Expert Comment

by:Steve Knight
ID: 17891106
OK, try removing the @echo off line and re-run it to see any errors.  I tried it on NT4 and Windows 2003.  On the screen it should come back with

E:\scripts\>trace

Tracert to Server1.domain.internal
server1.domain.internal,10 ,8 ,8 ,8 ,10.25.10.35
Tracert to server2.domain.internal
server2.domain.internal,10 ,6 ,6 ,6 ,10.25.10.36

And the two CSV lile lines go to the screend and CSV file

In the text file addresses.txt add the IP or servername or fully qualified.

It currently sets a maximum hop of 30 which you can adjust on the tracert line and turns off name resolution with the -d option to speed it up.

Correct me if I am wrong but what I am trying to produce for you IS what you are asking for ultimately isn't it before I put any more time into it?

Steve
0
 
LVL 43

Accepted Solution

by:
Steve Knight earned 500 total points
ID: 18042374
I wrote a custom batch file to carry out what was asked for and would appreciate at least a decent split in points for that, work would have continued if the asker continued with it...
0

Featured Post

New My Cloud Pro Series - organize everything!

With space to keep virtually everything, the My Cloud Pro Series offers your team the network storage to edit, save and share production files from anywhere with an internet connection. Compatible with both Mac and PC, you're able to protect your content regardless of OS.

Join & Write a Comment

Suggested Solutions

Lets look at the default installation and configuration of FreeProxy 4.10 REQUIREMENTS 1. FreeProxy 4.10 Application - Can be downloaded here (http://www.handcraftedsoftware.org/index.php?page=download) 2. Ensure that you disable the windows fi…
Data center, now-a-days, is referred as the home of all the advanced technologies. In-fact, most of the businesses are now establishing their entire organizational structure around the IT capabilities.
Viewers will learn how to connect to a wireless network using the network security key. They will also learn how to access the IP address and DNS server for connections that must be done manually. After setting up a router, find the network security…
After creating this article (http://www.experts-exchange.com/articles/23699/Setup-Mikrotik-routers-with-OSPF.html), I decided to make a video (no audio) to show you how to configure the routers and run some trace routes and pings between the 7 sites…

706 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now