Subversion Repositories computer_asset_manager_v1

Rev

Rev 38 | Go to most recent revision | Details | Compare with Previous | Last modification | View Log | RSS feed

Rev Author Line No. Line
1 rodolico 1
drop table if exists backups_server;
2
create table backups_server (
3
   backups_server_id int unsigned not null auto_increment,
4
   device_id         int unsigned not null references device (device_id),
5
   backup_space      bigint comment 'size in bytes available for backups',
6
   primary key       (backups_server_id)
7
) comment 'Just a linking table to available backup servers';
8
 
9
drop table if exists backups;
10
create table backups (
11
   backups_id        int unsigned not null auto_increment,
12
   device_id         int unsigned not null references device (device_id),
13
   backups_server_id  int unsigned not null references backup_server (backups_server_id),
14
   allocated_size    bigint default 1073741824 comment 'number of bytes client is paying for',
15
   start_time        time comment 'default start time of backup',
16
   notes             text comment 'any notes we want to keep on this',
17
   responsible_party varchar(64) comment 'email address of who to notify for problems',
18
   primary key       (backups_id)
19
) comment 'stores information on backup jobs allocated';
20
 
21
DROP TABLE IF EXISTS backups_run;
22
CREATE TABLE backups_run (
23
  backups_run_id     int unsigned not null auto_increment,
24
  backups_id         int unsigned not null references backups( backups_id ),
25
  report_date        datetime not null comment 'Date on the actual log file as part of its filename',
26
  start_time         datetime NOT NULL default '0000-00-00 00:00:00' comment 'when job started',
27
  version            varchar(10) comment 'Version of rsbackup creating processing this run',
28
  end_time           datetime NOT NULL default '0000-00-00 00:00:00' comment 'when job ended',
29
  files_count        bigint comment 'total number of files to be checked',
30
  files_size         bigint comment 'size of all files to be checked in bytes',
31
  transferred_count  bigint comment 'number of files transferred',
32
  transferred_size   bigint comment 'size of files transferred in bytes',
33
  files_deleted      bigint comment 'number of files which were deleted',
34
  data_sent          bigint comment 'bytes sent to server',
35
  data_received      bigint comment 'bytes received from server',
36
  PRIMARY KEY        (backups_run_id)
37
) comment 'added for each backup that runs';
38
 
39
 
40
DROP TABLE IF EXISTS backups_usage;
41
CREATE TABLE backups_usage (
42
  backups_usage_id   int unsigned not null auto_increment,
43
  report_date        date NOT NULL default '0000-00-00',
44
  backups_id         int unsigned not null references backups (backups_id),
45
  disk_usage         bigint comment 'bytes used on backup server',
46
  num_files          bigint comment 'number of files on backup server',
47
  num_dirs           bigint comment 'number of directories on backup server',
48
  PRIMARY KEY        (backups_usage_id)
49
) comment 'periodic reports from backup server itself on space used';
50
 
51
insert into _system ( group_name,key_name,theValue,added_date) values ( 'Modules', 'Backup', 'modules/backup/', now() );
52
 
