Re: [Bacula-users] Anyone written any handy queries (query.sql)???

2009-09-25 Thread John Lockard 
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.
  jlock...@umich.edu |Ann Arbor, MI  48109-2112
 www.umich.edu/~jlockard | 734-615-8776 | 734-647-8045 FAX
---

--
Come build with us! The BlackBerryreg; 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#45;12, 2009. Register now#33;
http://p.sf.net/sfu/devconf
___
Bacula-users mailing list
Bacula-users@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/bacula-users

Re: [Bacula-users] Anyone written any handy queries (query.sql)???

2009-08-12 Thread Russell Howe 
John Lockard wrote, sometime around 11/08/09 19:39:
 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.

Some of mine may only work on PostgreSQL - I dunno..


:List the most recent full backups
SELECT DISTINCT jobname, endtime, volumename from (
SELECT
 lastfullbackups.JobId, JobName, ScheduledTime, StartTime, EndTime, 
volumename
   FROM
 (
   SELECT
 jobid AS JobId,
 job.name AS JobName,
 job.schedtime AS ScheduledTime,
 job.starttime AS StartTime,
 job.endtime AS EndTime
   FROM
 (
   SELECT
 name as Job, max(endtime) AS EndTime
   FROM
 job
 INNER JOIN
 jobinfo
 ON
 job.name = JI_jobname
   WHERE
 type = 'B'
 AND level = 'F'
 AND jobstatus = 'T'
 AND ji_old = false
   GROUP BY name
 ) lastfullbackups
   LEFT OUTER JOIN
 job
   ON
   lastfullbackups.Job = job.name
 AND
   lastfullbackups.EndTime = job.endtime
 ) lastfullbackups
   LEFT OUTER JOIN
 jobmedia
   ON
 lastfullbackups.jobid = jobmedia.jobid
   LEFT OUTER JOIN
 media
   ON
 jobmedia.mediaid = media.mediaid
 ) foo


:List last 20 Full Backups for a named job
*Enter Job name:
SELECT DISTINCT Job.JobId,Client.Name AS 
Client,Job.StartTime,JobFiles,JobBytes,
   VolumeName
  FROM Client INNER JOIN Job on Client.ClientId = Job.ClientId INNER 
JOIN JobMedia ON Job.JobId = JobMedia.JobId INNER JOIN Media on 
JobMedia.MediaId=Media.MediaId
  WHERE Job.Name='%1'
  AND Level='F' AND JobStatus='T'
  ORDER BY Job.StartTime DESC LIMIT 20;


Also, I wanted to know which tapes needed to be offsite when I was 
running a full  differential combination. To do this, I created a table 
listing all the jobs and whether they should be included or not as I 
have quite a few old jobs in the catalog which I don't really care about:


CREATE TABLE jobinfo (
ji_primary SERIAL,
ji_old  boolean DEFAULT FALSE,
ji_jobname  varchar(45)
);


List the last completed full backup for each job, and the tape it is on:

CREATE VIEW lastfullbackuptapes AS
  SELECT DISTINCT foo2.jobname, foo2.endtime, foo2.volumename
FROM ( SELECT lastfullbackups.jobid, jobname, scheduledtime, 
starttime, endtime, volumename
FROM ( SELECT jobid, lastfullbackups.job AS jobname, 
job.schedtime AS scheduledtime, job.starttime, job.endtime
FROM ( SELECT ji_jobname AS job, max(endtime) AS endtime
FROM jobinfo
   LEFT JOIN ( SELECT job.name, job.endtime
FROM job
   WHERE job.type = 'B'::bpchar AND 
job.level = 'F' AND job.jobstatus = 'T') fulljobs ON 
jobinfo.ji_jobname = fulljobs.name
  WHERE ji_old = false
  GROUP BY ji_jobname) lastfullbackups
   LEFT JOIN job ON lastfullbackups.job = job.name AND 
lastfullbackups.endtime = job.endtime) lastfullbackups
   LEFT JOIN jobmedia ON lastfullbackups.jobid = jobmedia.jobid
LEFT JOIN media ON jobmedia.mediaid = media.mediaid) foo2
   ORDER BY foo2.jobname, foo2.endtime, foo2.volumename;


