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