There and Back Again

I hacked akismet a couple days ago to give me hourly spam statistics and then threw together a graph.

Looks like the evil spammers were at it again this morning.

The Code

Create a new table to hold the stats:

CREATE TABLE `spam_count` (
  `day` date NOT NULL default '0000-00-00',
  `hour` tinyint(4) NOT NULL default '0',
  `count` int(11) NOT NULL default '0',
  PRIMARY KEY  (`day`,`hour`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

Find your akismet plugin [blog root]/wp-content/plugins/akismet/akistmet.php

Find the line in akismet that updates the global spam count

<?php

update_option( 'akismet_spam_count', get_option('akismet_spam_count') + 1 );

?>

On my version of akismet thats line 139, in the ksd_auto_check_comment function

below that line add:

<?php

                /* Hourly Spam Counting Start */
                $today = date('Y-m-d');
                $hour = date('H');
                global $wpdb;
                $wpdb->query("insert into spam_count (`day`,`hour`,`count`) values('$today',$hour,1) on duplicate key update `count`=`count`+1");
                /* Hourly Spam Counting End */

?>

Then post some comments to test things, make sure your logged out and post something spammy. Then check the spam_count table and make sure its got a new row in it. If so then things are working right.

Now you have the data so the next step is too add some graphs. I use PEAR’s Image_Graph so install it.

Something like the command below might work:

pear install --alldeps Image_Graph-beta


Then make a graph the code for mine is:

<?php

// uses PEAR Image_Graph http://pear.php.net/Image_Graph
require_once 'Image/Graph.php';

require_once( dirname(__FILE__) . '/wp-config.php');

$day = date('Y-m-d',strtotime('-24 hours'));
$today = date('Y-m-d');
$hour = date('H',strtotime('-24 hours'));

$sql = "select `hour`, count from spam_count where day = '$today' or (day = '$day' and hour > $hour)";
$data = $wpdb->get_results($sql ,ARRAY_A);

$month = date('F');

// create the graph
$Graph =& Image_Graph::factory('graph', array(300, 300));
// add a TrueType font
//$Font =& $Graph->addNew('font', 'Verdana');
$Font =& $Graph->addNew('font', '/usr/share/fonts/dejavu/DejaVuSans.ttf');
// set the font size to 11 pixels
$Font->setSize(8);

$Graph->setFont($Font);

$Graph->add(
    Image_Graph::vertical(
        Image_Graph::factory('title', array("Blog Spams in the Last 24 Hours", 12)),
        $Plotarea = Image_Graph::factory('plotarea'),
            //$Plotarea = Image_Graph::factory('plotarea',array('axis', 'axis')),
        7
    )
);

$colors = array('red','green','blue','orange','gray','purple');

// create the dataset
$Dataset_all =& Image_Graph::factory('dataset');
foreach($data as $row) {
        $Dataset_all->addPoint($row['hour'],$row['count']);
}

$Dataset_all =& Image_Graph::factory('dataset');
foreach($data as $row) {
        $Dataset_all->addPoint($row['hour'],$row['count']);
}

// create the 1st plot as smoothed area chart using the 1st dataset
$Plot_all =& $Plotarea->addNew('Image_Graph_Plot_Smoothed_Area', array(&$Dataset_all));

$Plot_all->setLineColor($colors[5]);
$Plot_all->setFillColor("$colors[2]@0.2");
$Plot_all->setTitle('24 Spam');

// format the axis

$Processor =& Image_Graph::factory('Image_Graph_DataPreprocessor_Function', 'formatTime');
$AxisX =& $Plotarea->getAxis(IMAGE_GRAPH_AXIS_X);
$AxisX->setDataPreprocessor($Processor);
$AxisX->setLabelInterval(3);

// output the Graph
$Graph->done();

function formatTime($hour) {
        $hour = (int)$hour;
        $m = 'AM';
        if ($hour > 12) {
                $hour = $hour-12;
                $m = 'PM';
        }
        return "$hour$m";
}

?>

The code for the graph is a little more complicated then needed since its just a hacked up version of the graphs i make for WebThumb. Anyhow then its just a matter of putting your graph script in the src of an image.

<img src="/graph-spam-last24.php"/>

Oh and you might want to add some caching if your showing the graph all the time since generating pngs is a bit more expensive then generating html.

This entry was posted on Thursday, December 21st, 2006 at 10:13 am and is filed under PHP, pear. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.