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

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. <rhowe AT bmtmarinerisk DOT 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 AT lists.sourceforge DOT net
https://lists.sourceforge.net/lists/listinfo/bacula-users