Subversion Repositories computer_asset_manager_v1

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',

   responsible_party varchar(64) comment 'email address of who to notify for problems',

   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,screen_report ) values ('New Report','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++++',1);