Wonderful. This is a good start. Thanks Alan.
Erich On Aug 13, 2007, at 10:40 AM, Alan Brown wrote: > 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 > ------------------------------------------------------------------------- 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
