Re: [Bacula-users] query.sql suggestion

[Arno L. <al...@it...>]

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
> Bac...@li...
> https://lists.sourceforge.net/lists/listinfo/bacula-users

-- 
Arno Lehmann
IT-Service Lehmann
www.its-lehmann.de