This page contains a discussion paper on a flexible aggregate stats (i.e. web, ftp, quota usage) reporter.

--

Overview

I want to create a MySQL backend web-based aggregate stats reporter. Be able to report disk usage, http and ftp usage, mysql database usage and have data correlated per-domain and per-user account.

Details

• http usage is tracked per domain
• ftp usage is tracker per user
• disk usage is tracked per user
• database sizes are tracked per db
• multiple users can have the same domain for ftp/web
• multiple users can have the same mysql database
• a user can have multiple ftp/web domains
• a user can have multiple mysql databases

Final reporting should look something like this:

Month	User	Host	Web usage	FTP usage	MySQL database	Disk usage	Total bandwidth used
2008-04	mike	ALL (pachogrande.com, common-host.com)	15.1 GB	512 MB	3, 112 MB	50 MB, 33%	20.1 GB
2008-04	jon	ALL (sullex.com)	45 MB	1 MB	0, 0 MB	10 MB, 6.6 MB	46 MB

DB schema

Config table := userid, type, data
userid := varchar(32), i.e. 'mike'
type := { 'web' VHOST, 'ftp', 'mysql' DATABASENAME, 'mysql_capacity' DISKUSAGE, 'disk_capacity' DISKUSAGE, 'total_bandwidth_capacity' BANDUSAGE }
VHOST := varchar(32), i.e. 'pachogrande.com'
DATABASENAME := varchar(32), i.e. mikedatabase
DISKUSAGE := integer 'bytes'
BANDUSAGE := integer bytes

VHOST table := vhost, date, bandwidth

FTP table := userid, date, diskusage

DATABASE table := databasename, date, diskusage

DISK table := userid, date, diskusage

DB Queries

List of web vhosts for user: SELECT * FROM config WHERE type=web AND userid=%s
List of databases for user: SELECT * FROM config WHERE type=mysql and userid=%s
Disk capacity for user: SELECT * FROM config WHERE type=disk_capacity and userid=%s
MySQL capacity for user: SELECT * FROM config WHERE type=mysql_capacity and userid=%s
Total bandwidth capacity for user: SELECT * FROM config WHERE type=total_bandwidth_capacity AND userid=%s

Web traffic: SELECT * FROM VHOST WHERE vhost=%s AND date
Total web traffic: SELECT SUM(bandwidth), COUNT(*) FROM VHOST WHERE (vhost=%s AND vhost=%s ...) AND date GROUP BY date

FTP traffic: SELECT * FROM FTP WHERE userid=%s AND date

Database usage: SELECT * FROM DATABASE WHERE databasename=%s AND DATE
Total database usage: SELECT SUM(diskusage), COUNT(*) FROM DATABASE WHERE (databasename=%s AND databasename=%s ...) AND date GROUP BY date

Disk usage: SELECT * FROM DISK WHERE userid=%s AND date

Total traffiic: Total web bandwidth + Total FTP traffic

Getting data for each query

1. Web vhost - perl script to parse awstats report
2. FTP vhost - perl script to parse awstats ftp report
3. Database usage - perl script that runs du
4. Disk usage - perl script that runs repquota -a

MySQL schema

-- 
-- Table structure for table `config`
-- 

CREATE TABLE `config` (
  `config_id` int(10) unsigned NOT NULL,
  `config_userid` varchar(32) NOT NULL,
  `config_type` enum('web','ftp','mysql','mysql_capacity','disk_capacity','total_bandwidth_capacity') NOT NULL,
  `config_data` varchar(255) NOT NULL,
  PRIMARY KEY  (`config_id`),
  KEY `config_userid` (`config_userid`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

-- --------------------------------------------------------

-- 
-- Table structure for table `disk`
-- 

CREATE TABLE `disk` (
  `disk_id` int(10) unsigned NOT NULL,
  `disk_userid` varchar(32) NOT NULL,
  `disk_date` varchar(255) NOT NULL,
  `disk_diskusage` int(11) NOT NULL,
  PRIMARY KEY  (`disk_id`),
  UNIQUE KEY `disk_userid` (`disk_userid`),
  KEY `disk_date` (`disk_date`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

-- --------------------------------------------------------

-- 
-- Table structure for table `ftp`
-- 

CREATE TABLE `ftp` (
  `ftp_id` int(10) unsigned NOT NULL,
  `ftp_userid` varchar(32) NOT NULL,
  `ftp_date` varchar(255) NOT NULL,
  `ftp_bandwidth` int(11) NOT NULL,
  PRIMARY KEY  (`ftp_id`),
  KEY `ftp_userid` (`ftp_userid`),
  KEY `ftp_date` (`ftp_date`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

-- --------------------------------------------------------

-- 
-- Table structure for table `mysqldb`
-- 

CREATE TABLE `mysqldb` (
  `mysqldb_id` int(10) unsigned NOT NULL,
  `mysqldb_mysqldbname` varchar(255) NOT NULL,
  `mysqldb_date` varchar(255) NOT NULL,
  `datebase_diskusage` int(11) NOT NULL,
  PRIMARY KEY  (`mysqldb_id`),
  KEY `mysqldb_mysqldbname` (`mysqldb_mysqldbname`),
  KEY `mysqldb_date` (`mysqldb_date`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

-- --------------------------------------------------------

-- 
-- Table structure for table `vhost`
-- 

CREATE TABLE `vhost` (
  `vhost_id` int(10) unsigned NOT NULL,
  `vhost_vhost` varchar(255) NOT NULL,
  `vhost_date` varchar(255) NOT NULL,
  `vhost_bandwidth` int(11) NOT NULL,
  PRIMARY KEY  (`vhost_id`),
  KEY `vhost_vhost` (`vhost_vhost`),
  KEY `vhost_date` (`vhost_date`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;