1#
2# This file contains sample queries
3# that you can possibly use with the bconsole query command.
4#
5
6# 1
7:List up to 20 places where a File is saved regardless of the directory and Client
8*Enter Filename (no path):
9SELECT DISTINCT Job.JobId AS JobId, Client.Name AS Client,
10  Path.Path,File.Name,StartTime,Level,JobFiles,ROUND(JobBytes/1024.0/1024.0/1024.0,3) AS SIZE_GB
11 FROM Client,Job,File,Path WHERE Client.ClientId=Job.ClientId
12 AND JobStatus IN ('T','W') AND Job.JobId=File.JobId
13 AND Path.PathId=File.PathId
14 AND File.Name='%1'
15 ORDER BY Job.StartTime LIMIT 20;
16
17# 2
18:List where the most recent copies of a file with path of a Client are saved
19*Enter path with trailing slash:
20*Enter filename:
21*Enter Client name:
22SELECT DISTINCT Job.JobId,StartTime AS JobStartTime,VolumeName,Client.Name AS ClientName
23 FROM Job,File,Path,Media,JobMedia,Client
24 WHERE File.JobId=Job.JobId
25 AND Path.Path='%1'
26 AND File.Name='%2'
27 AND Client.Name='%3'
28 AND Path.PathId=File.PathId
29 AND JobMedia.JobId=Job.JobId
30 AND JobMedia.MediaId=Media.MediaId
31 AND Client.ClientId=Job.ClientId
32 ORDER BY Job.StartTime DESC LIMIT 5;
33
34# 3
35:List last 20 Full Backups for a Client
36*Enter Client name:
37SELECT DISTINCT Job.JobId,Client.Name AS Client,Starttime,JobFiles,ROUND(Job.JobBytes/1024.0/1024.0/1024.0,3) AS GB
38 FROM Client,Job,JobMedia,Media
39 WHERE Client.Name='%1'
40 AND Client.ClientId=Job.ClientId
41 AND Level='F' AND JobStatus IN ('T', 'W')
42 AND JobMedia.JobId=Job.JobId
43 AND JobMedia.MediaId=Media.MediaId
44 ORDER BY Job.StartTime DESC LIMIT 20;
45
46# 4
47:List all backups for a Client after a specified time
48*Enter Client Name:
49*Enter time in YYYY-MM-DD HH:MM:SS format:
50SELECT DISTINCT Job.JobId,Client.Name AS Client,Level,StartTime,JobFiles,ROUND(Job.JobBytes/1024.0/1024.0/1024.0,3) AS GB,Count(VolumeName) AS Volumes
51 FROM Client,Job,JobMedia,Media
52 WHERE Client.Name='%1'
53 AND Client.ClientId=Job.ClientId
54 AND JobStatus IN ('T', 'W')
55 AND JobMedia.JobId=Job.JobId AND JobMedia.MediaId=Media.MediaId
56 AND Job.StartTime >= '%2'
57 GROUP BY Job.JobId,Client.Name,Level,StartTime,JobFiles,Job.JobBytes
58 ORDER BY Job.StartTime;
59
60# 5
61:List all backups for a Client and COUNT the Volumes which been used
62*Enter Client Name:
63SELECT DISTINCT Job.JobId AS JobId,Client.Name AS Client,
64   FileSet.FileSet AS FileSet,Level,StartTime,
65   JobFiles,ROUND(Job.JobBytes/1024.0/1024.0/1024.0,3) AS GB,Count(VolumeName) AS Volumes
66 FROM Client,Job,JobMedia,Media,FileSet
67 WHERE Client.Name='%1'
68 AND Client.ClientId=Job.ClientId AND Job.Type='B'
69 AND Job.JobStatus IN ('T','W') AND Job.FileSetId=FileSet.FileSetId
70 AND JobMedia.JobId=Job.JobId AND JobMedia.MediaId=Media.MediaId
71 GROUP BY Job.JobId,Client.Name,FileSet.FileSet,Level,StartTime,JobFiles,Job.JobBytes
72 ORDER BY Job.StartTime;
73
74# 6
75:List Volume Attributes for a selected Volume
76*Enter Volume name:
77SELECT Slot,MaxVolBytes,VolCapacityBytes,VolStatus,Recycle,VolRetention,
78  VolUseDuration,MaxVolJobs,MaxVolFiles
79 FROM Media
80 WHERE VolumeName='%1';
81
82# 7
83:List Volumes used by selected JobId
84*Enter JobId:
85SELECT DISTINCT Job.JobId,VolumeName
86 FROM Job,JobMedia,Media
87 WHERE Job.JobId=%1
88 AND Job.JobId=JobMedia.JobId
89 AND JobMedia.MediaId=Media.MediaId;
90# 8
91:List Volumes to Restore All Files
92*Enter Client Name:
93!DROP TABLE temp;
94!DROP TABLE temp2;
95CREATE TABLE temp (JobId BIGINT NOT NULL,
96 JobTDate BIGINT,
97 ClientId BIGINT,
98 Level CHAR,
99 StartTime TEXT,
100 VolumeName TEXT,
101 StartFile BIGINT,
102 VolSessionId BIGINT,
103 VolSessionTime BIGINT );
104CREATE TABLE temp2 (JobId BIGINT NOT NULL,
105 StartTime TEXT,
106 VolumeName TEXT,
107 Level CHAR,
108 StartFile BIGINT,
109 VolSessionId BIGINT,
110 VolSessionTime BIGINT);
111# Select last Full save
112INSERT INTO temp SELECT Job.JobId,JobTDate,Job.ClientId,Job.Level,
113  StartTime,VolumeName,JobMedia.StartFile,VolSessionId,VolSessionTime
114 FROM Client,Job,JobMedia,Media WHERE Client.Name='%1'
115 AND Client.ClientId=Job.ClientId
116 AND Level='F' AND JobStatus IN ('T', 'W')
117 AND JobMedia.JobId=Job.JobId
118 AND JobMedia.MediaId=Media.MediaId
119 ORDER BY Job.JobTDate DESC LIMIT 1;
120# Copy into temp 2 getting all volumes of Full save
121INSERT INTO temp2 SELECT Job.JobId,Job.StartTime,Media.VolumeName,Job.Level,
122  JobMedia.StartFile,Job.VolSessionId,Job.VolSessionTime
123 FROM temp,Job,JobMedia,Media WHERE temp.JobId=Job.JobId
124 AND Job.Level='F' AND Job.JobStatus IN ('T', 'W')
125 AND JobMedia.JobId=Job.JobId
126 AND JobMedia.MediaId=Media.MediaId;
127# Now add subsequent incrementals
128INSERT INTO temp2 SELECT DISTINCT Job.JobId,Job.StartTime,Media.VolumeName,
129  Job.Level,JobMedia.StartFile,Job.VolSessionId,Job.VolSessionTime
130 FROM Job,temp,JobMedia,Media
131 WHERE Job.JobTDate>temp.JobTDate
132 AND Job.ClientId=temp.ClientId
133 AND Job.Level IN ('I','D') AND JobStatus IN ('T', 'W')
134 AND JobMedia.JobId=Job.JobId
135 AND JobMedia.MediaId=Media.MediaId;
136# list results
137SELECT DISTINCT VolumeName from temp2;
138!DROP TABLE temp;
139!DROP TABLE temp2;
140
141# 9
142:List Pool Attributes for a selected Pool
143*Enter Pool name:
144SELECT Recycle,VolRetention,VolUseDuration,MaxVolJobs,MaxVolFiles,MaxVolBytes
145 FROM Pool
146 WHERE Name='%1';
147
148# 10
149:List total files/bytes by Job
150SELECT COUNT(*) AS Jobs,SUM(JobFiles) AS Files,ROUND(SUM(JobBytes/1024.0/1024.0/1024.0),3) AS GB, Name AS Job
151 FROM Job
152 GROUP by Name;
153
154# 11
155:List total files/bytes by Volume
156SELECT COUNT(*) AS Jobs,SUM(JobFiles) AS Files,ROUND(SUM(Job.JobBytes/1024.0/1024.0/1024.0),3) AS GB, VolumeName
157 FROM Job,JobMedia,Media
158 WHERE JobMedia.JobId=Job.JobId
159 AND JobMedia.MediaId=Media.MediaId
160 GROUP by VolumeName;
161
162# 12
163:List Files for a selected JobId
164*Enter JobId:
165SELECT Path.Path,File.Name FROM File,Path WHERE File.JobId=%1
166 AND Path.PathId=File.PathId ORDER BY
167 Path.Path,File.Name;
168
169# 13
170:List Jobs stored on a selected MediaId
171*Enter MediaId:
172SELECT DISTINCT Job.JobId,Job.Name,Job.StartTime,Job.Type,
173  Job.Level,Job.JobFiles,ROUND(Job.JobBytes/1024.0/1024.0/1024.0,3) AS GB,Job.JobStatus
174 FROM JobMedia,Job
175 WHERE JobMedia.JobId=Job.JobId
176 AND JobMedia.MediaId=%1
177 ORDER by Job.StartTime;
178
179# 14
180:List Jobs stored for a given Volume name
181*Enter Volume name:
182SELECT DISTINCT Job.JobId AS JobId,Job.Name AS Name,Job.StartTime AS StartTime,
183  Job.Type AS Type,Job.Level AS Level,Job.JobFiles AS Files,
184  ROUND(Job.JobBytes/1024.0/1024.0/1024.0,3) AS GB ,Job.JobStatus AS Status
185 FROM Media,JobMedia,Job
186 WHERE Media.VolumeName='%1'
187 AND Media.MediaId=JobMedia.MediaId
188 AND JobMedia.JobId=Job.JobId
189 ORDER by Job.StartTime;
190
191# 15
192:List Volumes Bareos thinks are in changer
193SELECT MediaId,VolumeName,ROUND(VolBytes/(1024.0*1024.0*1024.0),3) AS GB,Storage.Name
194  AS Storage,Slot,Pool.Name AS Pool,MediaType,VolStatus
195  FROM Media,Pool,Storage
196  WHERE Media.PoolId=Pool.PoolId
197  AND Slot>0 AND InChanger=1
198  AND Media.StorageId=Storage.StorageId
199  ORDER BY MediaType ASC, Slot ASC;
200
201# 16
202:List Volumes likely to need replacement from age or errors
203SELECT VolumeName AS Volume,VolErrors AS Errors, VolMounts AS Mounts,
204VolWrites AS Writes,RecycleCount,VolStatus AS Status
205  FROM Media
206  WHERE (VolErrors>0) OR (VolStatus='Error') OR (VolMounts>50) OR
207        (VolStatus='Disabled') OR (VolWrites>3999999)
208  ORDER BY VolErrors DESC, VolStatus, VolMounts,VolumeName DESC;
209
210#  17
211:List Volumes Bareos thinks are eligible for the changer
212SELECT VolumeName,VolStatus,Storage.Name AS Location,
213   VolBytes/(1024*1024*1024) AS GB,MediaId,MediaType,Pool.Name AS Pool
214   FROM Media,Pool,Storage
215   WHERE Media.PoolId=Pool.PoolId
216   AND Media.StorageId=Storage.StorageId
217   AND InChanger=0
218   AND ((VolStatus='Purged') OR (VolStatus='Append') OR (VolStatus='Recycle'))
219   ORDER BY VolMounts ASC, Pool.Name ASC, VolumeName ASC
220
221# 18
222:List Volumes by Volume:
223SELECT VolumeName, Job.JobId AS JobID, Job.Name AS JobName, Job.StartTime AS
224Start, JobFiles AS Files,ROUND(Job.JobBytes/1024.0/1024.0/1024.0,3) AS GB
225 FROM Job,JobMedia,Media
226 WHERE JobMedia.JobId=Job.JobId
227 AND JobMedia.MediaId=Media.MediaId
228 GROUP by VolumeName, Job.JobID, Job.Name, Job.JobBytes, JobFiles, Job.StartTime
229 ORDER by VolumeName;
230
231# 19
232:List Volumes by Jobs:
233SELECT Job.Name AS JobName, Job.JobId AS JobID, VolumeName, Job.StartTime AS
234Start, JobFiles AS Files,ROUND(Job.JobBytes/1024.0/1024.0/1024.0,3) AS GB
235 FROM Job,JobMedia,Media
236 WHERE JobMedia.JobId=Job.JobId
237 AND JobMedia.MediaId=Media.MediaId
238 GROUP by VolumeName, Job.JobID, Job.Name, Job.JobBytes, JobFiles, Job.StartTime
239 ORDER by JobName, Start;
240
241# 20
242:List Volumes for a jobname:
243*Enter Job name:
244SELECT Job.Name AS JobName, Job.JobId AS JobID, VolumeName, Job.StartTime AS
245Start, JobFiles AS Files,ROUND(Job.JobBytes/1024.0/1024.0/1024.0,3) AS Bytes
246 FROM Job,JobMedia,Media
247 WHERE Job.Name='%1'
248 AND JobMedia.JobId=Job.JobId
249 AND JobMedia.MediaId=Media.MediaId
250 GROUP by VolumeName, Job.JobID, Job.Name, Job.JobBytes, JobFiles, Job.StartTime
251 ORDER by JobName, Start;
252
253# 21
254:List Jobs of a client that contain a file (regardless of the directory, with wildcards)
255*Enter Client name:
256*Enter Filename (no path, search uses SQL LIKE operation):
257*Enter search limit:
258SELECT DISTINCT
259  Job.JobId AS JobId,
260  Path.Path,
261  File.Name,
262  Job.StartTime,
263  File.MD5,
264  Job.Level,
265  Media.VolumeName
266FROM Client, Job, File, Path, JobMedia, Media
267WHERE Client.Name='%1'
268  AND File.Name LIKE '%2'
269  AND File.FileIndex > 0
270  AND Client.ClientId=Job.ClientId
271  AND Job.JobStatus IN ('T','W')
272  AND Job.JobId=File.JobId
273  AND Path.PathId=File.PathId
274  AND Client.ClientId=Job.ClientId
275  AND JobMedia.JobId=Job.JobId
276  AND JobMedia.MediaId=Media.MediaId
277ORDER BY File.Name ASC, Job.StartTime DESC
278LIMIT %3;
279