Automated Visitor Stats from a Drupal Website

Drupal Statistics On Your Cell Phone

Some web statistics are so fundamental to webmasters that they can't live without them. For me, a basic measure of how a site is doing is it's daily count of unique IPs. That's a count of how many individual computers visited the site. I have that and other summary statistics text messaged to my phone every morning. In this post I'm going to show you how to do this on a Drupal based website using PHP, SQL, and CRON. According to it's About page:

Drupal is a free software package that allows an individual or a community of users to easily publish, manage and organize a wide variety of content on a website. Tens of thousands of people and organizations are using Drupal to power scores of different web sites.

Drupal stores its data in a MySQL database. In order to generate the right statistics you'll need to know the database tables and what they contain. If you want to view the tables directly from within Drupal, the DBA module will allow this. However, in this example the sample script pulls the data for you so you don't have to worry about the underlying database structure.

Overview

A daily cron job executes the script using the lynx command line browser. The PHP script executes a scalar query to a MySQL database. It then emails the results. The email address is a special address Verizon provides all their customers. Any mail sent there will be received as an SMS. Other cell phone providers offer a similar service. It's usually an "undocumented" feature; or at the very least, it's not advertised.

Some web hosts don't allow connections to the MySQL database from outside the server. If that's the case for you, then the PHP script has to reside on the web host. Otherwise, you could keep this script on your own desktop or home server.

Details

The SQL is specific to Drupal 5 and MySQL 4. I played around with the tables and their schema to find which ones contain visitor information. The table to access is aptly named "accesslog". The relevant date field to use is "timestamp" and it's an int(10) UNIX format. This is important because I only want the previous day's stats. A conditional statement has been added to ignore records coming from my home network, "192.168%". Some of my sites are hosted on my home server and those entries in the table should be ignored. Once a site goes live then my IP will be insignificant. But when in development and testing I want to ignore that address.

To get the script running, fill in the database related variables. They are in the "DB Info" section. Replace the example cell phone number's email address with your own. You can even use a regular email address if you like.

//Daily SMS Reports for jozefnagy.com

require_once('DB.php'); //PEAR DB

$to = "6175551234@vtext.com";
$subject = "Daily Website Report";
$msg;
$sql;
$result;

//DB Info
$user = "";
$pass = "";
$host = "";
$database = "";
$dbtype = "mysql";

$dsn = "$dbtype://$user:$pass@$host/$database";

$db = DB::connect($dsn);
if(DB::isError($db)){
mail($to, $subject, "DB Connect Error");
return 1;
}

//Report 1: unique IPs
$sql = "select count(distinct hostname) as total from accesslog where left(FROM_UNIXTIME(timestamp), 10) = subdate(curdate(), interval 1 day) and hostname '192.168%'";

$result = $db->getOne($sql); //return scalar value

if(DB::isError($result))
$result = "ERROR";
if($result == null)
$result = "ERROR";

$msg = "Yesterday's Unique IPs = $result";
mail($to, $subject, $msg);

Download above script

CRON

Now that the script is installed, we need to automate its execution. If you wanted to manually execute the script, simply visit its URL: http://www.example.com/cgi-bin/php/report.php. To automatically have the script executed daily, cron will visit the URL. This can be done from the same server hosting the database and the script or not. In my case, my home server reaches out to the URL. This cron job runs every morning at 8:00 AM:

0 8 * * * lynx -source http://www.example.com/cgi-bin/php/report.php

Conclusion

Unlike proprietary commercial solutions Drupal's openness lends itself to infinite extensibility. The database it creates contains a wealth of information. This data is especially critical to a business oriented website. The simple example script here can be expanded to email automated, regularly scheduled reports. Beyond plain text emails you can create graphical reports based on live data as standalone pages that can be viewed on demand.

Text Messaging Charges

Keep in mind the toll this will take on your text messaging plan. Some carriers used to allow free incoming messages. This is a long gone tradition. These messages can add up fast. For example 5 messages every morning for 1 month totals 150 messages/month. Make sure your plan can handle this. If you prefer, combine as many stats into a single SMS as you can to minimize usage against your monthly allotment. Per agreed upon standards an SMS may contain 160 characters. There are some exception depending on device and carrier. Howstuffworks.com has an easy to read explanation of the SMS protocol.