On Thu, 6 Dec 2007, Bruno Friedmann wrote:
> Hi All,
>
> I'me looking for a way to emaulate the auto prune (automatic and for all
> media in all pools) command in director. To recover the same behaviour
> there's in version 1.38x - 2.0x.
>
> This is needed by some customer to know what media should be inserted
> for the evening night when they issue the status director on morning.
They don't need that to know what's needed to insert. What they need to
know is which volumes are past their retention period and eligiible for
pruning.
Here's what I use:
Query.sql
These 2 are standard, tweaked for published LTO aging parameters
# 15
:List Volumes Bacula thinks are in changer
SELECT VolumeName AS Volume,ROUND(VolBytes/(1024*1024*1024)) AS GB,Slot,
VolStatus AS Status,LEFT(LastWritten,10) AS LastWritten,
LEFT(FROM_UNIXTIME(UNIX_TIMESTAMP(Media.LastWritten) +
Media.VolRetention),10) AS Expiry
FROM Media,Pool,Storage
WHERE Media.PoolId=Pool.PoolId
AND Slot>0 AND InChanger=1
AND Media.StorageId=Storage.StorageId
ORDER BY MediaType ASC, Volstatus ASC, VolumeName ASC, Slot ASC;
# 16
:List Volumes likely to need replacement from age or errors
SELECT VolumeName AS Volume,VolMounts AS Mounts,VolErrors AS Errors,
VolWrites AS Writes,VolStatus AS Status
FROM Media
WHERE (VolErrors>0) OR (VolStatus='Error') OR (VolMounts>5000) OR
(VolStatus='Disabled') OR (VolWrites>399999999)
ORDER BY VolStatus ASC, VolErrors,VolMounts,VolumeName DESC;
# 18
:List Volumes Bacula thinks should be removed from changer
SELECT VolumeName,VolStatus AS Status, ROUND(VolBytes/(1024*1024*1024)) AS
GB,
LEFT(LastWritten,10) AS LastWritten,
LEFT(FROM_UNIXTIME(UNIX_TIMESTAMP(Media.LastWritten) +
Media.VolRetention),10) 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 * .98)), NOW()) > 5
AND ((VolStatus='Used') OR (VolStatus='Full')))
OR (VolStatus='Disabled')
OR (VolStatus='Error'))
ORDER BY VolumeName ASC, Slot ASC;
This lists volumes which will expire in the next 7-10 days
# 19
:List Volumes Bacula thinks are eligible for the changer
SELECT VolumeName,VolStatus as Status,
###Storage.Name AS Location,
ROUND(VolBytes/(1024*1024*1024)) AS GB,LEFT(LastWritten,10) AS
LastWritten,
LEFT(FROM_UNIXTIME(UNIX_TIMESTAMP(Media.LastWritten) +
Media.VolRetention),10) 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 * .98)), NOW()) <= 5)
ORDER BY VolumeName ASC, Pool.Name ASC, VolMounts ASC
I put them all into a shell script I call checklibrary:
# cat /usr/local/bin/checklibrary
#!/bin/sh
echo Tapes to add to changer:
echo -e query\\\n19 | /usr/sbin/bconsole -c /etc/bacula/bconsole.conf | grep
-B1 -C1 \|
echo Tapes to remove from changer:
echo -e query\\\n18 | /usr/sbin/bconsole -c /etc/bacula/bconsole.conf | grep
-B1 -C1 \|
echo Tapes currently in changer:
echo -e query\\\n15 | /usr/sbin/bconsole -c /etc/bacula/bconsole.conf | grep
-B1 -C1 \|
It's rough and ready but it works for me. :)
-------------------------------------------------------------------------
SF.Net email is sponsored by: The Future of Linux Business White Paper
from Novell. From the desktop to the data center, Linux is going
mainstream. Let it simplify your IT future.
http://altfarm.mediaplex.com/ad/ck/8857-50307-18918-4
_______________________________________________
Bacula-users mailing list
Bacula-users@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/bacula-users