List all completed differential backups since the last full backup for 
each job and the tape they are on (uses the above view):

CREATE VIEW lastdiffbackuptapes AS
  SELECT DISTINCT res.name, res.endtime, media.volumename
FROM lastfullbackuptapes
JOIN ( SELECT job.jobid, job.job, job.name, job.type, job.level, 
job.clientid, job.jobstatus, job.schedtime, job.starttime, job.endtime, 
job.jobtdate, job.volsessionid, job.volsessiontime, job.jobfiles, 
job.jobbytes, job.joberrors, job.jobmissingfiles, job.poolid, 
job.filesetid, job.purgedfiles, job.hasbase
 FROM job
WHERE job.type = 'B' AND job.jobstatus = 'T' AND 
job.level = 'D') res ON lastfullbackuptapes.jobname = res.name AND 
lastfullbackuptapes.endtime  res.starttime
LEFT JOIN jobmedia ON res.jobid = jobmedia.jobid
LEFT JOIN media ON jobmedia.mediaid = media.mediaid
   ORDER BY res.name, res.endtime, media.volumename;



List all tapes which should be offsite in order to maintain the most up 
to date complete backup (for each job the most recent full and all 
subsequent differentials):

  SELECT DISTINCT res.volumename
FROM ( SELECT lastfullbackuptapes.volumename
FROM lastfullbackuptapes
UNION
  SELECT lastdiffbackuptapes.volumename
FROM lastdiffbackuptapes) res
   ORDER BY res.volumename;


-- 
Russell Howe, IT Manager. rh...@bmtmarinerisk.com
BMT Marine  Offshore Surveys Ltd.

[Bacula-users] Anyone written any handy queries (query.sql)???

2009-08-11 Thread John Lockard 
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 Slot0 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 Slot0 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 Slot0 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.
  jlock...@umich.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@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/bacula-users

Re: [Bacula-users] Anyone written any handy queries (query.sql)???

2009-08-11 Thread John Drescher 
On Tue, Aug 11, 2009 at 2:39 PM, John Lockardjlock...@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 Slot0 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 Slot0 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 Slot0 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 Slot0 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

Re: [Bacula-users] Anyone written any handy queries (query.sql)???

2009-08-11 Thread Mike Eggleston 
On Tue, 11 Aug 2009, John Drescher might have said:

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

Here are my custom queries.

Mike


# 17
:List pool space allocated by status
SELECT Pool.Name AS Pool, VolStatus AS Status, sum(VolBytes) AS Size,
count(*) AS Number
  FROM Media, Pool
  WHERE Media.PoolID = Pool.PoolId
  GROUP by Pool.Name, VolStatus
  ORDER by Pool.Name, VolStatus;
# 18
:List Volumes by pool, name
SELECT VolumeName AS Volume, VolMounts AS Mounts, VolErrors AS Errors,
VolBytes AS Size, VolWrites AS Writes, VolStatus AS Status, Pool.Name AS 
Pool
  FROM Media, Pool
  WHERE Media.PoolID = Pool.PoolId
  ORDER by Pool.Name, VolumeName;
# 19
:List Volumes by pool, age
SELECT VolumeName AS Volume, VolMounts AS Mounts, VolErrors AS Errors,
VolBytes AS Size, VolWrites AS Writes, VolStatus AS Status,
LastWritten AS 'Last Written', Pool.Name AS Pool
  FROM Media, Pool
  WHERE Media.PoolID = Pool.PoolId
  ORDER by Pool.Name, LastWritten Desc;
# 20
:List storage used by client and pool
SELECT Client.Name AS Client, Pool.Name as Pool,
sum(Job.JobFiles) as Files, sum(Job.JobBytes) as Bytes
  FROM Client, Job, Pool
  WHERE Job.ClientID = Client.ClientID and Job.PoolID = Pool.PoolID
  GROUP by Client.ClientID
  ORDER by Client.Name;

--
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

Re: [Bacula-users] Anyone written any handy queries (query.sql)???

2009-08-11 Thread mark . bergman 


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 Slot0 AND InChanger=1
  AND Media.StorageId=Storage.StorageId
  AND ( (VolErrors0) 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 RadiologyUniversity 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