Add the attached queries to query.sql
They are postgresql-specific and may not work first time on Mysql (but
they started out as mysql queries)
I have a few ancilliary (nasty, horrible spaghetti code) bash scripts
which will load/unload a changer from/to the magazine/IOslots(*) and to
tell operators from commandline (or piped into email) which tapes should
be pulled from the datasafe (oldest first)
I'm sure they can be improved but I'm willing to post them here as a
starting point if someone's interested.
:List Media for a pool, ordered by LastWritten
SELECT Name AS PoolName
FROM Pool ;
*Enter Pool name:
SELECT VolumeName,
lpad(text(round(VolBytes/1024.00/1024.00/1024.00,1)),7,' ') AS GB,
date(LastWritten) as LastWritten,
VolStatus as Status,
date(Media.LastWritten + interval '1 second' * Media.VolRetention) AS
Expiry,
InChanger
FROM Media,Pool where Media.PoolId=Pool.PoolId AND Pool.Name='%1'
ORDER BY LastWritten,VolumeName;
:List Volumes Bacula thinks should be removed from changer
SELECT Slot, VolumeName,VolStatus AS Status,
lpad(text(round(VolBytes/(1024.00*1024.00*1024.00),1)),7,' ') AS GB,
date(LastWritten) AS LastWritten,
date(Media.LastWritten + interval '1 second' * Media.VolRetention) AS
Expiry
##,Storage.Name AS Location
FROM Media,Pool,Storage
WHERE Media.PoolId=Pool.PoolId
AND ((VolStatus='Used') OR (VolStatus='Full') OR
(VolStatus='Disabled') OR (VolStatus='Error'))
AND Slot>0 and Slot<500 AND InChanger=1
AND Media.StorageId=Storage.StorageId
AND date(Media.LastWritten + interval '1 second' * Media.VolRetention
* .98)-current_date > 5
ORDER BY VolumeName;
:List Volumes Bacula thinks are eligible for the changer (98%+ of
retention time passed AND will expire in the next 5 days or already expired)
SELECT DISTINCT VolumeName,VolStatus as Status,
##Storage.Name AS Location,
lpad(text(round(VolBytes/(1024.00*1024.00*1024.00),1)),7,' ') AS GB,
date(LastWritten) AS LastWritten,
date(Media.LastWritten + interval '1 second' * Media.VolRetention) AS
Expiry
FROM Media,Pool,Storage
WHERE Media.PoolId=Pool.PoolId
AND InChanger=0
AND ((VolStatus='Purged') OR (VolStatus='Append') OR
(VolStatus='Recycle')
OR date(Media.LastWritten + interval '1 second' * Media.VolRetention
* .98)-current_date <= 5)
ORDER BY VolumeName ASC ;
On 17/02/16 22:05, Kern Sibbald wrote:
> Hello Ana (and Heitor),
>
> Please note that at the current time, the list command is generic, and
> thus it only knows how to print character strings that have been
> returned by the SQL engine. Though there may be some way to tell SQL
> that the result we get for expiresin is a "duration" (note: very
> different from a date such as LastWritten), I do not know how to do it.
>
> Thus for the moment, we are limited to displaying SQL generated
> character strings in the form that SQL gives them to us.
>
> The main point of the new expiresin field is that it is not zero, the
> retention period has not expired. If it is a big positive number
> (number of seconds remaining before the volume expires), then the Volume
> will not be recycled.
>
> Many people forget that the retention period *begins* from the
> LastWritten time, which means that as long as you are writing on the
> Volume, nothing will be expired. Maybe devoting a bit of thought to
> that particular point, and what would happen if we changed it, would
> make retention periods easier to understand.
>
> Best regards,
> Kern
>
> On 02/16/2016 07:19 PM, Ana Emília M. Arruda wrote:
>> Hello Kern and Heitor,
>>
>> I can see the ExpiresIn field in Bacula 7.4.0 version.
>> Maybe the ExpiresIn value could be more useful if displayed in the same
>> format as the LastWritten field.
>>
>> Best regards,
>> Ana
>>
>> On Tue, Feb 16, 2016 at 2:46 AM, Heitor Faria <heitor AT bacula.com DOT br
>> <mailto:heitor AT bacula.com DOT br>> wrote:
>>
>> >>> Recently I got tired of doing the mental gymnastics to see
>> whenVolumes
>> >>> will expire and with Eric's SQL help, we modified the list
>> (andllist)
>> >>> media output to eliminate one or two of the columns in the caseof
>> list
>> >>> media, but to add an "expiresin" field, which makes it much easier
>> to
>> >>> see when a volume will expire.
>> >>>
>> >>> This code has been in the public git repository in Branch-7.4 along
>> with
>> >>> a number of bug fixes since the 7.4.0 release. If you are
>> interested in
>> >>> simplifying the Volume expiration mind gymnastics you might
>> checkout and
>> >>> try the new code.
>> >>>
>> >>> By the way, I thought that the above feature was added after the
>> 7.4.0
>> >>> release, but according to what I see in the repo, much to my
>> surprise it
>> >>> should also be in the released 7.4.0 version.
>> >>>
>> >>> I would be interested in any feedback.
>> >>
>> >> Hello, Kern: this feature is great and I'm thankful for it, but is
>> there a way
>> >> to make information human readable?
>> >
>> > What do you mean?
>> >
>> > Please show me what you currently see and what you prefer to see.
>>
>> Hello, Kern: sorry for being laconic.
>> When I mean "human readable" is in the sense of ls, df and other
>> Linux commands:
>>
>> "-h, --human-readable
>> print sizes in human readable format (e.g., 1K 234M 2G)"
>>
>> *What I see today:*
>>
>> *list media pool=File
>>
>> +---------+------------+-----------+---------+---------------+----------+--------------+---------+------+-----------+-----------+---------------------+------------+
>> | MediaId | VolumeName | VolStatus | Enabled | VolBytes |
>> VolFiles | VolRetention | Recycle | Slot | InChanger | MediaType |
>> LastWritten | ExpiresIn |
>>
>> +---------+------------+-----------+---------+---------------+----------+--------------+---------+------+-----------+-----------+---------------------+------------+
>> | 1 | Vol-0001 | Error | 1 | 1,286,119,412 |
>> 0 | 31,536,000 | 1 | 0 | 0 | File1 |
>> 2015-10-21 23:57:35 | 21,428,702 |
>>
>> +---------+------------+-----------+---------+---------------+----------+--------------+---------+------+-----------+-----------+---------------------+------------+
>>
>> *What I think would make the user life easier:*
>>
>> *list media pool=File human
>>
>> +---------+------------+-----------+---------+----------+----------+--------------+---------+------+-----------+-----------+---------------------+------------+
>> | MediaId | VolumeName | VolStatus | Enabled | VolBytes | VolFiles |
>> VolRetention | Recycle | Slot | InChanger | MediaType | LastWritten
>> | ExpiresIn |
>>
>> +---------+------------+-----------+---------+---------------+----------+--------------+---------+------+-----------+-----------+---------------------+------------+
>> | 1 | Vol-0001 | Error | 1 | 1,29 GB | 0
>> | 31,536,000 | 1 | 0 | 0 | File1 | 2015-10-21
>> 23:57:35 | 248 days |
>>
>> +---------+------------+-----------+---------+----------+----------+--------------+---------+------+-----------+-----------+---------------------+------------+
>>
>> > Best regards,
>> > Kern
>>
>> Regards,
>> --
>>
>> ===========================================================================
>> Heitor Medrado de Faria - LPIC-III | ITIL-F | Bacula Systems
>> Certified Administrator II
>> Do you need Bacula training? http://bacula.us/video-classes/
>> +55 61 8268-4220 <tel:%2B55%2061%208268-4220>
>> Site: http://bacula.us FB: heitor.faria
>>
>> ===========================================================================
>>
>>
>> ------------------------------------------------------------------------------
>> Site24x7 APM Insight: Get Deep Visibility into Application Performance
>> APM + Mobile APM + RUM: Monitor 3 App instances at just $35/Month
>> Monitor end-to-end web transactions and take corrective actions now
>> Troubleshoot faster and improve end-user experience. Signup Now!
>> http://pubads.g.doubleclick.net/gampad/clk?id=272487151&iu=/4140
>> _______________________________________________
>> Bacula-users mailing list
>> Bacula-users AT lists.sourceforge DOT net
>> <mailto:Bacula-users AT lists.sourceforge DOT net>
>> https://lists.sourceforge.net/lists/listinfo/bacula-users
>>
>>
> ------------------------------------------------------------------------------
> Site24x7 APM Insight: Get Deep Visibility into Application Performance
> APM + Mobile APM + RUM: Monitor 3 App instances at just $35/Month
> Monitor end-to-end web transactions and take corrective actions now
> Troubleshoot faster and improve end-user experience. Signup Now!
> http://pubads.g.doubleclick.net/gampad/clk?id=272487151&iu=/4140
> _______________________________________________
> Bacula-users mailing list
> Bacula-users AT lists.sourceforge DOT net
> https://lists.sourceforge.net/lists/listinfo/bacula-users
>
>
------------------------------------------------------------------------------
Site24x7 APM Insight: Get Deep Visibility into Application Performance
APM + Mobile APM + RUM: Monitor 3 App instances at just $35/Month
Monitor end-to-end web transactions and take corrective actions now
Troubleshoot faster and improve end-user experience. Signup Now!
http://pubads.g.doubleclick.net/gampad/clk?id=272487151&iu=/4140
_______________________________________________
Bacula-users mailing list
Bacula-users AT lists.sourceforge DOT net
https://lists.sourceforge.net/lists/listinfo/bacula-users
|
