Hi,
28.06.2007 20:59,, Alan Brown wrote::
> 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
I experimented a bit, because I'm running MySQL in a version without
DATEDIFF, and came up with something more complicated, due to the time
arithmetic involved. Here is my starting point, in case anyone else
runs older MySQL catalogs:
select VolumeName, LastWritten from Media where VolStatus in
('Append','Used','Full') and
unix_timestamp(LastWritten)+VolRetention<unix_timestamp()+(60*60*24*7);
60*60*24*7 should be one week in seconds, by the way.
Arno
>
> -------------------------------------------------------------------------
> 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
--
Arno Lehmann
IT-Service Lehmann
www.its-lehmann.de
-------------------------------------------------------------------------
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
