On Tue, Aug 11, 2009 at 2:39 PM, John Lockard<jlockard AT umich DOT 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 AT lists.sourceforge DOT 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 AT lists.sourceforge DOT net
https://lists.sourceforge.net/lists/listinfo/bacula-users
|
