Traceroute on a Map using Pingdom API, MaxMind, and D3

Today, I was looking at the traceroute module for NodeJS and was thinking about combining the results of a traceroute with a geolocation service that translates the IP addresses into Geo coordinates that can be placed on a world map built with D3. MaxMind Geolocation database seemed like a good idea and I was trying to figure out how to do that on Redis for nodeJS. Then I stumbled on GeoIP which seems to make it easier to use MaxMind with Node. Actually, Philip Tellis created geoip-lite which is a native NodeJS API for the GeoLite data from MaxMind. MapTail, for example, uses geoip-lite to display visitors on a realtime map.

But, I do not have a public server with NodeJS and when I noticed that Pingdom tools has a traceroute method in their API, I decided to implement this project in PHP, using curl to retrieve traceroute information from the Pingdom API, converting IP to location using MaxMind on MySQL, and placing the geo coordinates on a D3.js map. Let’s get started.

Error Detection in PHP cURL

Signing up for a free API key with Pingdom was easy. They have a RESTful API which is nice but their documentation is really lacking in examples. API examples, a sandbox or playground, really help in understanding APIs more quickly. I have only found one example of using traceroute outside of the Pingdom website and it was wrong and a waste of time. But, enough of the complaints, the documentation is easy enough to read and the API call is really simple. I setup a curl call with error checking and put my API key in the header:

This resulted in an error however, which I did catch, and it said: “cURL Error: SSL certificate problem, verify that the CA cert is OK. Details: error:14090086:SSL routines:SSL3_GET_SERVER_CERTIFICATE:certificate verify failed.”

You can read more about this error here but the easy solution was to place curl_setopt($ch, CURLOPT_SSL_VERIFYPEER, false); in my code and another try resulted in a 401 Unauthorized, user credentials missing error. I’m just being lazy in reading the documentation.

I looked again and realized that I need to pass my username and password in basic authorization header. I added curl_setopt($ch, CURLOPT_USERPWD, “MY-EMAIL:PASSWORD”); to the code above and finally received the appropriate response. The last part is just parsing the JSON string response. Here is the final example code of using the Traceroute Method with Pingdom API:

This type of example should be in the Pingdom API docs. Also, I wanted to mention hurl because it is an awesome tool for demoing and debugging APIs. I added some code that let’s you pass in the website for your traceroute API call and parsed the response with a little regex so only IPs are returned back in a string separated by commas.

Setting up MaxMind in MySQL

It looks like MaxMind is one of the best services in Geo location. I pulled the data in CSV format from MaxMind GeoLite, created a database named “maxmind”, and loaded the data into mysql using these commands in command-line sql queries:

After that, you can run MySQL queries like this:

SELECT locid FROM blocks
WHERE INET_ATON(“174.36.207.186″)
BETWEEN startIPNum AND endIPNum
LIMIT 1;

which result in an location id (12120 for that IP) that you can then use to query for location specifics:

SELECT country, region, city, postalCode, areaCode FROM location WHERE locid = 12120;

I do want to point out that there are other non-database ways of using MaxMind but I am choosing on leveraging a database for this example. Let’s consider some database optimizations for these queries in bold above. I have looked at using polygons for more efficiency, using only one key in the query, but after running some tests I have found that this query gives me fastest results:

SELECT city FROM location WHERE locid = ( SELECT locid FROM blocks WHERE INET_ATON(’66.129.84.11′) BETWEEN startIPNum AND endIPNum);

My knowledge on MySQL optimizations is rather limited so if anyone can provide more explanation here, it would be very much appreciated. So now, how do we connect the PHP MySql communication with Pingdom API calls and D3 World Map in the front-end? The HTML will ask for a host and draw a World Map with D3 initially. When the user enters a host, we will send that information via Ajax to the traceroute PHP file which will return IP results from the Pingdom API call. If there are any errors we can provide that information back to the end user. If we get some IPs, another Ajax call will fire to a PHP file that connects to the MaxMind database and runs queries to return Geo Coordinates for each IP. Let’s write this file:

As you can see, I decided to pass all the IPs via POST instead of doing each IP individually. I connect to MySQL, select the MaxMind database, and loop through the array of IPs in queries that should translate IPs to Geo Coordinates. Then, I return a multidimensional array with Geo Locations in the response. If some IP does not resolve to a location, it gets skipped. Finally, let’s put together the front end.

Plotting Geo Coordinates on a D3 World Map

First, I build a regular World Map, similar to these examples but without the tooltips. I added a form with a text field that lets the end user provide their domain to make a traceroute. I also add loader element to display information about what is processing, which is really important since the traceroute API call takes about 20-30 seconds to return. When the end user provides a host name and presses go, the JavaScript makes an Ajax request to the traceroute php script above and initiates the Pingdom Tools API call to retrieve the IP addresses. When the API call returns it gets forwarded to a callback function that makes another Ajax request to the MaxMind php script above which translates those IP addresses to Geo Coordinates. I have used the d3 XHR methods described in the D3 documentation and this stackoverflow question.

Next, those IP addresses are sent to a drawit function which plots all the Geo locations on the world map and also shows the start and end location. Finally, I loop through the Geo coordinates again and draw lines in between each point to show how the request travelled from beginning to end. For the purpose of the demo, the PHP scripts are returning a predefined set of IPs and a predefined set of geo coordinates. This is mostly because I don’t have the MaxMind database on my public server, however, you should be able to recreate this example on your machine with the scripts provided above and using the page source of the demo.

Check out the demo of a traceroute visualization.