Re: [Bacula-users] List files associated with destroyed tape

On Aug 6, 2014, at 12:11 PM, John Wallach <john AT democracynow DOT 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 AT lists.sourceforge DOT net
https://lists.sourceforge.net/lists/listinfo/bacula-users