Hi,
The sql queries that you have all shared are very much appreciated.
Now I'd like to share a couple of my own. Not quite as complex as yours
though!
This first one I run in a script, as a RunAfterJob for the last backup of
the night, the Catalog. The result of the script is then sent to Zabbix,
which then alerts me if the Scratch Pool is running low on tapes.
# 18
:List how many volumes in Scratch Pool
SELECT MediaId AS Id,
Pool.Name AS Pool
FROM Media,Pool
WHERE Media.PoolId=Pool.PoolId
AND Pool.Name='Scratch'
AND Slot>0 AND InChanger=1;
The second one is similar to the one above, but not necessarily as useful.
# 19
:List how many Appendable Volumes in Monthly Pool
SELECT MediaId AS Id,
Pool.Name AS Pool,
Volstatus
FROM Media,Pool
WHERE Media.PoolId=Pool.PoolId
AND Pool.Name='Monthly'
AND Volstatus<>'Archive'
AND Volstatus<>'Full'
AND Slot>0 AND InChanger=1;
Could be useful to someone.
Kind regards,
Ken Barclay
>>-----Original Message-----
>>From: John Lockard [mailto:jlockard AT umich DOT edu]
>>Sent: Saturday, 26 September 2009 5:00 AM
>>To: Bacula-Users
>>Subject: 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.
>> jlockard AT umich DOT edu | Ann Arbor, MI 48109-2112
>> www.umich.edu/~jlockard | 734-615-8776 | 734-647-8045 FAX
>>-------------------------------------------------------------------
>>
>>-----------------------------------------------------------------------
>>-------
>>Come build with us! The BlackBerry® 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-12, 2009. Register
>>now!
>>http://p.sf.net/sfu/devconf
>>_______________________________________________
>>Bacula-users mailing list
>>Bacula-users AT lists.sourceforge DOT net
>>https://lists.sourceforge.net/lists/listinfo/bacula-users
------------------------------------------------------------------------------
The Planet: dedicated and managed hosting, cloud storage, colocation
Stay online with enterprise data centers and the best network in the business
Choose flexible plans and management services without long-term contracts
Personal 24x7 support from experience hosting pros just a phone call away.
http://p.sf.net/sfu/theplanet-com
_______________________________________________
Bacula-users mailing list
Bacula-users AT lists.sourceforge DOT net
https://lists.sourceforge.net/lists/listinfo/bacula-users
|
