Analysing your Drupal Watchdog for Spammers

mollom-watchdog

/**
 * Get a list of Mollom entries from the Drupal Watchdog 
 * summarised by IP address 
 */
SELECT hostname, count(wid)
FROM `watchdog`
where type = 'mollom' and message like 'Spam%'
group by hostname
order by count(wid) desc

mollom-watchdog-count-per-day-for-ip

/**
 * Get a summary of Mollom spam events for a given IP by day from the Drupal Watchdog 
 */
select FROM_UNIXTIME(timestamp, '%Y %M %d'), count(*)
from `watchdog`
where hostname = '<put an IP in here>'
group by FROM_UNIXTIME(timestamp, '%Y %M %d')
order by FROM_UNIXTIME(timestamp, '%Y %M %d')

mollom-watchdog-summary

/**
 * Get a summary of Mollom spam events by day from the Drupal Watchdog 
 */
select DATE_FORMAT(FROM_UNIXTIME(timestamp) , '%Y-%m-%d') as day, count(*)
 from `watchdog` 
 where message like 'Spam%'
 group by DATE_FORMAT(FROM_UNIXTIME(timestamp) , '%Y-%m-%d')
 order by DATE_FORMAT(FROM_UNIXTIME(timestamp) , '%Y-%m-%d')