On Mon, 13 Aug 2007, Erich Prinz wrote: > > Had a failure occur that prevented the usual running of Full backups. > > Looking for a 'how to' query to find the most recent Full backup of > all clients to see how far off we are into Incrementals / Diffs from > an actual Full. > > Any SQL gurus on the list that can help?
I don't have exactly what you're after (A bit of guru attention would solve that) but these should do as a starting point... Just append them to /etc/bacula/query.sql If you want to only see sucessful backups, uncomment the "# AND JobStatus = 'T'" lines - I commented those out in order to see how much tape was being eaten by failure. #20 :List Volumes by Jobs: SELECT Job.Name as JobName, Job.JobId as JobID, VolumeName, Job.StartTime as Start, sum(JobFiles) AS Files,sum(JobBytes) AS Bytes FROM Job,JobMedia,Media WHERE JobMedia.JobId=Job.JobId AND JobMedia.MediaId=Media.MediaId GROUP by VolumeName, Job.JobID, Job.Name, Job.StartTime ORDER by JobName, Start; # 21 :List Volumes for a jobname: *Enter Job name: SELECT Job.Name as JobName, Job.JobId as JobID, VolumeName, Job.StartTime as Start, sum(JobFiles) AS Files,sum(JobBytes) AS Bytes FROM Job,JobMedia,Media WHERE Job.Name='%1' AND JobMedia.JobId=Job.JobId AND JobMedia.MediaId=Media.MediaId GROUP by VolumeName, Job.JobID, Job.Name, Job.StartTime ORDER by JobName, Start; #22 :List all Full backups SELECT DISTINCT Job.JobId,Job.Name,Job.Level AS L,Job.JobStatus as S, StartTime,JobBytes/1024/1024/1024 as Gb FROM Client,Job,JobMedia,Media WHERE Client.ClientId=Job.ClientId AND Level='F' #AND JobStatus = 'T' AND JobMedia.JobId=Job.JobId AND JobMedia.MediaId=Media.MediaId ORDER BY Job.Name,Job.StartTime #23 :List all Full + Differential backups SELECT DISTINCT Job.JobId,Job.Name,Job.Level AS L,Job.JobStatus as S, StartTime,JobBytes/1024/1024/1024 as Gb FROM Client,Job,JobMedia,Media WHERE Client.ClientId=Job.ClientId # AND JobStatus = 'T' AND ( Level='F' or Level="D" ) AND JobMedia.JobId=Job.JobId AND JobMedia.MediaId=Media.MediaId ORDER BY Job.Name,Job.StartTime #24 :List all Full backups for a particular Job *Enter JobName: SELECT DISTINCT Job.JobId,Job.Name,Job.Level AS L,Job.JobStatus as S, StartTime,JobBytes/1024/1024/1024 as Gb FROM Client,Job,JobMedia,Media WHERE Client.ClientId=Job.ClientId # AND JobStatus = 'T' AND Level='F' AND JobMedia.JobId=Job.JobId AND JobMedia.MediaId=Media.MediaId AND Job.Name='%1' ORDER BY Job.Name,Job.StartTime #25 :List all Full + Differential backups for a particular Job *Enter JobName: SELECT DISTINCT Job.JobId,Job.Name,Job.Level AS L,Job.JobStatus as S, StartTime,JobBytes/1024/1024/1024 as Gb FROM Client,Job,JobMedia,Media WHERE Client.ClientId=Job.ClientId # AND JobStatus = 'T' AND ( Level='F' or Level="D" ) AND JobMedia.JobId=Job.JobId AND JobMedia.MediaId=Media.MediaId AND Job.Name='%1' ORDER BY Job.Name,Job.StartTime #26 :List all backups for a particular Job *Enter JobName: SELECT DISTINCT Job.JobId,Job.Name,Job.Level AS L,Job.JobStatus as S, StartTime,JobBytes/1024/1024/1024 as Gb FROM Client,Job,JobMedia,Media WHERE Client.ClientId=Job.ClientId # AND JobStatus = 'T' # AND ( Level='F' or Level="D" ) AND JobMedia.JobId=Job.JobId AND JobMedia.MediaId=Media.MediaId AND Job.Name='%1' ORDER BY Job.Name,Job.StartTime ------------------------------------------------------------------------- This SF.net email is sponsored by: Splunk Inc. Still grepping through log files to find problems? Stop. Now Search log events and configuration files using AJAX and a browser. Download your FREE copy of Splunk now >> http://get.splunk.com/ _______________________________________________ Bacula-users mailing list Bacula-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/bacula-users
