Home » Networking Tools » Interpreting ping test output — retroactively adding timestamps

I’d like to share a tip for interpreting ping output in a spreadsheet.  Let’s say you ran a ping test for 24 hours, one ping per second, and found that the summary stats indicate some packet loss.  You have 86400+ lines of ping output to analyse.  How do you know if the packet loss was confined to a particular period in time or if it was distributed throughout the test period?  Well, here’s one way to do it.

This walk-through uses ping output obtained from a Juniper router shell using the following command.  For the purposes of obtaining a timestamp, it’s crucial to know that these pings are being sent once per second.  The method can be adapted to different intervals, however:

nohup ping -i 1 -s 2048 192.168.200.2 > pingtest2048.txt&

Step 1:  Open a new Excel/OOo/whatever spreadsheet and paste the contents of pingtest2048.txt into cell A1.

pingexcel1

Step 2:  Clean up the output by deleting any rows which are NOT like A2, A3, A4, etc.  We only need the ping response output, not the header or ping statistics summary at the end.

Step 3:  Now, select column A (not just cell A1).  Select ‘Data’ in the ribbon and select ‘Text to Columns’.

pingexcel2

Step 4:  Select ‘Delimited’ … Click Next …

pingexcel3

Step 5:  Select ‘Space’ and ‘Other’ (with value of “=”) … Click ‘Finish’

pingexcel4

Step 6:  (Optional)  Additional clean-up can be performed by pressing and holding ‘Ctrl’ key and clicking on unnecessary columns.  In this case I am deleting A-D, G-K to make the output more readable.  Right-click and click ‘Delete’

pingexcel5

Step 7:  You’ll be left with just two columns, A and B.  We need to fill C1 with ‘1’, C2 with ‘2’ and then fill down to the bottom of the sheet.  To do this, select C1 and C2, then double-click the square in the bottom-right corner of the selection rectangle.

pingexcel0

Step 8:  In cell D1, enter the formula shown below and again use fill-down.  This is just the difference between icmp_seq response and expected response.  Therefore, if an ICMP echo request is answered, the value in Dn will be 0.  On the first lost packet, the value in this column becomes -1, on the ith lost packet, the cumulative difference becomes –i.  However, for our purposes we wish to reset the difference counter after each ‘flap’ event.

pingexcela

Step 9:  Column A will become our time-stamp.  Insert a new column and fill in cells 1 and 2 before using fill-down.  If you know the exact start time of the ping test, you can put this against icmp_seq 1, otherwise round to the nearest minute.

pingexcel7

Step 10:  Now we can interpret the results.  In this example we can see that icmp_seq 879 was sent and received, however icmp_seq 880 was sent but no reply was received.  This is indicated by the difference of 1.

pingexcel8

Step 11:  It’s important to note that 881 was sent and received, therefore we should reset the fill-down count in column D.  We can then spot any further packet drops as offset from 0 rather than 1.  However, if you want to view the drops in step-change offset, do not reset the count in column D.  In this instance I will reset it.

pingexcel9

Note also how the row numbers on the left match the icmp_seq / packet numbers.  It’s clear to see that packet 880 was dropped if you highlight the row in yellow.  In this screenshot I’ve made a mistake:  I didn’t correct the values in column A.  Reply 880 was dropped at 08:19:39 and Reply 881 was received at 08:19:40.  If you understand the mistake, you understand the purpose of this guide! ;)

Thanks to Temoor for showing me this trick.  No thanks to Juniper’s ping implementation which doesn’t allow the option of outputting a time-stamp with the output.  Whilst there are ways to shell script around this, I think using Excel is more portable.  What if the ping output was generated by another NOC?  You can’t retroactively run your ping script! ;)