[Bacula-users] query.sql suggestion

[Alan B. <aj...@ms...>]

Thanks to David for the snippet this morning.

I wanted to find tapes which were expired (or very close to expired(*)) 
and have them loaded into the autochanger before they are purged, plus not 
have bacula suggest they be removed.


Here are the 2 snippets I suggest for this.

They're adaptions of the existing entries and the advantage of these is 
that an expired-but-not-purged tape will be flagged for inclusion in the 
changer _BEFORE_ Bacula asks for it.

:List Volumes Bacula thinks should be removed from changer
SELECT VolumeName,VolStatus, VolBytes/(1024*1024*1024) AS GB,
   LastWritten,
   FROM_UNIXTIME(UNIX_TIMESTAMP(Media.LastWritten) + Media.VolRetention) AS Expiry,
   Slot,Storage.Name AS Location
   FROM Media,Pool,Storage
   WHERE Media.PoolId=Pool.PoolId
   AND Slot>0 AND InChanger=1
   AND Media.StorageId=Storage.StorageId
   AND ((DATEDIFF(FROM_UNIXTIME(UNIX_TIMESTAMP(LastWritten)
             + (Media.VolRetention * .95)), NOW()) > 1
       AND ((VolStatus='Used') OR (VolStatus='Full')))
       OR (VolStatus='Disabled')
       OR (VolStatus='Error'))
   ORDER BY VolumeName ASC, Slot ASC;

#  19
:List Volumes Bacula thinks are eligible for the changer
SELECT VolumeName,VolStatus,Storage.Name AS Location,
   VolBytes/(1024*1024*1024) AS GB,LastWritten,
   FROM_UNIXTIME(UNIX_TIMESTAMP(Media.LastWritten) + Media.VolRetention) AS Expiry
   FROM Media,Pool,Storage
   WHERE Media.PoolId=Pool.PoolId
#  AND Media.StorageId=Storage.StorageId
   AND InChanger=0
   AND Storage.Name='MSL6000-changer'
   AND ((VolStatus='Purged') OR (VolStatus='Append') OR 
(VolStatus='Recycle')
         OR DATEDIFF(FROM_UNIXTIME(UNIX_TIMESTAMP(Media.LastWritten)
             + (Media.VolRetention * .95)), NOW()) <= 1)
   ORDER BY VolumeName ASC, Pool.Name ASC, VolMounts ASC


(*) The selection is 95% of the retention period, rather than Retention 
period - 7 days