2 # This file contains sample queries that you can possibly use in
3 # your query.sql file. However, please note that these examples
4 # are not supported. They may or may not work -- in fact, they may
5 # work with one SQL engine and not another. As a consequence,
6 # please do not submit bug reports on either query.sql or
9 # If you find that they work for MySQL and not for PostgreSQL,
10 # then please send us a whole new file corrected for PostgreSQL
11 # and we will post it in the examples directory for everyone
16 :List up to 20 places where a File is saved regardless of the directory
17 *Enter Filename (no path):
18 SELECT DISTINCT Job.JobId as JobId, Client.Name as Client,
19 Path.Path,Filename.Name,StartTime,Level,JobFiles,JobBytes
20 FROM Client,Job,File,Filename,Path WHERE Client.ClientId=Job.ClientId
21 AND JobStatus='T' AND Job.JobId=File.JobId
22 AND Path.PathId=File.PathId AND Filename.FilenameId=File.FilenameId
23 AND Filename.Name='%1'
24 ORDER BY Job.StartTime LIMIT 20;
26 :List where the most recent copies of a file are saved
27 *Enter path with trailing slash:
30 SELECT DISTINCT Job.JobId,StartTime AS JobStartTime,VolumeName,Client.Name AS ClientName
31 FROM Job,File,Path,Filename,Media,JobMedia,Client
32 WHERE File.JobId=Job.JobId
34 AND Filename.Name='%2'
36 AND Path.PathId=File.PathId
37 AND Filename.FilenameId=File.FilenameId
38 AND JobMedia.JobId=Job.JobId
39 AND JobMedia.MediaId=Media.MediaId
40 AND Client.ClientId=Job.ClientId
41 ORDER BY Job.StartTime DESC LIMIT 5;
43 :List last 20 Full Backups for a Client
45 SELECT DISTINCT Job.JobId,Client.Name AS Client,StartTime,JobFiles,JobBytes,
46 JobMedia.StartFile as VolFile,VolumeName
47 FROM Client,Job,JobMedia,Media
48 WHERE Client.Name='%1'
49 AND Client.ClientId=Job.ClientId
50 AND Level='F' AND JobStatus='T'
51 AND JobMedia.JobId=Job.JobId AND JobMedia.MediaId=Media.MediaId
52 ORDER BY Job.StartTime DESC LIMIT 20;
54 :List all backups for a Client after a specified time
56 *Enter time in YYYY-MM-DD HH:MM:SS format:
57 SELECT DISTINCT Job.JobId,Client.Name as Client,Level,StartTime,JobFiles,JobBytes,VolumeName
58 FROM Client,Job,JobMedia,Media
59 WHERE Client.Name='%1'
60 AND Client.ClientId=Job.ClientId
62 AND JobMedia.JobId=Job.JobId AND JobMedia.MediaId=Media.MediaId
63 AND Job.StartTime >= '%2'
64 ORDER BY Job.StartTime;
66 :List all backups for a Client
68 SELECT DISTINCT Job.JobId as JobId,Client.Name as Client,
69 FileSet.FileSet AS FileSet,Level,StartTime,
70 JobFiles,JobBytes,VolumeName
71 FROM Client,Job,JobMedia,Media,FileSet
72 WHERE Client.Name='%1'
73 AND Client.ClientId=Job.ClientId AND Job.Type='B'
74 AND Job.JobStatus='T' AND Job.FileSetId=FileSet.FileSetId
75 AND JobMedia.JobId=Job.JobId AND JobMedia.MediaId=Media.MediaId
76 ORDER BY Job.StartTime;
78 :List Volume Attributes for a selected Volume
80 SELECT Slot,MaxVolBytes,VolCapacityBytes,VolStatus,Recycle,VolRetention,
81 VolUseDuration,MaxVolJobs,MaxVolFiles
83 WHERE VolumeName='%1';
85 :List Volumes used by selected JobId
87 SELECT DISTINCT Job.JobId,VolumeName
88 FROM Job,JobMedia,Media
90 AND Job.JobId=JobMedia.JobId
91 AND JobMedia.MediaId=Media.MediaId;
93 :List Volumes to Restore All Files
97 CREATE TABLE temp (JobId BIGINT NOT NULL,
105 VolSessionTime BIGINT );
106 CREATE TABLE temp2 (JobId BIGINT NOT NULL,
112 VolSessionTime BIGINT);
113 # Select last Full save
114 INSERT INTO temp SELECT Job.JobId,JobTDate,Job.ClientId,Job.Level,
115 StartTime,VolumeName,JobMedia.StartFile,VolSessionId,VolSessionTime
116 FROM Client,Job,JobMedia,Media WHERE Client.Name='%1'
117 AND Client.ClientId=Job.ClientId
118 AND Level='F' AND JobStatus='T'
119 AND JobMedia.JobId=Job.JobId
120 AND JobMedia.MediaId=Media.MediaId
121 ORDER BY Job.JobTDate DESC LIMIT 1;
122 # Copy into temp 2 getting all volumes of Full save
123 INSERT INTO temp2 SELECT Job.JobId,Job.StartTime,Media.VolumeName,Job.Level,
124 JobMedia.StartFile,Job.VolSessionId,Job.VolSessionTime
125 FROM temp,Job,JobMedia,Media WHERE temp.JobId=Job.JobId
126 AND Job.Level='F' AND Job.JobStatus='T'
127 AND JobMedia.JobId=Job.JobId
128 AND JobMedia.MediaId=Media.MediaId;
129 # Now add subsequent incrementals
130 INSERT INTO temp2 SELECT DISTINCT Job.JobId,Job.StartTime,Media.VolumeName,
131 Job.Level,JobMedia.StartFile,Job.VolSessionId,Job.VolSessionTime
132 FROM Job,temp,JobMedia,Media
133 WHERE Job.JobTDate>temp.JobTDate
134 AND Job.ClientId=temp.ClientId
135 AND Job.Level IN ('I','D') AND JobStatus='T'
136 AND JobMedia.JobId=Job.JobId
137 AND JobMedia.MediaId=Media.MediaId;
139 SELECT DISTINCT VolumeName from temp2;
143 :List Pool Attributes for a selected Pool
145 SELECT Recycle,VolRetention,VolUseDuration,MaxVolJobs,MaxVolFiles,MaxVolBytes
149 :List total files/bytes by Job
150 SELECT count(*) AS Jobs,sum(JobFiles) AS Files,sum(JobBytes) AS Bytes,Name AS Job
151 FROM Job GROUP by Name;
153 :List total files/bytes by Volume
154 SELECT count(*) AS Jobs,sum(JobFiles) AS Files,sum(JobBytes) AS Bytes,VolumeName
155 FROM Job,JobMedia,Media
156 WHERE JobMedia.JobId=Job.JobId
157 AND JobMedia.MediaId=Media.MediaId
160 :List Files for a selected JobId
162 SELECT Path.Path,Filename.Name FROM File,Filename,Path WHERE File.JobId=%1
163 AND Filename.FilenameId=File.FilenameId
164 AND Path.PathId=File.PathId ORDER BY
165 Path.Path,Filename.Name;
167 :List Jobs stored on a selected MediaId
169 SELECT DISTINCT Job.JobId,Job.Name,Job.StartTime,Job.Type,
170 Job.Level,Job.JobFiles,Job.JobBytes,Job.JobStatus
172 WHERE JobMedia.JobId=Job.JobId
173 AND JobMedia.MediaId=%1
174 ORDER by Job.StartTime;
176 :List Jobs stored for a given Volume name
178 SELECT DISTINCT Job.JobId as JobId,Job.Name as Name,Job.StartTime as StartTime,
179 Job.Type as Type,Job.Level as Level,Job.JobFiles as Files,
180 Job.JobBytes as Bytes,Job.JobStatus as Status
181 FROM Media,JobMedia,Job
182 WHERE Media.VolumeName='%1'
183 AND Media.MediaId=JobMedia.MediaId
184 AND JobMedia.JobId=Job.JobId
185 ORDER by Job.StartTime;
187 :List Volumes Bacula thinks are in changer
188 SELECT MediaId,VolumeName,VolBytes/(1024*1024*1024) AS GB,Storage.Name
189 AS Storage,Slot,Pool.Name AS Pool,MediaType,VolStatus
190 FROM Media,Pool,Storage
191 WHERE Media.PoolId=Pool.PoolId
192 AND Slot>0 AND InChanger=1
193 AND Media.StorageId=Storage.StorageId
194 ORDER BY MediaType ASC, Slot ASC;
196 :List Volumes likely to need replacement from age or errors
197 SELECT VolumeName AS Volume,VolMounts AS Mounts,VolErrors AS Errors,
198 VolWrites AS Writes,VolStatus AS Status
200 WHERE (VolErrors>0) OR (VolStatus='Error') OR (VolMounts>50) OR
201 (VolStatus='Disabled') OR (VolWrites>3999999)
202 ORDER BY VolStatus ASC, VolErrors,VolMounts,VolumeName DESC;
204 :List Volumes Bacula thinks are eligible for the changer
205 SELECT VolumeName,VolStatus,Storage.Name AS Location,
206 VolBytes/(1024*1024*1024) AS GB,MediaId,MediaType,Pool.Name AS Pool
207 FROM Media,Pool,Storage
208 WHERE Media.PoolId=Pool.PoolId
209 AND Media.StorageId=Storage.StorageId
211 AND ((VolStatus='Purged') OR (VolStatus='Append') OR (VolStatus='Recycle'))
212 ORDER BY VolMounts ASC, Pool.Name ASC, VolumeName ASC
214 :List Volumes by Volume:
215 SELECT VolumeName, Job.JobId as JobID, Job.Name as JobName, Job.StartTime as
216 Start, sum(JobFiles) AS Files,sum(JobBytes) AS Bytes
217 FROM Job,JobMedia,Media
218 WHERE JobMedia.JobId=Job.JobId
219 AND JobMedia.MediaId=Media.MediaId
220 GROUP by VolumeName, Job.JobID, Job.Name, Job.StartTime
223 :List Volumes by Jobs:
224 SELECT Job.Name as JobName, Job.JobId as JobID, VolumeName, Job.StartTime as
225 Start, sum(JobFiles) AS Files,sum(JobBytes) AS Bytes
226 FROM Job,JobMedia,Media
227 WHERE JobMedia.JobId=Job.JobId
228 AND JobMedia.MediaId=Media.MediaId
229 GROUP by VolumeName, Job.JobID, Job.Name, Job.StartTime
230 ORDER by JobName, Start;
232 :List Volumes for a jobname:
234 SELECT Job.Name as JobName, Job.JobId as JobID, VolumeName, Job.StartTime as
235 Start, sum(JobFiles) AS Files,sum(JobBytes) AS Bytes
236 FROM Job,JobMedia,Media
238 AND JobMedia.JobId=Job.JobId
239 AND JobMedia.MediaId=Media.MediaId
240 GROUP by VolumeName, Job.JobID, Job.Name, Job.StartTime
241 ORDER by JobName, Start;