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