Stephen Akins - Blog

Want to know when Google is adding your area to Street Level View?

2011-11-16T16:39:00.000-08:00

Google doesn't tell many people when they are driving around in their cars, photographing an area for Street Level View (I understand Google notifies Women's Shelters and other organizations that are sensitive about exterior photographs of their buildings). There are several obvious reasons for not being too public about their mapping intentions, of course. Most importantly, I imagine, is that Google wants to keep their data from being "tampered" with by people who would make some special display specifically for the mapping vehicle (e.g. a "Hi Mom!" banner).

I, however, really want to know anyway. I live on a small island that relies heavily on tourism. Our island has many artists and artisans who work out of studios on their own properties. I would like to have these artists have displays of their work on the ends of their driveways when Google conducts a street level view on our island (our island hasn't been covered yet, but I think it may be covered in the future). I think it would be good for the locals, and for the tourists who look at our island online.

As I said, google doesn't broadcast where they intend to cover next; but they DO broadcast where they are currently. Google has a special site that you can visit to see the current location of their vehicles, categorized by country:

http://maps.google.com/help/maps/streetview/learn/where-is-street-view.html

As I said, the chart on this page shows the current location and only seems to list the city or general area they are currently mapping. However, I think it's a start and below I'll show you how you can use this data to get an alert when Google starts mapping a new area in your country.

The method I'll describe here is a very simple one. The country data on the page above is populated through an XML feed. I decided to import the data into a Google Docs spreadsheet, and make the spreadsheet notify me via email if the cell that contains the data for my country changes. The steps I took are as follows:

Create a spreadsheet in Google Docs
In the first cell on the default worksheet (A1) insert the following function:
=ImportFeed("http://spreadsheets.google.com/feeds/list/0AjZ9lY-SjtYacnNVdGhsckJrM3k5X1hFd3BIWlhWcFE/oda/public/basic")
Select Tools > Notification Rules and add a new rule
Click the checkbox next to "Any of these cells are changed: ". In the cell range box, put the name of the cell that contains the data for your country (for my country, Canada, the cell was C7)
Click the checkbox next to "Email - Right Away" under "Notify Me With..."
Save your spreadsheet and exit!

The spreadsheet doesn't poll the source every second, but you should get a notification shortly after the data changes (certainly within the hour). Again, this isn't exactly "notice", but hopefully it will give you some warning.

If there is a lot of activity in your country (and you're getting too many notifications) you can use the FIND function to locate your area/city name within your country data and then base your notification on the result.

Accessballot

2011-07-19T11:14:00.000-07:00

I've completed the initial version of a English US EAC compliant paper ballot generating API. It takes XML candidate/issue data and uses it to generate a PDF of a ballot that is 100% compliant with the US EAC ballot design standards.

The application is Opensource and you can find it on Google Code at the following address:
http://code.google.com/p/accessballot/

You can also use the hosted version on the Accessvote.com (which is free of charge). The following is a sample ballot generated using the free API:
http://accessvote.com/api/us_eac/?s=http://accessvote.com/sample_ballots/us_eac-sample.xml.

If you would like more information on how to use it/integrate it into your own applications, you can view the project wiki at the following location:
http://code.google.com/p/accessballot/w/list.

Making DIVs, using the CSS "Float Left" property, all have uniform heights; automatically adjusting to match the tallest DIV in the row

2011-01-26T00:09:00.000-08:00

Update: I modified the code so it would work with the onresize() event

Would you believe that was the best title I could come up with? Honestly, if you can think of a better one shoot me a line - I want to hear from you.

Okay, I'm a big fan of Fluid Design. I love to design websites/webapps that flow into the space available, from giant screened desktops to mobile phones, and always look like they were designed with exactly that screen size in mind.

One thing that CSS-P brought to web design that didn't exist back when we were using invisible nested tables to do webpage layout is the ability to have rows with a dynamic number of "columns". So, for instance, if you are showing rows of thumbnails, you can have more thumbnails on a row if the browser window is wide, and fewer if the window is narrower.

The method for doing this is to cause items to "Float Left". Usually this means putting your content (say a thumbnail and label) into a DIV; and giving the DIV the CSS property of:

div.column
{
   float:left;
}

If the above were added to a page's stylesheet, the DIVs with the class "column" assigned to them would stack to the right of the item before it on the page, and if there are more than one item, each will stack to the right of the previous DIV until there is no more room on that line, and then the next will appear below that row, starting a new one.

If the DIVs do not share the same height, each row will have their tops all lined up, but will have bottoms extend downwards as much as they need to to accommodate the content they contain. And the next row, which also has all the tops lined up, will appear just below the bottom of the DIV, from the previous row, that was "tallest". Like this:

(So above the second row begins below the "tallest" item in the first row, which is c)

This isn't bad, but many designers would rather have some uniformity to the DIV sizes and have, say, the container backgrounds and borders fill in the spaces below a, b, and d. We can do that, of course, by giving each container a specified height, instead of letting the content drive the height, but then we would have to make all the containers as tall as they are likely ever to get which isn't prefect either.

Ideally, I think, it would be best to have all of the DIVs in a row be all be the same height, but only be as tall as is actually needed to accommodate the tallest DIV in the row. Like this:

(in the above example, the DIVs in the first row are as tall as the tallest DIV in the row, which is c, and all the DIVs in the second row are as tall as f, which is the tallest DIV in that row)

Well, with a bit of jQuery you can have your DIVs all adjust their heights so they are uniform across the entire row. You will have to add jQuery to your page (see How jQuery Works) and add the following Javascript to your page:

var currentTallest = 0;
var currentRowStart = 0;
var rowDivs = new Array();

function setConformingHeight(el, newHeight) {
 // set the height to something new, but remember the original height in case things change
 el.data("originalHeight", (el.data("originalHeight") == undefined) ? (el.height()) : (el.data("originalHeight")));
 el.height(newHeight);
}

function getOriginalHeight(el) {
 // if the height has changed, send the originalHeight
 return (el.data("originalHeight") == undefined) ? (el.height()) : (el.data("originalHeight"));
}

function columnConform() {

 // find the tallest DIV in the row, and set the heights of all of the DIVs to match it.
 $('div.column').each(function(index) {

  if(currentRowStart != $(this).position().top) {

   // we just came to a new row.  Set all the heights on the completed row
   for(currentDiv = 0 ; currentDiv < rowDivs.length ; currentDiv++) setConformingHeight(rowDivs[currentDiv], currentTallest);

   // set the variables for the new row
   rowDivs.length = 0; // empty the array
   currentRowStart = $(this).position().top;
   currentTallest = getOriginalHeight($(this));
   rowDivs.push($(this));

  } else {

   // another div on the current row.  Add it to the list and check if it's taller
   rowDivs.push($(this));
   currentTallest = (currentTallest < getOriginalHeight($(this))) ? (getOriginalHeight($(this))) : (currentTallest);

  }
  // do the last row
  for(currentDiv = 0 ; currentDiv < rowDivs.length ; currentDiv++) setConformingHeight(rowDivs[currentDiv], currentTallest);

 });

}


$(window).resize(function() {
 columnConform();
});

$(document).ready(function() {
 columnConform();
});

(the above code assumes you have assigned the CSS class called "column" to each of the DIVs that are "Floating Left")

If you can read Javascript/jQuery, you will find the code comments will sufficiently explain how it works; but in a nutshell: The script goes through each DIV, determining which are on the same row by comparing the X value of the top of each container. It keeps track of which is the tallest, and then sets the height for each DIV in the row based on that value.

Link Tracking using jQuery and the Google Analytics Asynchronous Tracking Code

2010-12-09T12:27:00.000-08:00

The Google Analytics tracking code is triggered when pages that include the code are called. But what about when users request PDFs and other documents that aren't web pages or click on links to external web pages on other websites? How do we track these events also?

Here is some jQuery you can add to your pages that will allow you to track when people click on these links. You will have to add the Google Analytics Asynchronous Tracking code (found here: http://code.google.com/apis/analytics/docs/tracking/asyncTracking.html) and add jQuery to your page (see here: http://docs.jquery.com/How_jQuery_Works) to use this code:

$(document).ready(function(){

 $('a').click(function(){

  href = ($(this).attr('href') == undefined) ? ('') : ($(this).attr('href'));
  href_lower = href.toLowerCase();
  
  if(href_lower.substr(-3) == "pdf" || href_lower.substr(-3) == "xls" || href_lower.substr(-3) == "doc") {
   _gaq.push(['_trackEvent', 'document', 'download', href_lower.substr(-3), $(this).text()]);
   _gaq.push(['_trackPageview', href]);
  }
 
  if(href_lower.substr(0, 4).toLowerCase() == "http") {
   _gaq.push(['_trackEvent', 'external_link', 'open', 'href', $(this).text()]);
   _gaq.push(['_trackPageview', href]);
  }
  
  if ($(this).attr('target') != undefined && $(this).attr('target').toLowerCase() != '_blank' && href_lower.substr(0,10) != "javascript") {
   setTimeout(function() { location.href = href; }, 200);
   return false;
  }

 });

});

So, the example above will track clicks on internal links opening files with the extensions pdf, xls, and doc and will categorize them in your Google Analytics event tracking reports as "documents". It will also track outgoing links (links with URLs beginning with "http") and categorize them as "external_links".

If you need more categories, add more if statements. In the method above I create one click event that is attached to every link on the page, rather than just attaching specific events to the links that require them. If a link satisfies more than one criteria (e.g. it's a link both to a PDF, and it's on an external website) then both events will be created. In the end I thought this approach was probably more customizable and efficient overall, especially if there are several event categories.

The setTimeout bit is to allow links that replace the page content (i.e. regular links) a moment before calling the page; so that the GA function has time to execute before opening the page. Be careful when using plugins like Fancybox; add links to such plugins to the exception list in the condition wrapped around the setTimeout function as these functions remove the href attribute and the combination of that and the setTimeout can cause unpredictable behaviour.

You may want to track events for clicks made by specific links like the nav or featured content. You can achieve this using a similar method:

$("#main_menu a").mouseup(function(){
if($(this).attr('href').toLowerCase() != "javascript:;" && $(this).attr('href') != "#") {
_gaq.push(['_trackEvent', 'main_menu', 'open', $(this).attr('href')]);
}
});

Change the ID in the yellow to whatever you call the container that your menu is kept in. I added the check for JavaScript:; and # just in case there are links that don't open pages (e.g. links that reveal further options).

Leave comments if you have other suggestions for mixing jQuery and GA for better tracking.

Google Nexus S Fun

2010-12-08T09:53:00.000-08:00

I happened to catch the second clue in the Google Nexus (@googlenexus) Nexus S contest about 20 minutes or so after they posted it. I didn't get the first puzzle; I thought I did but I was wrong. This one I was on the right track right away. The clue was:

Puzzle Challenge #2: There is more than meets the eye at http://goo.gl/MKoc8. You'll find the answer in a tweet.

So the FIRST thing I did when I went to the page was look at the source code. At the very bottom of the page I found this:

.
                               I                I                               
                                I      ++      I                                
                                 IIIIIIIIIIIIII                                 
                               IIIIIIIIIIIIIIIIII                               
                             IIIIIIIIIIIIIIIIIIIIII                             
                            IIII   IIIIIIIIII  IIIII                            
                           IIIIIIIIIIIIIIIIIIIIIIIIII                           
                          IIIIIIIIIIIIIIIIIIIIIIIIIII                           
                          IIIIIIIIIIIIIIIIIIIIIIIIIIII                          
                          IIIIIIIIIIIIIIIIIIIIIIIIIIII                          
                    IIII  IIIIIIIIIIIIIIIIIIIIIIIIIIII  IIII                    
                   IIIIII IIIIIIIIIIIIIIIIIIIIIIIIIIII IIIIII                   
                   IIIIII IIIIIIIIIIIIIIIIIIIIIIIIIIII IIIIII                   
                   IIIIII 0110100001110100011101000111 IIIIII                   
                   IIIIII 0000001110100010111100101111 IIIIII                   
                   IIIIII 0110011101101111011011110010 IIIIII                   
                   IIIIII 1110011001110110110000101111 IIIIII                   
                   IIIIII 0110101001010000010011110100 IIIIII                   
                   IIIIII 100101101100IIIIIIIIIIIIIIII IIIIII                   
                   IIIIII IIIIIIIIIIIIIIIIIIIIIIIIIIII IIIIII                   
                   IIIIII IIIIIIIIIIIIIIIIIIIIIIIIIIII IIIIII                   
                   +IIII  IIIIIIIIIIIIIIIIIIIIIIIIIIII  IIII:                   
                          IIIIIIIIIIIIIIIIIIIIIIIIIIII                          
                          IIIIIIIIIIIIIIIIIIIIIIIIIIII                          
                           IIIIIIIIIIIIIIIIIIIIIIIIII                           
                                IIIIII    IIIIII                                
                                IIIIII    IIIIII                                
                                IIIIII    IIIIII                                
                                IIIIII    IIIIII                                
                                IIIIII    IIIIII                                
                                IIIII,    IIIIII                                
                                  I:        ~I

I noticed the binary around the android's belly so I translated it into ascii text and got the following URL:

http://goo.gl/jPOIl

I went to the URL and was faced with a puzzle with a timer. The puzzle involved rearranging letters, by swapping two neighbours, in a string so they were in the "correct" order. You could see how many were correct at the bottom of the page. It was pretty simple and I got the solution before the timer ran out (it took me a few seconds to recognize I was being timed).

Clicking the Tweet your victory! button opened a web tweet box with the message:

@googlenexus Run, run, as fast as you can! You can't catch me, I'm the Gingerbread Android!

I hope it's not against the rules to post the solution; I looked quickly through the legalese and didn't see anything forbidding it.

Getting my Images on Canvas

2010-07-27T16:49:00.000-07:00

I've been rendering some of my models to print them on canvas recently.

I had to wait to buy a new computer to render my stuff. Can you believe it though? I'm already upgrading. You'd think 8 gb would be lots of RAM, wouldn't you? My new RAM is in the mail.

I transferred one of the images to canvas already. It's only a little over 2' by a little over 4' but it cost me nearly $300 to print after taxes! My neighbour is going to stretch it onto a frame for me. I'm pretty excited.

Google Android Desktop Image

2009-10-28T10:10:00.001-07:00

Here is a copy of a Google Android desktop image I made in Maya. It's really simple; didn't take me long, but I thought other people may enjoy it too. Let me know if you think it could be made better in some way; I love suggestions.

1680x1050

1280x1024

1280x1024

1024x768

btw, I lit the scene using a "light probe" from Paul Debevec's authoritative page on the topic. The exact light map that I used is of Galileo's Tomb and you can see the reflection of light from the windows that light the room in the Android's glossy head. ;). If anyone would like my Maya model, let me know and I'll post it here.

Pagerank Experiment

2009-06-10T11:12:00.001-07:00

Yes, "PageRank" is effectively meaningless now. (it's offical because I say so ;))

I had been reading people talking about how what is most commonly referred to as Pagerank doesn't exist any more and that Pagerank tools were pretty well useless. However, I didn't want to take their word for it without verifying it for myself. So a little while ago I created an experiment to test how much PageRank actually reflects where one is positioned in a google search engine results page. When I refer to "Pagerank" I mean the value (between 0 and 10) that is associated with a site or web page and that is meant to be a reflection of how popular content is based on who is pointing to that content (google "pagerank tool" to get a list of a tools you can use online to check the pagerank of any site).

In my experiment I took a domain that had no pagerank (gumbozumbo.com) and I put some content on the site (namely a Flash/Software chess clock that I made some time ago and I now offer for free on the gumbozumbo.com domain). In my experiment I targetted the search term "free chess clock", which was easy to do because I dedicated the entire domain to providing/promoting this free application and the light content was very keyword targetted (for the record, I did nothing underhanded or illegitimate here. I offered real and valuable content and did not misrepresent it in any way).

Now, if you use any PageRank checker on the gumbozumbo.com domain name, you will see that it has a reported PageRank of Zero (even though I solicited a few very relevant links to the content).

(ranking courtesy of prchecker.info)

However, if you search for "free chess clock" in Google, you will see that it ranks first; ahead of free chess clocks on other domains that have high Pagerank.

Clearly highly focused content has a much greater affect on Google search engine rankings than similar content on sites that are not as focused, even if those sites have high Pagerank.

I hope discussing the gumbozumbo domain in this blog isn't going to throw the result off (grin).

Google Spreadsheet Server Monitoring

2009-06-02T19:28:00.000-07:00

Monitor your websites using a Google Spreadsheet and some PHP

What do you mean the website is down?

So, your client calls you and tells you that the contact form on their website isn't working. One of their customers called them to tell them, and it looks like it's been down for awhile. Your client wonders why you're the last one to know - why do they pay for maintenance anyway?

This is the situation we faced too many times, years ago, and why we started monitoring our servers. We quickly went from being the last one to know when a website stopped working properly, to being the first. We also began collecting a lot of valuable data about the quality of our web hosting services. Further more, we did a kind of testing that really meant something real to us. Instead of just checking to see if a server was up we created "sensors", that we placed on client websites, and would do things like make a simple call to the website's actual database, emulating what the website did as closely as possible. This told us more about what the actual user experience was like, and about whether our servers were doing what they were supposed to, than just pinging a server to see if it was up.

A few weeks ago I was thinking about server monitor software and I realized that most of the mechanics behind the software is actually pretty simple; the more difficult part is the reporting side of things. Fortunately Google Spreadsheet has the ability to read data from external sources and wonderful graphs and gadgets (like speedometers) for translating the server monitoring data; and to display our information meaningfully and handily. I admit I'm a big fan of the Google docs webapps and I decided, mostly for fun, to try my hand at writing a server monitor with a little PHP and one Google spreadsheet.

Google Docs to the Rescue?

I decided to make a project out of building a server monitor that used a Google Spreadsheet for its front end. I was right in that the core was quite simple, but I admit I added a few unforeseen yet indispensable "enhancements" along the way (like data "archiving"). It worked (and was fun to do) so I decided to write a blog about how it works and also showing people how they may do something like it for themselves (and hopefully inspire people to create things like it - I may also do a series on other things that might be done like search engine ranking reports, etc). I provide all my code here, and instructions for creating the spreadsheet.

I started by defining what I wanted it to do, partly inspired by the kinds of things I know I can do with Google Docs. This was my list:

It would send out an email/sms notification if something went down
It would send out an email/sms if it went back up again
I could view the status of all the sensors
I could view detailed history for any single sensor
It would email me a daily report
I could share the ¹live data with other people, and publish it back out to the Internet (again, live data)

Well, It worked out rather well. The only real drawback is it's not as immediate as I'd have liked (I want data updated by the second if I can get it). However, Google docs isn't going to poll your datasource every second (and for good reason!) so immediate updates aren't going to happen. However you can force an update if you need to and it refreshes often enough, I think, to stay quite useful.

Below I describe how you can make one for yourself. I'm not sure if I need to say this, but: I did this project and wrote this blog to amuse myself; and I provide this information here for your own benefit/amusement. It's up to you whether or not it's as dependable as is you want a server monitor to be and I'm not responsible if it doesn't work as well as you think it should (nor is it Google's).

What you'll need:

You'll need a Google account (of course) and a server (I used a shared Linux server at LunarPages) that isn't on a webserver you are monitoring. You'll have to write some server side script (I used PHP) and you'll need a database (I used MySQL). The sensors you create will be in whatever you use on your website currently (I show a couple of examples further on). You will also have to add two jobs to the cron so you'll need to make sure you have permission to create cron jobs (most of our hosting providers provide an interface for creating cron jobs in their control panel).

How it Works

The basic design has a group of small "things" (scripts and worksheets) working together to make it all work.

A cron job calls a script that tests all the sensors, and sends out notifications if necessary. The results are put in a database. The spreadsheet populates itself with the data from the database by calling some scripts, which pass back the data using CSV, and the spreadsheet uses that data to create all of our fancy charts and graphs. The settings for the application (e.g. the list of sensors) are also stored in the spreadsheet and the PHP scripts use that information to determine which sensors to call, etc. Finally a daily script sends out a summary email report and "compresses" old data to save space.

Step 1: Creating a Sensor

A sensor, in our terms, is a fairly simple thing. In fact it could be an existing web page if all you want to do is see if the site's web server is serving up pages. The server monitor simply tests to see if a sensor (which is a web page) returns an error code in it's header. With something like a database sensor we will "artificially" return an error code in the response header if there is a failure to send a query to the database.

Really you can create a sensor to test anything your want, even things on an application level (e.g. test to see if a variable has an expected value). Ideally a sensor would be able to tell that a website is acting entirely the way that it is supposed to, and short of regularly [24/7] parsing each page on the website for error codes, missing images, broken links and basically a rigorous testing régime, I think the sensor approach is about the best one can do (I would love to hear that I'm wrong - please comment below if you think I am).

I suggest, however, even if you are just going to test to see if a webserver is serving up pages that you make a special page, something simple, that has no linked resources, and is only called by your server monitor. Something like this:

<html>
<body>
Web server is functioning properly
</body>
</html>

and call it something like /sensors/websensor.html

For a database sensor I suggest making a very simple call to one of the database tables actually used by your website. Further more, if you use an include file for connecting to your database on your website, I suggest you use the same include file your site uses. More than once a sensor has told us of a problem when someone accidentally overwrote a connection string file with a file from a test/staging server (Human error is the biggest problem actually).

A typical database sensor, written in PHP, might look something like this:

<?php
require_once('../Connections/your_connection_string_include_file.php');

$sql = "SELECT id FROM your_table LIMIT 1";
$dbtest = mysql_query($sql, $database_connection) or die(mysql_error());
$dbtest_totalrows = mysql_num_rows($dbtest);

if($dbtest_totalrows > 0) {
 ?>Database is functioning properly<?php
} else {
 header("http_response_code: 500");
 ?>Database not functioning properly<?php
}
?>

Now we have a sensor that will tell you if the webserver is serving up pages AND is able to access your database. Please note that it doesn't matter what the server side code is here, I just used PHP in my example. We have sensors in multiple languages testing multiple aspects of our web sites; all that matters is if the sensor returns an error code in the response header or not.

Step 2: Creating our Spreadsheet

We're going to step away from our text editors/IDEs long enough to start our spreadsheet now. We'll start by creating the first ² worksheet in the spreadsheet, which I call the "sensor list". I strongly suggest that you build your spreadsheet just the same way I did, in terms of labels and what rows and columns data is put in, and then play with it afterward when it's all working. It will be easiest to follow me if you start out more or less exactly as I describe.

This first worksheet is going to do two things: It's the place where we are going to list the sensors that the application will test (our Sensors Tester script is going to read this list to determine which sensors to call). Also, beside each sensor on the list (columns A & B), we're going to display the sensor's current status in terms of green, yellow and red "lights" (but we'll save that for Step 4). Set up your spreadsheet so that it looks like this:

(note that I have left the A and B columns blank for now - that's where we're going to display the sensor's status in Step 4)

The first column in our data (column C, the "Sensor ID" column), is what the server logs are keyed to. I used this method for two reasons: Using an integer means less storage space in the database (each log record can be associated with the appropriate sensor with as little as one byte of data) and if I keyed it to an existing field (e.g. sensor name) I wouldn't be able to edit that field without orphaning the sensor's previous data.

The second column is the name that will be used by the application when referring to the sensor (for instance, the email notifications will this name in their alerts). This will also be the name used on Graphs so try not to make and of these labels long if you can avoid it.

The third column is the email address that is used when sending out notifications for this server (use commas to list more than one address). Personally I like to have the monitor text message my cell phone when a server goes down. This can usually be done quite easily as many cell phone providers provide an email address that you can use to text your cell phone.

This is all we have to do to create new sensors. Create the sensor itself and install it on the corresponding server, and add the sensor to this list. Automatically the sensor will begin being scanned, we will be notified when there are issues, and we can see the sensor's status are read reports on it (as soon as there is enough data to do so).

IMPORTANT: Please note that Google Docs doesn't update the published document immediately after making changes; rather there is a lag time between when you change the document and when it republishes it. You can usually make the spreadsheet update the published version immediately if you turn off publishing and then turn it back on again.

We also need to create another worksheet that will contain some settings. We'll call this worksheet "Sensor and Report Settings", and in it you should create the following fields:

The yellow cell, B2, should contain the following formula:

=VLookup(B1,'Sensor List'!C3:D7, 2, FALSE)

Rather than create a separate set of worksheets for each sensor report, we're going to make one set of work sheets that will display the data from any sensor. We will control which sensor is being reported on by changing the number (sensorID) in the green box on this worksheet. The above formula gives you a little positive feedback by displaying the name of the sensor that you've just selected

Before our application can read these settings we must publish the spreadsheet. At the top select Share > Publish as web page... and you will get a dialog box where you can publish the document. Click Publish Now and then click More publishing options on the bottom of the dialog box. This is where you can create a URL for specific ranges of data. We're going to generate two URLs, one for the sensor list on the first worksheet, and the second to for the settings on the second worksheet. In the pop-up dialog that appears when you click More publishing options set the File Format to CSV, under What sheets select Sheet "Sensor List" only, and under What cells enter C3:F50 (I picked 50 at random, the number only has to be higher than the last sensor on your list, but equal to or less than the number of rows currently on the spreadsheet). Make a copy of this URL for yourself and generate one for the settings on the Sensor and Report Settings worksheet (cell range B3:B6).

Step 3: Testing our Sensors

Okay, now we have:

a list of sensors to test,
at least one sensor script installed on a web server that we will test.

Now, on the server that will be doing the testing (again, the server that you are using for your testing should not be on a server that you plan to test), we will add some PHP script and a MySQL database that will do the actual testing and send out notifications (if needed) and store the test results in our database. Clearly this is the nexus of our application.

Let's start by creating our database (I named my database sensors). Here is SQL for creating the tables that I am using:

DROP TABLE IF EXISTS `sensor_log`;

CREATE TABLE IF NOT EXISTS `sensor_log` (
 `ID` int(11) NOT NULL auto_increment,
 `sensorID` smallint(2) NOT NULL,
 `lag` smallint(2) NOT NULL,
 `status_code` smallint(2) NOT NULL,
 `created_date` timestamp NOT NULL default CURRENT_TIMESTAMP,
 PRIMARY KEY  (`ID`),
 KEY `sensorID` (`sensorID`),
 KEY `status_code` (`status_code`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

DROP TABLE IF EXISTS `sensor_log_archive`;

CREATE TABLE IF NOT EXISTS `sensor_log_archive` (
 `ID` int(11) NOT NULL auto_increment,
 `sensorID` int(2) NOT NULL,
 `average_lag` int(2) NOT NULL,
 `downtime` int(3) NOT NULL,
 `sensor_date` date NOT NULL,
 `created_date` timestamp NOT NULL default CURRENT_TIMESTAMP,
 PRIMARY KEY  (`ID`),
 KEY `sensorID` (`sensorID`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

This is a very straight forward set of tables. The sensor_log table is where we store the results of our tests, and the sensor_log_archive table is where we store our "compressed" data (our script archives data by taking the aggregate results for an entire day for each sensor and inserts it into the archive table, thus we reduce the amount of space by a factor of nearly 100 to 1).

Now we'll start creating our testing script. I call this script testallsensors.php. and I keep the script in a folder called sensors. The first thing you're going to need is a connection string to your database. Again I keep this in a separate file, like always, and call the file database_connection.php. My database connection looks something like this:

database_connection.php

<?php

$database_hostname = "localhost";
$database_username = "dbusername";
$database_password = "dbpassword";
$database_name = "dbname";

$database_connection = mysql_pconnect($database_hostname, $database_username, $database_password) or trigger_error(mysql_error(),E_USER_ERROR);

mysql_select_db($database_name, $database_connection);

?>

(As you can see, I connect to the server and select my database in my connection script. I rarely have an application that accesses two databases on the same server so I find this most useful)

We're also going to read some data from our spreadsheet. I use a readCSV function that I found in the comments area of one of the PHP Manual pages, that I modified very slightly for this purpose (see http://www.php.net/fgetcsv). I keep the function in an include file as I use on multiple pages. It looks like this:

csv.php

<?php

define("CSV_Start", 0);
define("CSV_Quoted",   1);
define("CSV_Quoted2",  2);
define("CSV_Unquoted", 3);

function readCSV($fh, $len, $delimiter = ',', $enclosure = '"') {
 $data = Array();
 $fildNr = 0;
 $state = CSV_Start;

 $data[0] = "";

 do {
  if(($line = fgets($fh, $len)) == FALSE) return FALSE;
  for ($ix = 0; $ix < strlen($line); $ix++) {
   if ($line[$ix] == $delimiter) {
    if ($state != CSV_Quoted) {
     $fildNr++;
     $data[$fildNr] = "";
     $state = CSV_Start;
    } else {
     $data[$fildNr] .= $line[$ix];
    }
   } elseif ($line[$ix] == $enclosure) {
    if ($state == CSV_Start) {
     $state = CSV_Quoted;
    } elseif ($state == CSV_Quoted) {
     $state = CSV_Quoted2;
    } elseif ($state == CSV_Quoted2) {
     $data[$fildNr] .= $line[$ix];
     $state = CSV_Quoted;
    } else {
     $data[$fildNr] .= $line[$ix];
    }
   } else {
    $data[$fildNr] .= $line[$ix];
    if ($state == CSV_Quoted2) {
     break;
    } elseif ($state == CSV_Start) {
     $state = CSV_Unquoted;
    }
   }
  }
 } while ($state == CSV_Quoted);

 return $data;  
}

?>

Okay, now we'll start testallsensors.php. The first thing we'll do is import the settings from our spreadsheet. We'll start by ECHOing the settings to the script output so we can verify that the settings are being imported correctly. (make sure you replace the URL so that it's the URL for the settings that you determined in Step 2).

testallsensors.php (stage one)

<?php

require_once('database_connection.php');
require_once('csv.php');


$handle = fopen("http://spreadsheets.google.com/pub?key=XXXXXXXXXXXXXXXX&output=csv&gid=2&range=B3:B6", "r");

if(($results = readCSV($handle, 1000)) == FALSE) break;
$settings_recipients = str_replace("\n", "", $results[0]);

if(($results = readCSV($handle, 1000)) == FALSE) break;
$settings_failures = str_replace("\n", "", $results[0]);

if(($results = readCSV($handle, 1000)) == FALSE) break;
$settings_retry_minutes = str_replace("\n", "", $results[0]);

if(($results = readCSV($handle, 1000)) == FALSE) break;
$settings_archive_days = str_replace("\n", "", $results[0]);

fclose($handle);

echo("$settings_recipients|$settings_failures|$settings_retry_minutes|$settings_archive_days");

?>

If everything went well, you should see the settings you entered into your spreadsheet when you run this script. If it didn't work, check the URL and make sure that your spreadsheet is Published.

Lets go ahead an check out the rest of this script. I'll discuss it in detail below:

testallsensors.php (stage two)

<?php

require_once('database_connection.php');
require_once('csv.php');



function timestamp() {
 $mytime = split(" ", microtime(true));
 $mytime = ($mytime[0] + $mytime[1]);
 return round($mytime*1000);
}



// Get the maintenance settings

$handle = fopen("http://spreadsheets.google.com/pub?key=XXXXXXXXXXXX&output=csv&gid=2&range=B3:B6", "r");

if(($results = readCSV($handle, 1000)) == FALSE) break;
$settings_recipients = str_replace("\n", "", $results[0]);

if(($results = readCSV($handle, 1000)) == FALSE) break;
$settings_failures = str_replace("\n", "", $results[0]);

if(($results = readCSV($handle, 1000)) == FALSE) break;
$settings_retry_minutes = str_replace("\n", "", $results[0]);

if(($results = readCSV($handle, 1000)) == FALSE) break;
$settings_archive_days = str_replace("\n", "", $results[0]);

fclose($handle);


// The back end could serve multiple spreadsheets each containing their own list of sensors
// if you want to do this, add each additional sensor list to this array:

$handle_list = array (
 "http://spreadsheets.google.com/pub?key=XXXXXXXXXXXXXXX&output=csv&gid=0&range=C3:F99"
);


$retries = 0;
$retry = false;

// we keep looping until we are sure a sensor has not gone down (i.e. a sensor that was previously up has not failed $settings_failures times).
do {

 $any_failures = false;

 for($handle_index=0 ; $handle_index<count($handle_list) ; $handle_index++) {
 
  $handle = fopen($handle_list[$handle_index], "r");
  
  while(($results = readCSV($handle, 1000)) != FALSE) {
 
   // go through each sensor on the list, test it, record it, and send out a notification if necessary

   $sensor_sensorID = intval($results[0]);
   $sensor_name = trim($results[1]);
   $sensor_url = trim($results[2]);
   $sensor_emails = trim($results[3]);
 
   if(!$sensor_sensorID || $sensor_name=="" || $sensor_url=="" || $sensor_emails=="") continue;


   // if it's a retry, then find out if this sensor failed last time
   $sensor_failed = false;
   if($retry) {
   
    $sql = "SELECT status_code FROM sensor_log WHERE sensorID=$sensor_sensorID ORDER BY created_date DESC LIMIT 1;";
 
    $result = mysql_query($sql, $database_connection) or die(mysql_error());
    $row = mysql_fetch_assoc($result);
    $sensor_failed = (intval($row['status_code'])==200) ? false : true;
    mysql_free_result($result);
   
   }


   if(($retry && $sensor_failed) || !$retry) {
   
    $start = timestamp();
    $response = @ file_get_contents($sensor_url, "r");
    list($version,$status_code,$msg) = explode(' ',$http_response_header[0], 3);
    $end = timestamp();
    $difference = $end-$start;


    // check to see if this sensor has had previous trouble
    $sql = "SELECT status_code FROM sensor_log WHERE sensorID=$sensor_sensorID ORDER BY ID DESC LIMIT $settings_failures";
    $dbtest = mysql_query($sql, $database_connection) or die(mysql_error());
    $dbtest_totalrows = mysql_num_rows($dbtest);

    $error_count = $settings_failures-$dbtest_totalrows;

    for($i=0 ; $i<$dbtest_totalrows ; $i++) {
     $row = mysql_fetch_assoc($dbtest);
     if($row['status_code'] != 200) $error_count++;
    }

    // add this last test to the database
    $sql = "INSERT INTO sensor_log (sensorID, status_code, lag) VALUES ($sensor_sensorID, $status_code, $difference)";
    $dbtest = mysql_query($sql, $database_connection) or die(mysql_error());


    // if this is the first good reading in awhile, then the server just went back up.
    if($error_count == $settings_failures && $status_code == 200) {
     // sensor has come back up after being down

     $subject =  "$sensor_name is back up!";
     $msg = "The server went back up!\n\n";

     ini_set('sendmail_from', 'nopreply@yoururl.com');

     $to = $sensor_emails; // separate email addresses with commas
     $mailheaders = "From: Server Monitor <nopreply@yoururl.com> \n";

     mail($to, $subject, $msg, $mailheaders);

    }

    $any_failures = ($status_code != 200 && $error_count < $settings_failures) ? true : $any_failures;


    if($retries == $settings_failures && $status_code != 200) {
     // send the email notification

     $subject =  "$sensor_name has gone down!";
     $msg = "The server went down!\n\n";

     ini_set('sendmail_from', 'nopreply@yoururl.com');

     $to = $sensor_emails; // separate email addresses with commas
     $mailheaders = "From: Server Monitor <noreply@yoururl.com> \n";

     mail($to, $subject, $msg, $mailheaders);

    }

   }

  }

  fclose($handle);
 
 }

 if($any_failures) {

  $retries++;
  sleep(abs($settings_retry_minutes * 60));
  $retry = true;

 } else {

  $retry = false;

 }


} while ($retry);

?>

Okay, there are a few things that need explaining here. I'll describe in plain language what's going on:

First the script reads the settings, as we reviewed in testallsensors.php (stage one).

The very outside loop is the retry loop. If any sensor fails (i.e. returns a status code other than 200) that wasn't failing previously, this loop continues until the sensor is either good, or the script has exhausted it's number of retries (the number of retries, and the length of time this script sleeps between each retry is determined in our settings). Each time a test is made, the lag time and the response code is INSERTed into the sensor_log table.

The loop nested inside the retry loop is a loop that goes through each sensor list (if there is more than one). I built the back end so it could serve more than one list of sensors (i.e. lists from multiple spreadsheets); I do this by looping through an array of sensor list URLs (the example has just one URL but you can add others). As it reads each sensor from the list, it calls it, gets the result and times the response time (what I refer to as lag).

I built it so one can have multiple sensor lists for organizational purposes. I thought there was pretty good chance that people would want to create separate spreadsheets for different customers etc. There's a lot to be said for having only one list though (one place to go to view the status of all of your sensors) and even if you have one sensor list you can, of course, create as many reports as you want for any sensorID on any number of spreadsheets. Just remember to never reuse a sensorID (as least not with the same backend and database). If you use the same sensorID on any sensor list twice, with the same back end/database, their data will get mixed together.

Within the inner loop we do our sensor test and if it comes back up after being previously down then an "Up" notification goes out. If a sensor returns $settings_failures failures after previously being up, then a "Down" notification also goes out. And whatever happens the result and the lag are inserting into the database with the current timestamp and the sensorID.

At the end of the loop we see if a sensor is failing (by which I mean, if it came back down but has not returned $settings_failures failures yet. If there is such a circumstance, the process sleeps for $settings_retry_minutes before it tries again.

Run a few tests with the script and make sure that it's properly reading the information from your spreadsheet, that it's conducting it's tests and INSERTing the test results in the database. Finally, simulate one of your sensors going down (you can do this by simply temporarily renaming a sensor so the script can't find it / gets a status code of 404) and make sure you get the notifications as the script detects the error. It's possible that your tests will time out on your webserver because of the sleep commands. This won't be an issue later as the cron will be running the script and it won't be running it using the webserver, but the timeouts can make testing difficult. If you get webserver timeouts, try shortening the $settings_retry_minutes and/or $settings_failures values temporarily for your tests or extend your server's timeout.

Important Note: If you edit this code, make sure you don't cause the script to go into an endless loop. Eventually, when you begin using the cron will thread a new instance of this script every 15 minutes and if the script isn't terminating correctly you could make your testing server very very unhappy.

I'm using LunarPages for hosting and they have a very easy to use option in their control panel for creating cron jobs, but every shared hosting service we use has a similar facility. Usually you have to call the script by passing it to the PHP interpreter (e.g. php /path/to/script/testallsensors.php). I find running the script every 15 minutes is perfect. More often than that isn't much more useful, and it may get your hosting services upset with you. If you have issues creating a cron job call your hosting service and they'll surely help you out.

When you are satisfied the script is working correctly, we'll move on displaying the current status for all the sensors in our spreadsheet.

Step 5: Current Server Status

To display the current server status, first we need to pull results from the database and populate a new worksheet with them. We'll do this by having the worksheet execute an importDATA function that calls a script that returns CSV values that the function will use to populate the worksheet. We'll start by creating the script that returns the CSV data:

sensorsummary.php

<?php

require_once('database_connection.php');
require_once('csv.php');


// We get the list of sensors from the spreadsheet

$spreadsheet_sensorID = array();
$spreadsheet_name = array();


$handle = fopen("http://spreadsheets.google.com/pub?key=XXXXXXXXXXXXXXXX&output=csv&gid=0&range=C3:C99", "r");

while(($results = readCSV($handle, 1000)) != FALSE) {

 array_push($spreadsheet_sensorID, intval($results[0]));

}


$sql = "SELECT sensorID, lag, status_code FROM sensor_log WHERE ID IN (SELECT MAX(ID) AS lastID FROM sensor_log WHERE sensorID IN (" . join(",", $spreadsheet_sensorID) . ") GROUP BY sensorID);";


$result = mysql_query($sql, $database_connection) or die(mysql_error());
$result_totalrows = mysql_num_rows($result);

for($i=0 ; $i<$result_totalrows ; $i++) {
 $row = mysql_fetch_assoc($result);
 echo "" . $row['sensorID'] . "," . $row['lag'] . "," . (($row['status_code']==200) ? (200) : (0)) . "\n";
}

mysql_free_result($result);

?>

Simply put, this script looks at all of the sensors on a sensor list and returns their current status (again, as CSV data, which I used as it was easiest).

If you have more than one spreadsheet/sensor list using your back end you will either have to create one of these scripts for each of your spreadsheets or pass something to this script to that identifies which sensor list URL it should use.

Once you have tested the script and made sure it is indeed outputting the sensor status data correctly, you can go ahead and import the data into your spreadsheet. Go to your spreadsheet and create a worksheet called Sensor Status Data. The worksheet should look like this:

In the cell A2, insert the following function:

=importData("http://www.yourserver.com/sensors/sensorsummary.php?temp=" & INT(NOW()/TIME(0;10;0)))

The temp value appended onto the end of the function causes the filename to change every 10 minutes; this helps to keep the data fairly current. Please understand that Google can't poll your script every few seconds or anything like that; nor would it be a good idea anyway (that's a LOT of traffic). Normally the spreadsheet updates the pulled data at variable freqencies, presumably depending on how busy their servers are. There is a certain amount of lag time here, especially during heavy traffic periods, but you can force an update if you really need to make sure the spreadsheet is as current as possible, and you can call testallsensors.php if you need to re-pole the servers being tested (you can force the data to reload manually by editing the cell with the function and changing the cell contents - usually I just add a space to the end of the cell contents).

I also made a second version of this script that pulls in some more details and orders the data into columns rather than rows. It really is very close to the same data used here sensorsummary.php, but I'll include it here for convenience sake; some of the graphs and gadgets that are available in Google Spreadsheet require the data to be organized like this:

sensorsummary_detailed.php

<?php

require_once('database_connection.php');
require_once('csv.php');



$current_ID = array();
$current_lag = array();
$current_code = array();

$historical_ID = array();
$historical_lag = array();





// We get the list of sensors from the spreadsheet

$spreadsheet_sensorID = array();
$spreadsheet_name = array();


$handle = fopen("http://spreadsheets.google.com/pub?key=XXXXXXXXXXXXXXXXXXXX&output=csv&gid=0&range=C3:D99", "r");

while(($results = readCSV($handle, 1000)) != FALSE) {

 array_push($spreadsheet_sensorID, intval($results[0]));
 array_push($spreadsheet_name, str_replace("\n", "", $results[1]));

}


// We get the historical average so we have something to compare against
$sql = "SELECT sensorID, FLOOR(AVG(lag)) AS average_lag, AVG(status_code) AS average_code, HOUR(created_date) AS hourgroup FROM `sensor_log` WHERE HOUR(NOW())=HOUR(created_date) GROUP BY sensorID, hourgroup;";
$result = mysql_query($sql, $database_connection) or die(mysql_error());
$result_totalrows = mysql_num_rows($result);

for($i=0 ; $i<$result_totalrows ; $i++) {
 $row = mysql_fetch_assoc($result);
 array_push($historical_lag, $row['average_lag']);
 array_push($historical_ID, $row['sensorID']);
}


// We get the average for the last hour
$sql = "SELECT sensorID, FLOOR(AVG(lag)) AS average_lag, AVG(status_code) AS average_code FROM `sensor_log` WHERE (TIMEDIFF(NOW(), created_date) < TIME('1:00:00')) GROUP BY sensorID;";
$result = mysql_query($sql, $database_connection) or die(mysql_error());
$result_totalrows = mysql_num_rows($result);

for($i=0 ; $i<$result_totalrows ; $i++) {
 $row = mysql_fetch_assoc($result);

 // build arrays of the results, so we can turn them on their side
 array_push($current_ID, $row['sensorID']);
 array_push($current_lag, $row['average_lag']);
 array_push($current_code, (($row['average_code']==200) ? (200) : (0)));

}


// write out a row of sensor names
for($j=0 ; $j<count($spreadsheet_sensorID) ; $j++) {
 for($i=0 ; $i<count($current_ID) ; $i++)
  if($spreadsheet_sensorID[$j] == $current_ID[$i])
   echo "\"{$spreadsheet_name[$j]} ({$current_ID[$i]})\"";
 if($j<count($spreadsheet_sensorID)-1) echo ",";
}

echo "\n";

// write out a row of current lag times
for($j=0 ; $j<count($spreadsheet_sensorID) ; $j++) {
 for($i=0 ; $i<count($current_ID) ; $i++)
  if($spreadsheet_sensorID[$j] == $current_ID[$i])
   echo "\"{$current_lag[$j]}\"";
 if($j<count($spreadsheet_sensorID)-1) echo ",";
}

echo "\n";

// write out a row of average lag times for the same hour
for($j=0 ; $j<count($spreadsheet_sensorID) ; $j++) {
 for($i=0 ; $i<count($historical_ID) ; $i++)
  if($spreadsheet_sensorID[$j] == $historical_ID[$i])
   echo "\"{$historical_lag[$j]}\"";
 if($j<count($spreadsheet_sensorID)-1) echo ",";
}

echo "\n";

// write out a row of status codes (200 = entire hour is good, 0 = error)
for($j=0 ; $j<count($spreadsheet_sensorID) ; $j++) {
 for($i=0 ; $i<count($current_ID) ; $i++)
  if($spreadsheet_sensorID[$j] == $current_ID[$i])
   echo "\"{$current_code[$j]}\"";
 if($j<count($spreadsheet_sensorID)-1) echo ",";
}

echo "\n";


mysql_free_result($result);

?>

Colouring Our Data

This is useful stuff; there's nothing like having problems stand out in red. Google Docs Spreadsheet has a very easy mechanism for colouring your cells based on rules. In my spreadsheet I took all of the values, including the values in the worksheets that contain the imported data, and made them so that they changed colour based on their value. This makes is really easy to spot trouble.

Colouring Lag Values

Ideally I think I'd like to base the colours on tolerances within what would be considered normal for a specific sensor, but in my example I used a gross scale that I apply to all the values. The values I used are:

< 500 = good (green),
500 - 1000 = medium (yellow),
> 1000 = bad (red)

Please note that some kinds of sensors are going to naturally take longer to return a result than others. For instance, a "web sensor" doesn't have to have any server side code at all, where as a "database sensor" needs to open a connection to the database server, run a query and inspect the results.

The diagram on the right shows the exact settings I used. Make sure you select the entire column (or at least from row 2 to the bottom of your worksheet) that you intend to create your rules for before you create your rules.

Colouring Errors

Errors are easier to colour because there are only two states: error (red) and no error (green). On the front worksheet (the sensor list), beside each sensor in the first column, I made a "light" by inserting the error value from the worksheet that I pull the sensor status into (I conveniently return the values in the same order that they appear on the list). I then colour the text so you can't see the value at all, just bright green or red by making the rule change the text colour so that it's the same as the background colour.

Okay! We've come a long way now. We have the sensors being tested, notifications being sent out, data being stored, and the results coloured with current status lights beside each of the sensors on our sensor list. Now we just need to create useful reports on individual sensors, daily sensor reports, and just to be thorough, we're going to archive/compress our old data.

Creating our Detailed History Report

History reports allow us to get a bigger picture of a sensor's status and allow us to see in finer detail what went wrong and when. When I first started this project I wasn't quite sure how I was going to create a history report (which requires multiple worksheets) for each sensor. It soon occurred to me that if I create an adaptable report, where I could change one setting and have the report populate itself with the data from any sensor, I would save the end user (in this case, me!) a lot of work (and if we ever need to send someone a copy of a sensor history report, we can always "hard wire" a copy of the report for that specific use).

The way that I chose to do this was by creating a cell (that I colour Green) on the Sensors and Report Settings worksheet where the user enters the sensor ID that they want to create a report for (if someone can think of a way to do this with some kind of select box or something I'd like to hear from you). For convenvience sake, I chose this page to display a list of the sensors and their hourly averages (the list also shows the current hour compared to the same hour's recent historical average) so that the user has the sensor list handy.

When the sensor ID is changed, two other worksheets are populated by calling two scripts that return 24 hour and 10 data historical data for the sensor indicated in the green box. Usually the data is populated within a few seconds of changing the [sensor ID] number in this cell, because changing the cell value alters the URLs that the data is read from and that typically triggers a [nearly] immediate update.

Then, finally, I have a fourth worksheet (titled the "Sensor Report") that shows two graphs based on the historical data for the sensor ID entered. The 24 hour graph shows actual values, where as the 10 day graph shows hourly averages for that period.

I pulled in the data by entering the following formulas in the A2 cells on the 24 Hour Error Trend Data and the 10 Day Error Trend Data worksheets:

for the 24 Hour Error Trend Data worksheet:

for the 10 Day Error Trend Data worksheet:

Graphing the Data

I used the Interactive Time Series graphs for this report. I found they were a good way of allowing the end user to examine any part of the data easily. Please note that these graphs will not be able to display any data until enough data collected first. You can [patiently] wait until there's enough data or you can generate some test data in the database if you are feeling particularly impatient.

Use the following for your Range in the graph's settings:

'24 Hour Error Trend Data'!A2:C130

and

'10 Day Error Trend Data'!A2:C250

(note that the end of these two ranges can't go beyond the end of the last row that you actually have in these two work sheets. I padded the worksheets with extra rows because, at least with the 24 hour data, you can't know exactly how many actual readings there will be (because bad sensor readings generate extra follow-up readings to verify the trouble wasn't just some temporary network fluctuation - plus you may have triggers several test readings).

And the following are the two PHP scripts that are called to pull in the data:

24hours.php

<?php

require_once('database_connection.php');

$sensorID = 0;
if(isset($_GET['sensorID'])) {
 $sensorID = intval($_GET['sensorID']);
}
if(isset($_POST['sensorID'])) {
 $sensorID = intval($_POST['sensorID']);
}

if($sensorID == 0) {
 echo "error";
 exit();
}

$sql = "SELECT lag, YEAR(created_date) AS yeargroup, MONTH(created_date) AS monthgroup, DAY(created_date) AS daygroup, HOUR(created_date) AS hourgroup, MINUTE(created_date) AS minutegroup, status_code, TIMEDIFF(NOW(), created_date) AS boo FROM sensor_log WHERE (TIMEDIFF(NOW(), created_date) < TIME('24:00:00')) AND sensorID=$sensorID ORDER BY created_date;";
$result = mysql_query($sql, $database_connection) or die(mysql_error());
$result_totalrows = mysql_num_rows($result);


for($i=0 ; $i<$result_totalrows ; $i++) {
 $row = mysql_fetch_assoc($result);
 $minutes = sprintf("%02d", $row['minutegroup']);
 echo "" . $row['monthgroup'] . "/" . $row['daygroup'] . "/" . $row['yeargroup'] . " " . $row['hourgroup'] . ":" . $minutes . "," . $row['lag'] . "," . (($row['status_code']==200) ? (200) : (0)) . "\n";
}


mysql_free_result($result);

?>

10days.php

<?php

require_once('database_connection.php');

$websiteID = 0;
if(isset($_GET['websiteID'])) {
$websiteID = intval($_GET['websiteID']);
} else if(isset($_POST['websiteID'])) {
$websiteID = intval($_POST['websiteID']);
}

if($websiteID == 0) {
echo "error";
exit();
}


$sql = "SELECT FLOOR(AVG(lag)) AS average_lag, YEAR(created_date) AS yeargroup, MONTH(created_date) AS monthgroup, DAY(created_date) AS daygroup, HOUR(created_date) AS hourgroup, AVG(status_code) AS average_code, TIMEDIFF(NOW(), created_date) AS boo FROM sensor_log WHERE (TIMEDIFF(NOW(), created_date) < TIME('240:00:00')) AND websiteID=$websiteID GROUP BY yeargroup, monthgroup, daygroup, hourgroup ORDER BY created_date;";
$result = mysql_query($sql, $database_connection) or die(mysql_error());
$result_totalrows = mysql_num_rows($result);

for($i=0 ; $i<$result_totalrows ; $i++) {
$row = mysql_fetch_assoc($result);
echo "" . $row['monthgroup'] . "/" . $row['daygroup'] . "/" . $row['yeargroup'] . " " . $row['hourgroup'] . ":00," . $row['average_lag'] . "," . (($row['average_code']==200) ? (200) : (0)) . "\n";
}


mysql_free_result($result);

?>

Creating the Daily Report and Maintaining Our Database

We're going to take care of both of these tasks with one script that we'll have the cron call every 24 hours:

The Daily Report

The daily report is a report that we'll have emailed to us first thing in our day so that we can see at a glance how our servers have been doing in the last 24 hours. The report I made is quite simple in the it shows the sensor list, as well as each sensor's up time and lag time, for the most recent 24 hours. For each value I use a small function that calculates an appropriate RGB value (colour) for each of the values shown on the sensor list.

"Archiving" old data

Also, as it would be unnecessary (or even excessive) to keep every ping value in perpetuity, we take data that is old (I use >2 weeks) and then average the values for each day we are archiving and place the averaged lag/up time values in another table (sensor_log_archive); deleting the old sensor values as we go. This should make your database nearly 100 times smaller than it would be otherwise.Here is the PHP script that I created for the report. Remember that you will have to create a cronjob that will call the script once a day.

daily.php

<?php

/*

 This script does two things.
 
 1) sends out the daily email report
 2) archives old data
 
 This script must be called by the cron daily

*/

require_once('database_connection.php');
require_once('csv.php');



// Get the maintenance settings

$handle = fopen("http://spreadsheets.google.com/pub?key=XXXXXXXXXXXXXXXXXXX&output=csv&gid=3&range=B3:B6", "r");

if(($results = readCSV($handle, 1000)) == FALSE) break;
$settings_recipients = str_replace("\n", "", $results[0]);

if(($results = readCSV($handle, 1000)) == FALSE) break;
$settings_failures = str_replace("\n", "", $results[0]);

if(($results = readCSV($handle, 1000)) == FALSE) break;
$settings_retry_minutes = str_replace("\n", "", $results[0]);

if(($results = readCSV($handle, 1000)) == FALSE) break;
$settings_archive_days = str_replace("\n", "", $results[0]);



// create a set of 2 dimensional arrays holding lag/down time for all sensors on all archivable dates
// (grouped by sensorID and date)
$compressed_sensor_readings = array();
$compressed_cummulativelag = array(); // Culumative lag time
$compressed_downtime = array();

$first_bad = array();
$last_code = array();

$sql = "SELECT sensorID, lag, status_code, created_date, DATE(created_date) AS group_date FROM sensor_log WHERE created_date < DATE_SUB(CURRENT_DATE(), INTERVAL $settings_archive_days DAY) ORDER BY sensorID, created_date";
$result = mysql_query($sql, $database_connection) or die(mysql_error());
$result_totalrows = mysql_num_rows($result);

for($i=0 ; $i<$result_totalrows ; $i++) {
 $row = mysql_fetch_assoc($result);
 $compressed_sensor_readings[$row['sensorID']][$row['group_date']]++;
 $compressed_cummulativelag[$row['sensorID']][$row['group_date']]+=$row['lag'];

 // check to see if up or down; and calculate downtime
 if($row['status_code'] == 200) {
  $compressed_downtime[$row['sensorID']][$row['group_date']] += (isset($last_code[$row['sensorID']][$row['group_date']]) && $last_code[$row['sensorID']][$row['group_date']] != 200) ? (strtotime($row['created_date']) - $first_bad[$row['sensorID']][$row['group_date']]) : 0;
  $last_code[$row['sensorID']][$row['group_date']] = 200;
  $first_bad[$row['sensorID']][$row['group_date']] = null;
 } else {
  $first_bad[$row['sensorID']][$row['group_date']] = (!isset($last_code[$row['sensorID']][$row['group_date']]) ) ? strtotime($row['created_date']) : $first_bad[$row['sensorID']][$row['group_date']];
  $first_bad[$row['sensorID']][$row['group_date']] = ($last_code[$row['sensorID']][$row['group_date']] == 200) ? strtotime($row['created_date']) : $first_bad[$row['sensorID']][$row['group_date']];
  $last_code[$row['sensorID']][$row['group_date']] = 0;
 }

}

mysql_free_result($result);


// Insert archived data into archive table
while (list($current_sensorID, $value) = each($compressed_sensor_readings)) {
 while (list($current_sensor_date, $value2) = each($value)) {
  $sql = "INSERT INTO sensor_log_archive (sensorID, average_lag, downtime, sensor_date) VALUES ($current_sensorID," . intval($compressed_cummulativelag[$current_sensorID][$current_sensor_date]/$compressed_sensor_readings[$current_sensorID][$current_sensor_date]) . ",{$compressed_downtime[$current_sensorID][$current_sensor_date]},'$current_sensor_date');\n";
  $result = mysql_query($sql, $database_connection) or die(mysql_error());
  mysql_free_result($result);
 }
}

// Delete pre-archived data from log
$sql = "DELETE FROM sensor_log WHERE created_date < DATE_SUB(CURRENT_DATE(), INTERVAL $settings_archive_days DAY);";
$result = mysql_query($sql, $database_connection) or die(mysql_error());

mysql_free_result($result);



function percentage_to_color($p){
 $red = $p<50 ? 255 : round(256 - ($p-50)*5.12);
 $green = $p>50 ? 255 : round(($p)*5.12);
 return sprintf("%02X%02X00", $red, $green);
}


function lag_to_color($l){
 $p = 100 - ($l/12.5);
 $p = ($p<0) ? 0 : $p;

 return percentage_to_color($p);
}





// We get the list of sensors from the spreadsheet
$spreadsheet_sensorID = array();
$spreadsheet_name = array();
$spreadsheet_url = array();

$handle = fopen("http://spreadsheets.google.com/pub?key=XXXXXXXXXXXXXXXXXX&output=csv&gid=0&range=C3:E99", "r");

while(($results = readCSV($handle, 1000)) != FALSE) {

 array_push($spreadsheet_sensorID, intval($results[0]));
 array_push($spreadsheet_name, str_replace("\n", "", $results[1]));
 array_push($spreadsheet_url, str_replace("\n", "", $results[2]));

}


// Summarize the data from yesterday

$summary_sensor_readings = array();
$summary_lag = array();
$summary_good_codes = array();


$sql = "SELECT sensorID, lag, status_code FROM sensor_log WHERE DAY(created_date) = DAY(DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY)) ORDER BY sensorID, created_date;";

$result = mysql_query($sql, $database_connection) or die(mysql_error());
$result_totalrows = mysql_num_rows($result);

for($i=0 ; $i<$result_totalrows ; $i++) {
 $row = mysql_fetch_assoc($result);
 if(intval($row['sensorID']) > 0) {
  $summary_sensor_readings[intval($row['sensorID'])]++;
  $summary_lag[intval($row['sensorID'])] += $row['lag'];
  if($row['status_code'] == 200) $summary_good_codes[intval($row['sensorID'])]++;
 }
}

mysql_free_result($result);


// create the daily email report.

$emailcontent = "";

$yesterday=date('l jS \of F, Y', time()-86400);

$emailcontent .= "<p>Monitoring Report for: $yesterday</p>";

$emailcontent .= "<table>";
$emailcontent .= "<tr><th>Sensor Tested</th><th>Average Lag</th><th>Uptime</th></tr>";

for($i=0 ; $i<count($spreadsheet_sensorID) ; $i++) {

  $average_lag = intval($summary_lag[$spreadsheet_sensorID[$i]] / $summary_sensor_readings[$spreadsheet_sensorID[$i]]);
  $percentage_up = sprintf("%.2d", ($summary_good_codes[$spreadsheet_sensorID[$i]] / $summary_sensor_readings[$spreadsheet_sensorID[$i]])*100 );
  


 $emailcontent .= "<tr><td><a href=\"{$spreadsheet_url[$i]}\">{$spreadsheet_name[$i]} ({$spreadsheet_sensorID[$i]})</a></td><td style=\"background: #" . lag_to_color(intval($average_lag)) . ";\">{$average_lag}</td><td style=\"background: #" . percentage_to_color(intval($percentage_up)) . ";\">{$percentage_up}%</td></tr>\n";
}
$emailcontent .= "</table>";

$email = "<html><body>\n" . $emailcontent . "\n</body></html>";

ini_set('sendmail_from', 'nopreply@yourdomain.com');
   
$mailheaders = "From: Server Monitor <noreply@yourdomain.com> \n";
$mailheaders .= 'Content-type: text/html; charset=iso-8859-1\n';
   
mail($settings_recipients, "Server Monitor Report", $email, $mailheaders);


?>

Remember now, to add new sensors all you have to do is upload a sensor script to the server you are monitoring and add 1 line to the sensor list. The application will see the published list and start calling the sensor.I have several little extra features I've added to my spreadsheet (e.g. a call that tells the user the next unused sensorID) that I'd be happy to share with people (I just don't want to turn this article into a book - grin).

Happy Days are Here Again!

Oh, Happy Customers! Now you are the first to know when a website goes down. What's more, you've got charts and graphs to show your customers the great service they are getting and demonstrate the diligence you show on their behalf. And, you have historical data that you can compare and will give you are better idea of how well your servers are performing, as well as providing you with data that you can use when working with your providers to help diagnose issues, identify bottlenecks and improve service [where needed].If you like this post, and you'd like to see some more projects along these lines, drop me a line (especially if you have ideas you'd like to contribute). If there is enough interest I will consider doing a series on using Google Spreadsheets as front ends to other types of reports and monitoring webapps.

By "live data" I refer to data that is connected to an external data source, and contains the most current information available.
A worksheet is like a page within your spreadsheet. You can select between, and create, worksheets using the tabs at the bottom of your spreadsheet.