Exporting Data from WebSitePulse's API to Google Drive

June 7th, 2012
Posted in Monitoring, Tools, Tech

What’s the fun in having a fully functioning API if you can’t play around with it, right? Sometimes data is needed in different forms to serve a specific purpose. APIs are great when the existing interface and available functions are not enough. Getting data straight to spreadsheets is great for custom reports and graphics. So, let’s learn how to do this.

Today we are going to focus on XML and export data with the HTTP API. Before we start, let me remind you that if you want to replicate and build upon any of what I’m writing below, you will need to register for any of our services – paid, trial or free. They will all give you access to our API and what you have in your account.

Currently the API supports 7 methods of exporting data – GetStatus, GetUptime, GetHistoryUpdateTargetStatus, GetDailyLog, AccountStatus and AccountSummary. The method names are pretty much self-explanatory. For the purpose of our article, today we are going to focus on GetUptime, but the same approach is applicable to all other methods.

A peek under the hood

If you like to know what the average response time for a particular month is, the GetUptime method will do the job.  Along with the uptime information, this method will also give you the number of checks, fails and estimated downtime (in seconds).

With GetUptime we can request a specific target (website or server that you monitor) and target location. We also need to specify the start and end date for the period we require the information for (the necessary minimum is one month). If you like to get information for June, 2012, your corresponding value would be 201206. If you are at least somewhat familiar with the API, you can skip down to Importing to Google Drive.

To export information from your WSP account you need to send the proper query. Each HTTP/GET request for the retrieval of uptime statistics is basically a URL comprising of several parts:

  • http://api.websitepulse.com/textserver.php - First is the base of the URL or resource we need to send the request to.
  • username=yourusername – then we add the username you would normally use to login to websitepulse.com
  • key=b555tyyy72092fc77pxxxxxxxxxx – right after the username you need to place your own API key, so you can access your account.
  • target=15555 – specifying the exact target you need the information for is the next part of the URL. You can get the target’s unique ID here, once you are logged in and have the API at hand. Here, using “all” instead of an ID, would return all targets you have in your account.
  • location=all – this way you request the location you need data from - “all” specifies that you want to extract information about each location you are monitoring from. The IDs of all locations are available here.
  • method=GetUptime – this parameter enables you to request different types of data. For example, with GetUptime you can select a date range (in months) and get statistics for uptime, response time, number of fails and estimated downtime. The different methods would provide you with different levels of detail.
  • format=xml – depending on how you want to use the data you can get it in 4 different formats – txt, csv1 (semi-colon separated), csv2 (comma separated) and xml.
  • startdate=201205 – choose a month to start with. 201205 would include the information for May, 2012
  • enddate=201206 – choose where the date range should end. Selecting the current month would include all available data for it, up to the current date.

Importing to Google Drive

The base URL, along with those 8 parameters is all you need to form a proper query. OK, enough with the boring part. Let’s build a query and fire up Google Docs :-) . This is how the query should look.

http://api.websitepulse.com/textserver.php?username=yourusername&
key=b555tyyy72092fc77pxxxxxxxxxx&
target=15555&location=22&startdate=201205&enddate=201206&
method=GetUptime&format=xml

When you paste it in your browser of choice, you will get a well-formed XML file in return. Here is a sample of the output. This is the output by Google Chrome. Since we’ll be importing the information to Google Drive, it seems like a nice fit.

We have decided to blur the label, but in your case this would be the name you gave to your target. New York is the location corresponding to ID 22, the one we entered in the URL. On this target we ran exactly 2540 checks from New York and in that time we got zero errors, so good for us!  Then we get the response time values and the uptime percentage. For downtime we got 0 and that’s a good thing.

Uptime statistics end up in monthly reports alongside many other performance metrics. Some of you might need to incorporate this data. Instead of generating multiple reports and finding yourself in copy/paste frenzy, you can use ImportXML in Google Spreadsheets. From there you can copy the data to MS Excel with a single move.

First we name the fields we want to extract. Because we use ImportXML with Xpath, we can get only specific parts of the response, thus making it easier to read.

Importing the data is really quite easy. To export the data we need, we simply need to place the URL and instructions together. Below you will find a sample. Paste that into any cell and you would get the uptime percentage for May and June.
=importxml("http://api.websitepulse.com/textserver.php?username=yourusername&key=b555tyyy72092fc77pxxxxxxxxxx&
target=15555&location=22&startdate=201205&enddate=201206&
method=GetUptime&format=xml",/Result/item/uptimepercent)

The part in red is our Xpath. Does it look familiar to you? It follows the hierarchy of the XML files. We simply tell Google Spreadsheets to take only the value of “uptimepercent” by giving the full path to it.

Now, in order to get the data for the remaining fields, we only need to change the item to a different one. Let’s scroll up and see what other values we had. We can click under the first label, Target, and paste following query, where we have replaced the item for uptime with the one for label. To save some time, we went ahead and entered all values.

=importxml("http://api.websitepulse.com/textserver.php?username=yourusername&key=b555tyyy72092fc77pxxxxxxxxxx&
target=15555&location=22&startdate=201205&
enddate=201206&method=GetUptime&format=xml ","/Result/item/label")

With this approach, you can simply check the item you need to export, and replace it in the example. If you are curious on how to extract the path, you can use Xpath Helper, straight out of Google Chrome. Once installed, the plugin will load automatically. You would simply need to click in the item you wish to get the path for and it will appear.

Snap it next to the URL and there you have it. Behold a scalable method to automatically grab data from our API, via HTTP/GET, and use it to your liking. The spreadsheet used in this post is available for download http://goo.gl/G2UwT . Make a copy and use it as you will. Don’t forget to fill in your username and key, otherwise the function won’t work. To make things a bit easier, we have specified two cells to enter them in.

A lot more can be done with the API with just a bit of tinkering. All available request methods and available calls are available in the official WebSitePulse API Specification. The data can be used for MS Excel charts, integrated to your reporting systems, or become part of a new application. Give it a spin by registering for a free monitoring account. The API is available with all trial accounts. We are curious to see what you make of it!

About Victoria Pal

Doesn't like queuing (particularly at Wimbledon). Likes travelling, tennis and reading. Loves working as a Project Manager at WebSitePulse.

comments powered by Disqus