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
-------------------------------------------------------------------------
This SF.net email is sponsored by DB2 Express
Download DB2 Express C - the FREE version of DB2 express and take
control of your XML. No limits. Just data. Click to get it now.
http://sourceforge.net/powerbar/db2/
_______________________________________________
Bacula-users mailing list
Bacula-users@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/bacula-users
