There and Back Again

More Spam Fun

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

Spam in the last 24

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`)

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

Find the line in akismet that updates the global spam count

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:

                /* 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:

// uses PEAR 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


        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')),

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

// create the dataset
$Dataset_all =& Image_Graph::factory('dataset');
foreach($data as $row) {

$Dataset_all =& Image_Graph::factory('dataset');
foreach($data as $row) {

// 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->setTitle('24 Spam');

// format the axis

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

// output the Graph

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.

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.

One thought on “More Spam Fun