Traceroute identification

danicam001

New Member
Joined
Dec 10, 2023
Messages
3
Office Version
  1. 365
Platform
  1. Windows
Hello Dear team

Hi everyone, this is my first post! I'm working on figuring out the hops in an internal traceroute using a list of known IPs. I'm running a traceroute on my Windows computer, saving it to a file, and then opening it in Excel.
I want to find the hops I already know. For example, in this traceroute, some hops are part of my network, so I can recognize them easily.

Normal Traceroute
Tracing route to 10.142.111.1 over a maximum of 8 hops

1 5 ms * 7 ms 10.111.111.10
2 5 ms 5 ms 5 ms 10.111.113.20
3 5 ms 5 ms 6 ms 192.16.100.103
4 8 ms 5 ms 4 ms 192.17.143.212
5 * * * Request timed out.
6 11 ms 22 ms 9 ms 192.142.120.1

Trace complete.

What I want to achieve is to have a list of all IPs that I know, either in another sheet or the same one. For those IPs that I recognize, place the corresponding names on the right side of the tracert, like this:

List of known Ips:
10.111.113.20 - router123
192.16.100.103 - router456
192.17.143.212 - router777
192.142.120.1 - Core1
etc

The final result I would like to be like this:
Tracing route to 10.142.111.1 over a maximum of 8 hops

Column A Column B
1 5 ms * 7 ms 10.111.111.10
2 5 ms 5 ms 5 ms 10.111.113.20 router123
3 5 ms 5 ms 6 ms 192.16.100.103 router456
4 8 ms 5 ms 4 ms 192.17.143.212 router777
5 * * * Request timed out.
6 11 ms 22 ms 9 ms 192.142.120.1 Core1

Trace complete.

Thank you, Dan
 

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off
Just improving the formatting this is the expected result:

ColumnAColumn B
Tracing route to 10.142.111.1 over a maximum of 8 hops
1 5 ms * 7 ms 10.111.111.10router123
2 5 ms 5 ms 5 ms 10.111.113.20router456
3 5 ms 5 ms 6 ms 192.16.100.103router777
4 8 ms 5 ms 4 ms 192.17.143.212
5 * * * Request timed out.
6 11 ms 22 ms 9 ms 192.142.120.1Core1
 
Upvote 0
Welcome to the MrExcel board!

Would something like this help?

23 12 11.xlsm
ABCDE
1Normal TracerouteList of known Ips:
2Tracing route to 10.142.111.1 over a maximum of 8 hops10.111.113.20router123
3192.16.100.103router456
41 5 ms * 7 ms 10.111.111.10 192.17.143.212router777
52 5 ms 5 ms 5 ms 10.111.113.20router123192.142.120.1Core1
63 5 ms 5 ms 6 ms 192.16.100.103router456
74 8 ms 5 ms 4 ms 192.17.143.212router777
85 * * * Request timed out. 
96 11 ms 22 ms 9 ms 192.142.120.1Core1
Traceroute
Cell Formulas
RangeFormula
B4:B9B4=IFNA(VLOOKUP(TEXTAFTER(A4," ",-1),D$2:E$5,2,0),"")
 
Upvote 0
Thank you very much. It worked like a charm!!
I was also trying to do it via vba.

I need to add a second for loop for the "list of known IPs" and control

VBA Code:
Sub find_in_traceroute()
Last = Cells(Rows.Count, "b").End(xlUp).Row
For i = 1 To 500
    If (Cells(i, "b").Value) Like "*172.17.10.10*" Then
        Cells(i, "c").Value = "ROUTER-ABC"
    End If
Next i
End Sub
 
Last edited by a moderator:
Upvote 0
When posting vba code in the forum, please use the available code tags. It makes your code much easier to read/debug & copy. My signature block below has more details. I have added the tags for you this time. 😊

Thank you very much. It worked like a charm!!
Good news! :)

I was also trying to do it via vba.
In future, please include such information in your original post. ;)

Your code above calculates a value for 'Last' but then does not use that value anywhere. Should it?

Where is your list of known IPs and the corresponding names?
 
Upvote 0

Forum statistics

Threads
1,223,911
Messages
6,175,337
Members
452,636
Latest member
laura12345

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top