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;


-- 
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.
      jlockard AT umich DOT edu |        Ann Arbor, MI  48109-2112
 www.umich.edu/~jlockard |     734-615-8776 | 734-647-8045 FAX
-------------------------------------------------------------------

------------------------------------------------------------------------------
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 AT lists.sourceforge DOT net
https://lists.sourceforge.net/lists/listinfo/bacula-users