On Aug 6, 2014, at 12:11 PM, John Wallach <j...@democracynow.org> wrote:
> Greetings,
>
> Our ancient autoloader decided to render a particular tape inoperable.
> Before my time jobs were run that spanned multiple volumes, the
> destroyed tape was in one such job.
>
> I cannot figure out how to find the files associated with the specific
> media. Here's a query that I thought would work but returns the same for
> any media id provided:
> SELECT t3.Path, t1.Name FROM Filename AS t1 JOIN File as t2 ON
> t1.FilenameId = t2.FilenameId JOIN Path as t3 ON t3.PathId = t2.PathId
> JOIN JobMedia as t4 ON t2.JobId = t4.JobId WHERE t4.MediaId = 475 AND
> t2.JobId = 8876 LIMIT 10;
SELECT t3.Path, t1.Name
FROM Filename AS FN JOIN File as F ON FN.FilenameId = F.FilenameId
JOIN Path as P ON P.PathId = F.PathId
JOIN JobMedia as JB ON F.JobId = JB.JobId
WHERE JB.MediaId = 475
AND F.JobId = 8876
LIMIT 10;
Restructured the query to improve readability. Not tested.
Combining your query and my blog post:
You already know the Job: 8876.
Mine is 181425
You already know the Volume name. Let’s get the volume id (mediaid):
bacula=# select MediaId, VolumeName from Media where MediaId in (select
distinct(MediaId) from JobMedia where JobId = 181425);
mediaid | volumename
---------+-------------------------
2929 | IncrAutoNoNextPool-2929
(1 row)
So just select the entries from the file table:
bacula=# select fileid, filenameid, pathid from file where jobid = 181425 limit
10;
fileid | filenameid | pathid
-----------+------------+---------
527206021 | 8285311 | 1473156
527206020 | 291 | 1473278
527206019 | 2964380 | 1473278
527206018 | 2964379 | 1473278
527206017 | 2964378 | 1473278
527206016 | 2964376 | 1473278
527206015 | 291 | 1473025
527206014 | 2964380 | 1473025
527206013 | 2964379 | 1473025
527206012 | 2964376 | 1473025
(10 rows)
bacula=#
Now let’s get the names and paths:
SELECT F.fileid, F.filenameid, F.pathid, P.path || '/' || FN.name
FROM file F JOIN filename FN on F.filenameid = FN.filenameid
JOIN path P ON F.pathid = P.pathid
WHERE jobid = 181425
LIMIT 10;
bacula-# LIMIT 10;
fileid | filenameid | pathid |
?column?
-----------+------------+---------+-----------------------------------------------------------------------------------
527206021 | 8285311 | 1473156 | usr/home/dan//.mailfilter.log
527206020 | 291 | 1473278 | usr/home/dan/Maildir//
527206019 | 2964380 | 1473278 | usr/home/dan/Maildir//dovecot.index.log
527206018 | 2964379 | 1473278 | usr/home/dan/Maildir//dovecot.index.cache
527206017 | 2964378 | 1473278 | usr/home/dan/Maildir//dovecot.index
527206016 | 2964376 | 1473278 | usr/home/dan/Maildir//dovecot-uidlist
527206015 | 291 | 1473025 | usr/home/dan/Maildir/.Trash//
527206014 | 2964380 | 1473025 |
usr/home/dan/Maildir/.Trash//dovecot.index.log
527206013 | 2964379 | 1473025 |
usr/home/dan/Maildir/.Trash//dovecot.index.cache
527206012 | 2964376 | 1473025 | usr/home/dan/Maildir/.Trash//dovecot-uidlist
(10 rows)
bacula=#
Hope that helps
—
Dan Langille
signature.asc
Description: Message signed with OpenPGP using GPGMail
------------------------------------------------------------------------------ Infragistics Professional Build stunning WinForms apps today! Reboot your WinForms applications with our WinForms controls. Build a bridge from your legacy apps to the future. http://pubads.g.doubleclick.net/gampad/clk?id=153845071&iu=/4140/ostg.clktrk
_______________________________________________ Bacula-users mailing list Bacula-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/bacula-users
