2 SELECT count(*) AS Jobs, sum(JobFiles) AS Files,
3 sum(JobBytes) AS Bytes, Name AS Job FROM Job GROUP BY Name;
4 SELECT max(JobId) AS Jobs,sum(JobFiles) AS Files,
5 sum(JobBytes) As Bytes FROM Job
7 :List where a file is saved:
8 *Enter path with trailing slash:
11 SELECT Job.JobId, StartTime AS JobStartTime, VolumeName, Client.Name AS ClientName
12 FROM Job,File,Path,Filename,Media,JobMedia,Client
13 WHERE File.JobId=Job.JobId
15 AND Filename.Name="%2"
17 AND Path.PathId=File.PathId
18 AND Filename.FilenameId=File.FilenameId
19 AND JobMedia.JobId=Job.JobId
20 AND JobMedia.MediaId=Media.MediaId
21 AND Client.ClientId=Job.ClientId
24 :List where the most recent copies of a file are saved:
25 *Enter path with trailing slash:
28 SELECT Job.JobId, StartTime AS JobStartTime, VolumeName, Client.Name AS ClientName
29 FROM Job,File,Path,Filename,Media,JobMedia,Client
30 WHERE File.JobId=Job.JobId
32 AND Filename.Name="%2"
34 AND Path.PathId=File.PathId
35 AND Filename.FilenameId=File.FilenameId
36 AND JobMedia.JobId=Job.JobId
37 AND JobMedia.MediaId=Media.MediaId
38 AND Client.ClientId=Job.ClientId
39 ORDER BY Job.StartTime DESC LIMIT 5;
41 :List total files/bytes by Job:
42 SELECT count(*) AS Jobs, sum(JobFiles) AS Files,
43 sum(JobBytes) AS Bytes, Name AS Job
44 FROM Job GROUP by Name
46 :List total files/bytes by Volume:
47 SELECT count(*) AS Jobs, sum(JobFiles) AS Files,
48 sum(JobBytes) AS Bytes, VolumeName
49 FROM Job,JobMedia,Media
50 WHERE JobMedia.JobId=Job.JobId
51 AND JobMedia.MediaId=Media.MediaId
54 :List last 20 Full Backups for a Client:
56 Select Job.JobId,Client.Name as Client,StartTime,JobFiles,JobBytes,
57 JobMedia.StartFile as VolFile, VolumeName
58 FROM Client,Job,JobMedia,Media
59 WHERE Client.Name="%1"
60 AND Client.ClientId=Job.ClientId
61 AND Level='F' AND JobStatus='T'
62 AND JobMedia.JobId=Job.JobId AND JobMedia.MediaId=Media.MediaId
63 ORDER BY JobId DESC LIMIT 20;
65 :List Volumes used by selected JobId:
67 SELECT Job.JobId,VolumeName
68 FROM Job,JobMedia,Media
70 AND Job.JobId=JobMedia.JobId
71 AND JobMedia.MediaId=Media.MediaId;
73 :List Volumes to Restore All Files:
77 CREATE TABLE temp (JobId INTEGER UNSIGNED NOT NULL,
78 JobTDate BIGINT UNSIGNED,
79 ClientId INTEGER UNSIGNED,
83 StartFile INTEGER UNSIGNED,
84 VolSessionId INTEGER UNSIGNED,
85 VolSessionTime INTEGER UNSIGNED);
86 CREATE TABLE temp2 (JobId INTEGER UNSIGNED NOT NULL,
90 StartFile INTEGER UNSIGNED,
91 VolSessionId INTEGER UNSIGNED,
92 VolSessionTime INTEGER UNSIGNED);
93 # Select last Full save
94 INSERT INTO temp SELECT Job.JobId,JobTDate,Job.ClientId,Job.Level,
95 StartTime,VolumeName,JobMedia.StartFile,VolSessionId,VolSessionTime
96 FROM Client,Job,JobMedia,Media WHERE Client.Name="%1"
97 AND Client.ClientId=Job.ClientId
98 AND Level='F' AND JobStatus='T'
99 AND JobMedia.JobId=Job.JobId
100 AND JobMedia.MediaId=Media.MediaId
101 ORDER BY Job.JobTDate DESC LIMIT 1;
103 INSERT INTO temp2 SELECT JobId,StartTime,VolumeName,Level,StartFile,
104 VolSessionId,VolSessionTime
106 # Now add subsequent incrementals
107 INSERT INTO temp2 SELECT Job.JobId,Job.StartTime,Media.VolumeName,
108 Job.Level,JobMedia.StartFile,Job.VolSessionId,Job.VolSessionTime
109 FROM Job,temp,JobMedia,Media
110 WHERE Job.JobTDate>temp.JobTDate
111 AND Job.ClientId=temp.ClientId
112 AND Job.Level='I' AND JobStatus='T'
113 AND JobMedia.JobId=Job.JobId
114 AND JobMedia.MediaId=Media.MediaId
121 :List where a File is saved:
122 *Enter Filename (no path):
123 SELECT Job.JobId as JobId, Client.Name as Client,
124 Path.Path||Filename.Name as Name,
125 StartTime,Level,JobFiles,JobBytes
126 FROM Client,Job,File,Filename,Path WHERE Client.ClientId=Job.ClientId
127 AND JobStatus='T' AND Job.JobId=File.JobId
128 AND Path.PathId=File.PathId AND Filename.FilenameId=File.FilenameId
129 AND Filename.Name='%1' LIMIT 20;