Rev 50 | Blame | Compare with Previous | Last modification | View Log | Download | RSS feed
drop table if exists backups_server;
create table backups_server (
backups_server_id int unsigned not null auto_increment,
device_id int unsigned not null references device (device_id),
backup_space bigint comment 'size in bytes available for backups',
primary key (backups_server_id)
) comment 'Just a linking table to available backup servers';
drop table if exists backups;
create table backups (
backups_id int unsigned not null auto_increment,
device_id int unsigned not null references device (device_id),
backups_server_id int unsigned not null references backup_server (backups_server_id),
allocated_size bigint default 1073741824 comment 'number of bytes client is paying for',
start_time time comment 'default start time of backup',
notes text comment 'any notes we want to keep on this',
bandwidth_allocated int unsigned comment 'amount of bandwidth in megabits/s',
responsible_party varchar(64) comment 'email address of who to notify for problems',
added_date date,
removed_date date default null,
primary key (backups_id)
) comment 'stores information on backup jobs allocated';
DROP TABLE IF EXISTS backups_run;
CREATE TABLE backups_run (
backups_run_id int unsigned not null auto_increment,
backups_id int unsigned not null references backups( backups_id ),
report_date datetime not null comment 'Date on the actual log file as part of its filename',
start_time datetime NOT NULL default '0000-00-00 00:00:00' comment 'when job started',
version varchar(10) comment 'Version of rsbackup creating processing this run',
end_time datetime NOT NULL default '0000-00-00 00:00:00' comment 'when job ended',
files_count bigint comment 'total number of files to be checked',
files_size bigint comment 'size of all files to be checked in bytes',
transferred_count bigint comment 'number of files transferred',
transferred_size bigint comment 'size of files transferred in bytes',
files_deleted bigint comment 'number of files which were deleted',
data_sent bigint comment 'bytes sent to server',
data_received bigint comment 'bytes received from server',
PRIMARY KEY (backups_run_id)
) comment 'added for each backup that runs';
DROP TABLE IF EXISTS backups_usage;
CREATE TABLE backups_usage (
backups_usage_id int unsigned not null auto_increment,
report_date date NOT NULL default '0000-00-00',
backups_id int unsigned not null references backups (backups_id),
disk_usage bigint comment 'bytes used on backup server',
num_files bigint comment 'number of files on backup server',
num_dirs bigint comment 'number of directories on backup server',
PRIMARY KEY (backups_usage_id)
) comment 'periodic reports from backup server itself on space used';
insert into _system ( group_name,key_name,theValue,added_date) values ( 'Modules', 'Backup', 'modules/backup/', now() );
/* Reports */
insert into report ( name, query, parameters ) values ('Active Backups','select device.name Device,
backupserver.name Server,
floor(allocated_size/1024/1024/1024) \"Max Size (G)\",
concat(round(allocated_size / backup_space * 100), \'%\') \"%\",
start_time Starts
from backups join backups_server on backups.backups_server_id = backups_server.backups_server_id
join device backupserver on backups_server.device_id = backupserver.device_id
join device on backups.device_id = device.device_id','');
insert into report ( name, query, parameters ) values ('Device Backup History','select device.name Device,
backups_run.start_time Started,
timediff(backups_run.end_time,backups_run.start_time) Duration,
lpad(floor((data_sent+data_received) / 1024 / 1024), 10, \' \') \"M Xfer\"
from backups join backups_run using (backups_id)
join device using (device_id)
where device.device_id = <device_id>
and backups_run.start_time >= <start_date>
and backups_run.start_time < <end_date>
order by device.name,backups_run.start_time','device_id++Device++select device_id,device.name from device where device_id in (select device_id from backups) order by device.name++select device.name from device where device_id = <value>
start_date++Starting Date++++
end_date++Ending Date++++');
insert into report ( name, query, parameters ) values ('Backup Stats','select device.name Device,
max( timediff(backups_run.end_time,backups_run.start_time) ) Longest,
min( timediff(backups_run.end_time,backups_run.start_time) ) Shortest,
avg( TIME_TO_SEC(backups_run.end_time)-TIME_TO_SEC(backups_run.start_time) ) AvgTime,
lpad(format( max((data_sent+data_received) / 1024 / 1024), 0 ),10,\' \') Largest,
lpad(format( min((data_sent+data_received) / 1024 / 1024), 0 ),10,\' \') Smallest,
lpad(format(avg( (data_sent+data_received) / 1024 / 1024), 0 ),10,\' \') AvgXFer,
count(*) Num,
stddev((data_sent+data_received)) StdDev,
var_pop((data_sent+data_received)) Var
from backups join backups_run using (backups_id)
join device using (device_id)
group by device.name','');
insert into report ( name, query, parameters ) values ('Monthly Backup Transfer','select device.name Device,
concat(year(backups_run.start_time), \'-\', lpad(month(backups_run.start_time),2,\'0\')) Period,
lpad(format(sum(data_sent+data_received)/1024/1024/1024,0),10,\' \') \'Transfer\',
lpad(format((sum(data_sent+data_received)- (allocated_size * 0.01))/1024/1024/1024,0),10,\' \') \'Overage\'
from backups join backups_run using (backups_id)
join device using (device_id)
where month(backups_run.start_time) = <month> and year(backups_run.start_time) = <year>
group by device.name, concat(year(backups_run.start_time), \'-\', month(backups_run.start_time))
order by device.name, concat(year(backups_run.start_time), \'-\', month(backups_run.start_time))','month++Report Month (number)++++
year++Report Year (4 digit)++++');
insert into report ( name, query, parameters ) values ('Client Monthly Transfer History','select device.name Device,
concat(year(backups_run.start_time), \'-\', lpad(month(backups_run.start_time),2,\'0\')) Period,
lpad(format(sum(data_sent+data_received)/1024/1024/1024,0),10,\' \') \'Transfer\',
lpad(format((sum(data_sent+data_received)- (allocated_size * 0.01))/1024/1024/1024,0),10,\' \') \'Overage\'
from backups join backups_run using (backups_id)
join device using (device_id)
where device.device_id = <device_id>
group by device.name, concat(year(backups_run.start_time), \'-\', month(backups_run.start_time))
order by device.name, concat(year(backups_run.start_time), \'-\', month(backups_run.start_time))','device_id++Computer++select device_id,device.name from device where device_id in (select device_id from backups) order by device.name++select name from device where device_id = <value>');
/* last <count> backups recorded */
insert into report ( name, query, parameters ) values ('Backup History','select
backups_run.start_time Started,
backups_run.end_time Ended,
backups_run.version Version,
format(backups_run.files_count,0) Files,
format(backups_run.files_size/1024/1024,0) \'Size (M)\',
format(backups_run.transferred_count,0) Transfer,
format(backups_run.skipped,0) Skipped,
format(backups_run.files_deleted,0) Deleted,
format(backups_run.data_sent/1024/1024,2) \'MB Out\',
format(backups_run.data_received/1024/1024,2) \'MB In\',
backups_run.disk_used Disk
from
backups_run join backups using (backups_id)
where
backups.device_id = <device_id>
order by
backups_run.report_date desc
limit <count>','device_id++Device ID++++
count++Number of Reports++++');
/* This has a menu entry, so add it */
/* first, make sure we don't have any danglies */
/* create three menu options. First one is the main menu option (ie, no parent_id) */
insert into menu( url, caption, parent_id) values ('/modules/backup/', 'Backups', null);