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 Slot>0 AND InChanger=1
AND Media.StorageId=Storage.StorageId
AND ( (VolErrors>0) 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 Radiology University 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 this message is strictly prohibited. If you have
received this communication in error, please notify us immediately by e-mail,
and delete the original message.
------------------------------------------------------------------------------
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
