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-us...@lists.sourceforge.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-devel mailing list Bacula-devel@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/bacula-devel