53
/* Reports */
54
insert into report ( name, query, parameters ) values ('Active Backups','select device.name Device, 
55
       backupserver.name Server, 
56
       floor(allocated_size/1024/1024/1024) \"Max Size (G)\", 
57
       concat(round(allocated_size / backup_space * 100), \'%\') \"%\",
58
       start_time Starts
59
from backups join backups_server on backups.backups_server_id = backups_server.backups_server_id
60
     join device backupserver on backups_server.device_id = backupserver.device_id
61
     join device on backups.device_id = device.device_id','');
62
 
63
insert into report ( name, query, parameters ) values ('Device Backup History','select device.name Device, 
64
       backups_run.start_time Started,
65
       timediff(backups_run.end_time,backups_run.start_time) Duration,
66
       lpad(floor((data_sent+data_received) / 1024 / 1024), 10, \' \') \"M Xfer\"
67
from backups join backups_run using (backups_id)
68
     join device using (device_id)
69
where device.device_id = <device_id>
70
      and backups_run.start_time >= <start_date>
71
      and backups_run.start_time < <end_date>
72
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>
73
start_date++Starting Date++++
74
end_date++Ending Date++++');
75
 
76
insert into report ( name, query, parameters ) values ('Backup Stats','select device.name Device,
77
       max( timediff(backups_run.end_time,backups_run.start_time) ) Longest,
78
       min( timediff(backups_run.end_time,backups_run.start_time) ) Shortest,
79
       avg( TIME_TO_SEC(backups_run.end_time)-TIME_TO_SEC(backups_run.start_time) ) AvgTime,
80
       lpad(format( max((data_sent+data_received) / 1024 / 1024), 0 ),10,\' \') Largest,
81
       lpad(format( min((data_sent+data_received) / 1024 / 1024), 0 ),10,\' \') Smallest,
82
       lpad(format(avg( (data_sent+data_received) / 1024 / 1024), 0 ),10,\' \') AvgXFer,
83
       count(*) Num,
84
       stddev((data_sent+data_received)) StdDev,
85
       var_pop((data_sent+data_received)) Var
86
from backups join backups_run using (backups_id)
87
     join device using (device_id)
88
group by device.name','');
89
 
90
insert into report ( name, query, parameters ) values ('Monthly Backup Transfer','select device.name Device, 
91
       concat(year(backups_run.start_time), \'-\', lpad(month(backups_run.start_time),2,\'0\')) Period,
92
       lpad(format(sum(data_sent+data_received)/1024/1024/1024,0),10,\' \') \'Transfer\',
93
       lpad(format((sum(data_sent+data_received)- (allocated_size * 0.01))/1024/1024/1024,0),10,\' \') \'Overage\'
94
from backups join backups_run using (backups_id)
95
     join device using (device_id)
96
where month(backups_run.start_time) = <month> and year(backups_run.start_time) = <year>
97
group by device.name, concat(year(backups_run.start_time), \'-\', month(backups_run.start_time))
98
order by device.name, concat(year(backups_run.start_time), \'-\', month(backups_run.start_time))','month++Report Month (number)++++
99
year++Report Year (4 digit)++++');
100
 
101
 
102
insert into report ( name, query, parameters ) values ('Client Monthly Transfer History','select device.name Device, 
103
       concat(year(backups_run.start_time), \'-\', lpad(month(backups_run.start_time),2,\'0\')) Period,
104
       lpad(format(sum(data_sent+data_received)/1024/1024/1024,0),10,\' \') \'Transfer\',
105
       lpad(format((sum(data_sent+data_received)- (allocated_size * 0.01))/1024/1024/1024,0),10,\' \') \'Overage\'
106
from backups join backups_run using (backups_id)
107
     join device using (device_id)
108
where device.device_id = <device_id>
109
group by device.name, concat(year(backups_run.start_time), \'-\', month(backups_run.start_time))
110
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>');
38 rodolico 111
 
112
/* last <count> backups recorded */
113
 
50 rodolico 114
insert into report ( name, query, parameters ) values ('Backup History','select
38 rodolico 115
   backups_run.start_time Started,
116
   backups_run.end_time Ended,
117
   backups_run.version Version,
118
   format(backups_run.files_count,0) Files,
119
   format(backups_run.files_size/1024/1024,0) \'Size (M)\',
120
   format(backups_run.transferred_count,0) Transfer,
121
   format(backups_run.skipped,0) Skipped,
122
   format(backups_run.files_deleted,0) Deleted,
123
   format(backups_run.data_sent/1024/1024,2) \'MB Out\',
124
   format(backups_run.data_received/1024/1024,2) \'MB In\',
125
   backups_run.disk_used Disk
126
from
127
   backups_run join backups using (backups_id)
128
where
129
   backups.device_id = <device_id>
130
order by
131
   backups_run.report_date desc
132
limit <count>','device_id++Device ID++++
50 rodolico 133
count++Number of Reports++++');