Akismet spam graphs with PHP RRD

After reading a post on hacking Akismet to add graphs I decided I liked the idea but I didn’t want to store the data in a database. It seemed like it would be better to store it using a RRD and then use the PHP RRD library. So after a little hacking I’ve created a version that does basically the same thing except uses a RRD.

A good place to start is the PHP RRDTool tutorial. It will make it easier to read the following code if you have an idea of how to use the RRDTool extension.

All of the following changes should be made in the plugins/akismet directory.

The first part of the code is akismet_rrd.php that adds a utility function for updating the RRD file. It will create the file if it doesn’t already exist. To gather the stats on incoming spam it uses a timestamp of the last time it ran and then queries WordPress’s comment table for anything marked as spam in increments that match the step used to set up the RRD. The update function can be called in a number of locations but the most efficient seems to be right before any deletes happen.

[code lang=”php”]
AKISMET_RRD_TS )
{
update_option( ‘akismet_stat_last_update’, ($current_time – (($time_diff – AKISMET_RRD_TS) % AKISMET_RRD_TS)) );

akismet_create_rrd();

global $wpdb;

while( $last_update < $current_time - AKISMET_RRD_TS ) { $data = $wpdb->get_row(“SELECT COUNT(1) as spam_count FROM $wpdb->comments WHERE comment_date_gmt BETWEEN FROM_UNIXTIME(” . $last_update . “) AND FROM_UNIXTIME(” . ($last_update + AKISMET_RRD_TS) . “) AND comment_approved = ‘spam'”);
$spam_count = 0;
if($data)
{
$spam_count = $data->spam_count;
}

rrd_update(AKISMET_RRD_FILE, ($last_update + (get_settings(‘gmt_offset’) * 3600)) . “:$spam_count”);

$last_update += AKISMET_RRD_TS;
}
}
}

function akismet_create_rrd()
{
if( !file_exists(AKISMET_RRD_FILE) )
{
$opts = array( “–step”, AKISMET_RRD_TS, “–start”, 0,
“DS:input:ABSOLUTE:600:0:100000”,
“RRA:AVERAGE:0.5:1:600”,
“RRA:AVERAGE:0.5:6:700”,
“RRA:AVERAGE:0.5:24:775”,
“RRA:AVERAGE:0.5:288:797”,
“RRA:MAX:0.5:1:600”,
“RRA:MAX:0.5:6:700”,
“RRA:MAX:0.5:24:775”,
“RRA:MAX:0.5:288:797″
);

rrd_create(AKISMET_RRD_FILE, $opts, count($opts));
}
}

?>
[/code]

The next step is the code to create graphs from the RRD in the file akismet_graph.php. This code registers the menu for WordPress in the Dashboard area and when displayed will generate the graphs for a day, month and year.

[code lang=”php”]



One downside to the way this setup works is that you can miss some data if you delete quicker than the step time. I figured that was ok since the graphs are averages, the step is only 5 minutes and most people probably don’t purge their spam that quickly.

[tags]php, akismet, rrd[/tags]

One thought on “Akismet spam graphs with PHP RRD

Comments are closed.