Re: [Bacula-users] Anyone written any handy queries (query.sql)???
On Tue, Aug 11, 2009 at 02:39:39PM -0400, John Lockard wrote: I have modified my query.sql to include some queries that I use frequently and I thought maybe someone else would find them useful additions. Also, I was wondering if anyone had queries which they find useful and would like to share. In my setup, I need to rotate tapes on a weekly basis to keep offsites in case of emergency, so I need to find certain groups of tapes for easy removal and it's easier to group them in query output than having to scan down a 57 item long list and pick out the ones I need (and other similar needs). I hope someone finds this useful, -John SNIP I've just done another one which might be useful... I keep backups on disk for about a week over a month, then I migrate my Differential and Full backups to tape for safe keeping. Bacula doesn't purge media when all jobs from a virtual tape have been migrated to physical tape, so the disk jobs stay around for the full life of the job. In my case that means my Full backups would live on disk for 9 months, even though they were migrated to tape almost 8 months ago. So, I need a way to find all MediaId's which have had *ALL* of their jobs migrated to tape so that I can purge them. Here's what I've come up with. (If there's an easier way to do this, please tell me). (Works on MySQL) :Test List Migrated Jobs stored on Media *Order by (Job or Media): !DROP TABLE tempmig; !DROP TABLE tempmig2; !DROP TABLE tempmig3; CREATE TABLE tempmig (MediaId INT, Type BINARY(1)); CREATE TABLE tempmig2 (MediaId INT, Type BINARY(1)); CREATE TABLE tempmig3 (MediaId INT NOT NULL); INSERT INTO tempmig SELECT JobMedia.MediaId,Job.Type FROM Job,JobMedia,Media WHERE JobMedia.MediaId=Media.MediaId AND Job.JobId=JobMedia.JobId AND Job.Type='M'; INSERT INTO tempmig2 SELECT JobMedia.MediaId,Job.Type FROM Job,JobMedia,Media WHERE tempmig.MediaId=JobMedia.MediaId AND Job.JobId=JobMedia.JobId AND Job.Type!='M'; INSERT INTO tempmig3 SELECT tempmig.MediaId FROM tempmig LEFT JOIN tempmig2 ON tempmig2.MediaId = tempmig.MediaId WHERE tempmig2.MediaId IS NULL; SELECT DISTINCT Job.JobId,JobMedia.MediaId,Job.Name,Job.Type,Job.Level,Job.JobStatus AS Status,Job.JobFiles AS Files,Job.JobBytes/(1024*1024*1024) AS GB FROM JobMedia,Job,tempmig3 WHERE JobMedia.JobId=Job.JobId AND JobMedia.MediaId=tempmig3.MediaId ORDER by JobMedia.%1Id ASC; !DROP TABLE tempmig; !DROP TABLE tempmig2; !DROP TABLE tempmig3; -John -- No matter how sophisticated you may be, a large granite mountain cannot be denied - it speaks in silence to the very core of your being - Ansel Adams --- John M. Lockard | U of Michigan - School of Information Unix and Security Admin | 1214 SI North - 1075 Beal Ave. jlock...@umich.edu |Ann Arbor, MI 48109-2112 www.umich.edu/~jlockard | 734-615-8776 | 734-647-8045 FAX --- -- Come build with us! The BlackBerryreg; Developer Conference in SF, CA is the only developer event you need to attend this year. Jumpstart your developing skills, take BlackBerry mobile applications to market and stay ahead of the curve. Join us from November 9#45;12, 2009. Register now#33; http://p.sf.net/sfu/devconf ___ Bacula-users mailing list Bacula-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/bacula-users
Re: [Bacula-users] Anyone written any handy queries (query.sql)???
John Lockard wrote, sometime around 11/08/09 19:39: I have modified my query.sql to include some queries that I use frequently and I thought maybe someone else would find them useful additions. Also, I was wondering if anyone had queries which they find useful and would like to share. Some of mine may only work on PostgreSQL - I dunno.. :List the most recent full backups SELECT DISTINCT jobname, endtime, volumename from ( SELECT lastfullbackups.JobId, JobName, ScheduledTime, StartTime, EndTime, volumename FROM ( SELECT jobid AS JobId, job.name AS JobName, job.schedtime AS ScheduledTime, job.starttime AS StartTime, job.endtime AS EndTime FROM ( SELECT name as Job, max(endtime) AS EndTime FROM job INNER JOIN jobinfo ON job.name = JI_jobname WHERE type = 'B' AND level = 'F' AND jobstatus = 'T' AND ji_old = false GROUP BY name ) lastfullbackups LEFT OUTER JOIN job ON lastfullbackups.Job = job.name AND lastfullbackups.EndTime = job.endtime ) lastfullbackups LEFT OUTER JOIN jobmedia ON lastfullbackups.jobid = jobmedia.jobid LEFT OUTER JOIN media ON jobmedia.mediaid = media.mediaid ) foo :List last 20 Full Backups for a named job *Enter Job name: SELECT DISTINCT Job.JobId,Client.Name AS Client,Job.StartTime,JobFiles,JobBytes, VolumeName FROM Client INNER JOIN Job on Client.ClientId = Job.ClientId INNER JOIN JobMedia ON Job.JobId = JobMedia.JobId INNER JOIN Media on JobMedia.MediaId=Media.MediaId WHERE Job.Name='%1' AND Level='F' AND JobStatus='T' ORDER BY Job.StartTime DESC LIMIT 20; Also, I wanted to know which tapes needed to be offsite when I was running a full differential combination. To do this, I created a table listing all the jobs and whether they should be included or not as I have quite a few old jobs in the catalog which I don't really care about: CREATE TABLE jobinfo ( ji_primary SERIAL, ji_old boolean DEFAULT FALSE, ji_jobname varchar(45) ); List the last completed full backup for each job, and the tape it is on: CREATE VIEW lastfullbackuptapes AS SELECT DISTINCT foo2.jobname, foo2.endtime, foo2.volumename FROM ( SELECT lastfullbackups.jobid, jobname, scheduledtime, starttime, endtime, volumename FROM ( SELECT jobid, lastfullbackups.job AS jobname, job.schedtime AS scheduledtime, job.starttime, job.endtime FROM ( SELECT ji_jobname AS job, max(endtime) AS endtime FROM jobinfo LEFT JOIN ( SELECT job.name, job.endtime FROM job WHERE job.type = 'B'::bpchar AND job.level = 'F' AND job.jobstatus = 'T') fulljobs ON jobinfo.ji_jobname = fulljobs.name WHERE ji_old = false GROUP BY ji_jobname) lastfullbackups LEFT JOIN job ON lastfullbackups.job = job.name AND lastfullbackups.endtime = job.endtime) lastfullbackups LEFT JOIN jobmedia ON lastfullbackups.jobid = jobmedia.jobid LEFT JOIN media ON jobmedia.mediaid = media.mediaid) foo2 ORDER BY foo2.jobname, foo2.endtime, foo2.volumename; List all completed differential backups since the last full backup for each job and the tape they are on (uses the above view): CREATE VIEW lastdiffbackuptapes AS SELECT DISTINCT res.name, res.endtime, media.volumename FROM lastfullbackuptapes JOIN ( SELECT job.jobid, job.job, job.name, job.type, job.level, job.clientid, job.jobstatus, job.schedtime, job.starttime, job.endtime, job.jobtdate, job.volsessionid, job.volsessiontime, job.jobfiles, job.jobbytes, job.joberrors, job.jobmissingfiles, job.poolid, job.filesetid, job.purgedfiles, job.hasbase FROM job WHERE job.type = 'B' AND job.jobstatus = 'T' AND job.level = 'D') res ON lastfullbackuptapes.jobname = res.name AND lastfullbackuptapes.endtime res.starttime LEFT JOIN jobmedia ON res.jobid = jobmedia.jobid LEFT JOIN media ON jobmedia.mediaid = media.mediaid ORDER BY res.name, res.endtime, media.volumename; List all tapes which should be offsite in order to maintain the most up to date complete backup (for each job the most recent full and all subsequent differentials): SELECT DISTINCT res.volumename FROM ( SELECT lastfullbackuptapes.volumename FROM lastfullbackuptapes UNION SELECT lastdiffbackuptapes.volumename FROM lastdiffbackuptapes) res ORDER BY res.volumename; -- Russell Howe, IT Manager. rh...@bmtmarinerisk.com BMT Marine Offshore Surveys Ltd.
Re: [Bacula-users] Anyone written any handy queries (query.sql)???
On Tue, Aug 11, 2009 at 2:39 PM, John Lockardjlock...@umich.edu wrote: I have modified my query.sql to include some queries that I use frequently and I thought maybe someone else would find them useful additions. Also, I was wondering if anyone had queries which they find useful and would like to share. In my setup, I need to rotate tapes on a weekly basis to keep offsites in case of emergency, so I need to find certain groups of tapes for easy removal and it's easier to group them in query output than having to scan down a 57 item long list and pick out the ones I need (and other similar needs). I hope someone finds this useful, -John ## :List Volumes Bacula thinks are in changer (By Slot) SELECT Slot,VolumeName,MediaId AS Id,VolBytes/(1024*1024*1024) AS GB,Storage.Name AS Storage,Pool.Name AS Pool,VolStatus FROM Media,Pool,Storage WHERE Media.PoolId=Pool.PoolId AND Slot0 AND InChanger=1 AND Media.StorageId=Storage.StorageId ORDER BY Slot ASC; If you want ordered by VolumeName: ORDER BY VolumeName ASC; If you want ordered by Pool, then Slot: ORDER BY Pool,Slot; ## :List Full Volumes Bacula thinks are in changer (By Pool) SELECT Slot,VolumeName,MediaId AS Id,Pool.Name AS Pool,VolStatus, VolBytes/(1024*1024*1024) AS GB FROM Media,Pool,Storage WHERE Media.PoolId=Pool.PoolId AND VolStatus='Full' AND Slot0 AND InChanger=1 AND Media.StorageId=Storage.StorageId ORDER BY Pool,Slot ASC; If you want ordered by Slot: ORDER BY Slot ASC; ## :List Non-Full Volumes Bacula thinks are in changer (By Pool) SELECT Slot,VolumeName,MediaId AS Id,Pool.Name AS Pool,VolStatus, VolBytes/(1024*1024*1024) AS GB FROM Media,Pool,Storage WHERE Media.PoolId=Pool.PoolId AND VolStatus!='Full' AND Slot0 AND InChanger=1 AND Media.StorageId=Storage.StorageId ORDER BY Pool,Slot ASC; If you want ordered by Slot: ORDER BY Slot ASC; ## (Change Media.MediaType entry below to match your settings) :List All Tape Volumes Bacula knows about (By VolumeName) SELECT Slot,VolumeName,MediaId AS Id,Pool.Name AS Pool,VolStatus, VolBytes/(1024*1024*1024) AS GB FROM Media,Pool,Storage WHERE Media.PoolId=Pool.PoolId AND Media.StorageId=Storage.StorageId AND Media.MediaType='LTO-2' ORDER BY VolumeName ASC; If you want ordered by Pool and Slot: ORDER BY Pool,Slot ASC; If you want ordered by Slot: ORDER BY Slot ASC; ## :List All Volumes in a Pool *Enter Pool name: SELECT MediaId AS Id,VolumeName,VolBytes/(1024*1024*1024) AS GB,Slot, Pool.Name AS Pool,VolStatus FROM Media,Pool,Storage WHERE Media.PoolId=Pool.PoolId AND Pool.Name='%1' AND Media.StorageId=Storage.StorageId ORDER BY VolumeName ASC; ## (Not as useful as I thought it would be, but here it is) :Show Log for JobId *Enter JobId: SELECT Time,LogText FROM Log WHERE JobId='%1' ORDER BY Time; -- Let Crystal Reports handle the reporting - Free Crystal Reports 2008 30-Day trial. Simplify your report design, integration and deployment - and focus on what you do best, core application coding. Discover what's new with Crystal Reports now. http://p.sf.net/sfu/bobj-july ___ Bacula-users mailing list Bacula-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/bacula-users Here are mine.. The most useful one is #17. I see that you do similar with your full / not full queries. # 17 :List Volumes Bacula thinks are full and in changer SELECT MediaId,VolumeName,VolBytes/(1024*1024*1024) AS GB,Storage.Name AS Storage,Slot,Pool.Name AS Pool,MediaType,Media.lastwritten,VolStatus FROM Media,Pool,Storage WHERE Media.PoolId=Pool.PoolId AND Slot0 AND InChanger=1 AND (VolStatus='Full' OR VolStatus='Archive') AND Media.StorageId=Storage.StorageId ORDER BY MediaType ASC, Media.lastwritten ASC; # 18 :Get all the Jobids for a job *Enter Job Name: SELECT jobid,job,level,clientid,jobstatus,jobfiles,jobbytes FROM Job WHERE Name = '%1'; # 19 :List Volumes by status *Enter volume status to find SELECT MediaId,VolumeName,VolBytes/(1024*1024*1024) AS GB,Storage.Name AS Storage,Slot,Pool.Name AS Pool,MediaType,VolStatus FROM Media,Pool,Storage WHERE Media.PoolId=Pool.PoolId AND (VolStatus='%1') AND Media.StorageId=Storage.StorageId ORDER BY Storage ASC,MediaType ASC, Slot ASC; -- John M. Drescher -- Let Crystal Reports handle the reporting - Free Crystal Reports 2008 30-Day trial. Simplify your report design, integration and deployment - and focus on what you do best, core application coding. Discover what's new with Crystal Reports now. http://p.sf.net/sfu/bobj-july ___ Bacula-users mailing list Bacula-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/bacula-users
Re: [Bacula-users] Anyone written any handy queries (query.sql)???
On Tue, 11 Aug 2009, John Drescher might have said: On Tue, Aug 11, 2009 at 2:39 PM, John Lockardjlock...@umich.edu wrote: I have modified my query.sql to include some queries that I use frequently and I thought maybe someone else would find them useful additions. Also, I was wondering if anyone had queries which they find useful and would like to share. Here are my custom queries. Mike # 17 :List pool space allocated by status SELECT Pool.Name AS Pool, VolStatus AS Status, sum(VolBytes) AS Size, count(*) AS Number FROM Media, Pool WHERE Media.PoolID = Pool.PoolId GROUP by Pool.Name, VolStatus ORDER by Pool.Name, VolStatus; # 18 :List Volumes by pool, name SELECT VolumeName AS Volume, VolMounts AS Mounts, VolErrors AS Errors, VolBytes AS Size, VolWrites AS Writes, VolStatus AS Status, Pool.Name AS Pool FROM Media, Pool WHERE Media.PoolID = Pool.PoolId ORDER by Pool.Name, VolumeName; # 19 :List Volumes by pool, age SELECT VolumeName AS Volume, VolMounts AS Mounts, VolErrors AS Errors, VolBytes AS Size, VolWrites AS Writes, VolStatus AS Status, LastWritten AS 'Last Written', Pool.Name AS Pool FROM Media, Pool WHERE Media.PoolID = Pool.PoolId ORDER by Pool.Name, LastWritten Desc; # 20 :List storage used by client and pool SELECT Client.Name AS Client, Pool.Name as Pool, sum(Job.JobFiles) as Files, sum(Job.JobBytes) as Bytes FROM Client, Job, Pool WHERE Job.ClientID = Client.ClientID and Job.PoolID = Pool.PoolID GROUP by Client.ClientID ORDER by Client.Name; -- Let Crystal Reports handle the reporting - Free Crystal Reports 2008 30-Day trial. Simplify your report design, integration and deployment - and focus on what you do best, core application coding. Discover what's new with Crystal Reports now. http://p.sf.net/sfu/bobj-july ___ Bacula-users mailing list Bacula-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/bacula-users
Re: [Bacula-users] Anyone written any handy queries (query.sql)???
In the message dated: Tue, 11 Aug 2009 14:39:39 EDT, The pithy ruminations from John Lockard on [Bacula-users] Anyone written any handy queries (query.sql)??? were: = I have modified my query.sql to include some queries that = I use frequently and I thought maybe someone else would = find them useful additions. Also, I was wondering if anyone = had queries which they find useful and would like to share. Sure. Here's my collection, largely drawn from earlier postings to this list (check the archives...hint, hint!) and from direct help from other bacula users. My SQL knowledge is infintesimal, so any improvements would be welcome (the wildcard search query, in particular, is terribly slow). -===- :List Volumes in the changer that are in need of replacement SELECT VolumeName As VolName,Storage.Name AS Storage,Slot,Pool.Name AS Pool,MediaType,VolS tatus AS Status, VolErrors AS Errors FROM Media,Pool,Storage WHERE Media.PoolId=Pool.PoolId AND Slot0 AND InChanger=1 AND Media.StorageId=Storage.StorageId AND ( (VolErrors0) OR (VolStatus='Error') OR (VolStatus='Disabled') OR (VolStatus='Full')) ORDER BY Slot ASC, VolumeName; :List tapes in expiration order # thanks to Eric Bollinger # From: Eric Bollengier e...@eb.homelinux.org # Date: Sat, 5 Aug 2006 20:31:37 +0200 SELECT Media.VolumeName AS volname, Media.VolStatus AS status, Media.LastWritten AS lastwritten, Media.InChanger AS Loaded, Pool.Name AS pool, FROM_UNIXTIME( UNIX_TIMESTAMP(Media.LastWritten) + (Media.VolRetention) ) AS expire FROM Media INNER JOIN Pool ON (Pool.PoolId = Media.PoolId) AND Media.Recycle = 1 AND Media.MediaType IN ('LTO2') AND Pool.Name IN ('Default','Full','Incremental') ORDER BY expire ASC, Media.VolUseDuration DESC LIMIT 25; :Find next volumes to load SELECT VolumeName AS VolName,Pool.Name AS Pool,MediaType AS Type,VolStatus AS Status, InCh anger FROM Media,Pool,Storage WHERE Media.PoolId=Pool.PoolId AND InChanger=0 AND Media.StorageId=Storage.StorageId AND ( VolStatus IN ('Purged', 'Recycle') OR Pool.Name='Scratch') ORDER BY VolumeName; :List Volumes in Error status SELECT Media.MediaId AS MediaId,Pool.Name AS Pool,VolStatus AS Status FROM Media,Pool WHERE (VolStatus='Error') ORDER BY Pool.Name,MediaId; :List all places where a File is saved *Enter Filename (no path): SELECT DISTINCT Client.Name as Client, Path.Path,Filename.Name,File.MD5 as Checksum, Job.JobId as JobId FROM Client,Job,File,Filename,Path WHERE Client.ClientId=Job.ClientId AND JobStatus='T' AND Job.JobId=File.JobId AND Path.PathId=File.PathId AND Filename.FilenameId=File.FilenameId AND Filename.Name='%1' ORDER BY Path.Path; :Search for files with wildcards (% for *): *Enter Filename (no path): SELECT DISTINCT Job.JobId as JobId, Client.Name as Client, Path.Path,Filename.Name,StartTime FROM Client,Job,File,Filename,Path WHERE Client.ClientId=Job.ClientId AND Filename.Name like '%1' ORDER BY Filename.Name LIMIT 50; # AND JobStatus='T' AND Job.JobId=File.JobId # AND Path.PathId=File.PathId AND Filename.FilenameId=File.FilenameId = = In my setup, I need to rotate tapes on a weekly basis to = keep offsites in case of emergency, so I need to find certain = groups of tapes for easy removal and it's easier to group = them in query output than having to scan down a 57 item long = list and pick out the ones I need (and other similar needs). = = I hope someone finds this useful, = -John = [SNIP!] = -- = Do not try the patience of wizards, = for they are subtle and quick to anger. = --- = John M. Lockard | U of Michigan - School of Information = Unix and Security Admin | 1214 SI North - 1075 Beal Ave. = jlock...@umich.edu |Ann Arbor, MI 48109-2112 = www.umich.edu/~jlockard | 734-615-8776 | 734-647-8045 FAX = --- = = -- [Advertisement from SourceForge SNIPPED!] Mark Bergman voice: 215-662-7310 mark.berg...@uphs.upenn.edu fax: 215-614-0266 System Administrator Section of Biomedical Image Analysis Department of RadiologyUniversity of Pennsylvania PGP Key: https://www.rad.upenn.edu/sbia/bergman The information contained in this e-mail message is intended only for the personal and confidential use of the recipient(s) named above. If the reader of this message is not the intended recipient or an agent responsible for delivering it to the intended recipient, you are hereby notified that you have received this document in error and that any review, dissemination, distribution, or copying of