On Tue, Aug 11, 2009 at 2:39 PM, John Lockard<jlock...@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 Slot>0 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 Slot>0 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 Slot>0 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 Slot>0 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