More Munin Monitoring Madness

Another quick Munin monitor script, this time for drupal 6 (since it works on the database itself, YMMV and who knows if it works on drupal7.)

I wanted to be able to get a quick view of the action on the drupal site, so I whipped up a munin plugin to graph total hits and unique hits on the site with a couple SQL queries. The result:

The code:

#!/bin/bash
# count drupal node hits and unique visitors in log in past 24 hours

case $1 in
   config)
        cat <<'EOM'
graph_title Drupal Site hits
graph_vlabel hits
graph_category drupal
graph_info This graph shows the count of drupal node accesses
hits.label hits
hits.info page hits per day
unique.label unique hits
unique.info unique visitors past 24 hours
EOM
        exit 0;;
esac

i=`mysql -u munin --password=whatever  --column-names=0 --execute "use drupal; 
  select sum(daycount) from node_counter where daycount>0"`
j=`mysql -u munin --password=whatever  --column-names=0 --execute "use drupal; 
  select count(*) from (select * from accesslog 
  where (timestamp > (UNIX_TIMESTAMP(now()-interval 1 day))) group by hostname) as t;"`
echo hits.value $i
echo unique.value $j

The two mysql commands generate the values we graph. You'll want to possibly create an SQL user just for this task (munin/whatever is what's in the script, I suggest you don't use whatever as the password… I sure don't!) with minimal permissions only for the needed database/tables. 

The first SQL command just sums up the drupal-supplied hit counts for the current day for each node. This is great as a raw count of hits, but repeated hits from the same visitor (your friendly neighborhood search robot) really skew this number. The second SQL command generates unique visitors (by IP address) from the log, over the past 24 hours. and is immune from spam/worm/crawler spikes.

Enjoy!