On Nov 21, 2011, at 6:20 PM, Dan Langille wrote:

> 
> On Nov 21, 2011, at 8:29 AM, Alan Brown wrote:
> 
>> 
>> I have an oddball request.
>> 
>> A user needs all copies of files matching a set of wildcards (eg: 
>> MER_RR__1PRACR200?????_??????_????????????_?????_?????_????_h17v13_BBDR.tgz
>> )
>> 
>> The catch is that they may be across any of 30 different 1Tb filesystems 
>> (each with its own backup job) and they were deleted about 6 months ago 
>> (Still on tape and in the database)
>> 
>> The most recent copy will do for each file.
>> 
>> Does anyone have any ideas on how I can do this without pulling my hair out?
> 
> 
> I think it needs to be two part.
> 
> 1 - SQL: construct a query to pull back a list of filenames and clients where 
> these files is backed up, grouped by client, ordered by date descending.

This might get you started:

SELECT 
  filename.name, 
  job.jobid, 
  job.job, 
  job.name, 
  job.type, 
  job.level, 
  client.name, 
  job.starttime, 
  job.endtime, 
  media.volumename, 
  file.fileid
FROM 
  public.filename, 
  public.file, 
  public.job, 
  public.client, 
  public.jobmedia, 
  public.media
WHERE 
  filename.filenameid = file.filenameid AND
  file.jobid = job.jobid AND
  job.clientid = client.clientid AND
  jobmedia.jobid = job.jobid AND
  media.mediaid = jobmedia.mediaid AND
  filename.name LIKE 're%.conf';

That will pull back all backups, not just the most recent for each filename.


The following will be the most recent backup for each filename that matches the 
pattern:

SELECT 
  filename.name, 
  max(job.starttime)
FROM 
  public.filename, 
  public.file, 
  public.job, 
  public.client, 
  public.jobmedia, 
  public.media
WHERE 
  filename.filenameid = file.filenameid AND
  file.jobid = job.jobid AND
  job.clientid = client.clientid AND
  jobmedia.jobid = job.jobid AND
  media.mediaid = jobmedia.mediaid AND
  filename.name LIKE 're%.conf'
  group by filename.name



Now, you just have to join the two to get the job IDs that you want:

select * from
(SELECT 
  filename.name, 
  job.jobid, 
  job.job, 
  job.name, 
  job.type, 
  job.level, 
  client.name, 
  job.starttime, 
  job.endtime, 
  media.volumename, 
  file.fileid
FROM 
  public.filename, 
  public.file, 
  public.job, 
  public.client, 
  public.jobmedia, 
  public.media
WHERE 
  filename.filenameid = file.filenameid AND
  file.jobid = job.jobid AND
  job.clientid = client.clientid AND
  jobmedia.jobid = job.jobid AND
  media.mediaid = jobmedia.mediaid AND
  filename.name LIKE 're%.conf') as alljobs

  JOIN

(SELECT 
  filename.name, 
  max(job.starttime) as starttime
FROM 
  public.filename, 
  public.file, 
  public.job, 
  public.client, 
  public.jobmedia, 
  public.media
WHERE 
  filename.filenameid = file.filenameid AND
  file.jobid = job.jobid AND
  job.clientid = client.clientid AND
  jobmedia.jobid = job.jobid AND
  media.mediaid = jobmedia.mediaid AND
  filename.name LIKE 're%.conf'
  group by filename.name) as jobtimes
ON alljobs.starttime = jobtimes.starttime

> 
> 2 - use that list as input to a shell script that drives bconsole
> 
> Does that get you started?  I can help with the SQL if you want.
> 
> -- 
> Dan Langille - http://langille.org
> 
> 
> ------------------------------------------------------------------------------
> All the data continuously generated in your IT infrastructure 
> contains a definitive record of customers, application performance, 
> security threats, fraudulent activity, and more. Splunk takes this 
> data and makes sense of it. IT sense. And common sense.
> http://p.sf.net/sfu/splunk-novd2d
> _______________________________________________
> Bacula-users mailing list
> Bacula-users AT lists.sourceforge DOT net
> https://lists.sourceforge.net/lists/listinfo/bacula-users

-- 
Dan Langille - http://langille.org


------------------------------------------------------------------------------
All the data continuously generated in your IT infrastructure 
contains a definitive record of customers, application performance, 
security threats, fraudulent activity, and more. Splunk takes this 
data and makes sense of it. IT sense. And common sense.
http://p.sf.net/sfu/splunk-novd2d
_______________________________________________
Bacula-users mailing list
Bacula-users AT lists.sourceforge DOT net
https://lists.sourceforge.net/lists/listinfo/bacula-users