[Bacula-users] query for file sizes in a job
Hi. Based on the PHP script in the ancient e-mail thread https://sourceforge.net/p/bacula/mailman/message/28210999/ I tweaked it a bit for using with MySQL (the original was for SQLite). See it attached. -- Silver attachment: fileSizes.php -- Download BIRT iHub F-Type - The Free Enterprise-Grade BIRT Server from Actuate! Instantly Supercharge Your Business Reports and Dashboards with Interactivity, Sharing, Native Excel Exports, App Integration more Get technology previously reserved for billion-dollar corporations, FREE http://pubads.g.doubleclick.net/gampad/clk?id=164703151iu=/4140/ostg.clktrk___ Bacula-users mailing list Bacula-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/bacula-users
Re: [Bacula-users] query for file sizes in a job
Some more tweaks to the script. BTW, I think it would be a useful feature for Bacula-Web too, but it should have also some searching and pagination possibilities (should be relatively easy to add to this script too actually). -- Silver On 12/04/2014 12:42 PM, Silver Salonen wrote: Hi. Based on the PHP script in the ancient e-mail thread https://sourceforge.net/p/bacula/mailman/message/28210999/ I tweaked it a bit for using with MySQL (the original was for SQLite). See it attached. -- Silver attachment: fileSizes_mysql.php -- Download BIRT iHub F-Type - The Free Enterprise-Grade BIRT Server from Actuate! Instantly Supercharge Your Business Reports and Dashboards with Interactivity, Sharing, Native Excel Exports, App Integration more Get technology previously reserved for billion-dollar corporations, FREE http://pubads.g.doubleclick.net/gampad/clk?id=164703151iu=/4140/ostg.clktrk___ Bacula-users mailing list Bacula-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/bacula-users
Re: [Bacula-users] query for file sizes in a job
) {
die('Connection to database failed: ' .
$e-getMessage());
}
return $db;
}
private function get_job_details() {
$size = array();
$path = array();
$job_details = array();
$sql = 'SELECT File.Lstat as lstat, Path.Path as file_path,
Filename.name as file_name FROM File JOIN Path ON
Path.PathId=File.PathId JOIN Filename ON File.JobId=' . $this-jobid .
' AND Filename.FilenameId=File.FilenameId;';
$query = $this-get_db()-query($sql);
$rows = $query-fetchAll();
if(count($rows) === 0) {
die('JobId ' . $this-jobid . ' does not exists or does
not have any file.');
}
foreach ($rows as $key = $row) {
$lstat = $this-decode_bacula_lstat($row['lstat']);
$size = $this-setUnitForSize($lstat['size']);
$job_details[] = array('size' = $size, 'path' =
$row['file_path']
. $row['file_name']);
}
$this-sort_job_details($job_details);
return $job_details;
}
private function setUnitForSize($size) {
return bcdiv($size, $this-units[$this-unit]);
}
private function sort_job_details($job_details) {
for($i = 0; $i count($job_details); $i++) {
$size[$i] = $job_details[$i]['size'];
$path[$i] = $job_details[$i]['path'];
}
if($this-order === 'asc') {
array_multisort($size, SORT_ASC, $job_details);
} elseif($this-order === 'desc') {
array_multisort($size, SORT_DESC, $job_details);
}
}
public function print_job_details() {
$job_details = $this-get_job_details();
foreach($job_details as $val) {
print $val['size'] . ' | ' . $val['path'] . \n;
}
}
}
function usage() {
echo '
' . basename(__FILE__) . ' jobid sort unit
jobid - Job identifier
sort - sort order by filesize (asc or desc)
unit - unit for show (b - bytes, k - kilo, m - mega, g - giga, t - tera)
';
}
if(count($argv) === 4) {
list($script, $jobid, $order, $unit) = $argv;
$obj = new BaculaJobDetails($jobid, $order, $unit);
$obj-print_job_details();
} else {
usage();
}
?
-Original Message-
From: Stuart McGraw [mailto:smcg4...@frii.com]
Sent: Friday, October 07, 2011 10:30 AM
To: Bacula-users@lists.sourceforge.net
Subject: Re: [Bacula-users] query for file sizes in a job
On 10/06/2011 12:36 PM, Jeff Shanholtz wrote:
I'm currently tuning my exclude rules and one of the things I want to
do is make sure I'm not backing up any massive files that don't need
to be backed up. Is there any way to get bacula to list file sizes
along with the file names since llist doesn't do this?
The filesize and other file attributes are stored in
(psuedo?-)base-64 encoded form in the lstat field of the 'file' table of the
catalog database.
I ran into the same problem and, since I'm using Postgresql for my catalogs,
wrote a little pg extension function in C that is called with an lstat value
and the index number of the stat field wanted. This is used as a base to
define some one-line convenience functions like lstat_size(text),
lstat_mtime(text), etc, which then allows one to define views like:
CREATE VIEW v_files AS (
SELECT f.fileid,
f.jobid,
CASE fileindex WHEN 0 THEN 'X' ELSE ' ' END AS del,
lstat_size (lstat) AS size,
TIMESTAMP WITH TIME ZONE 'epoch' + lstat_mtime (lstat) *
INTERVAL '1 second' AS mtime,
p.path||n.name AS filename
FROM file f
JOIN path p ON p.pathid=f.pathid
JOIN filename n ON n.filenameid=f.filenameid);
which generates results like:
SELECT * FROM v_files WHERE ...whatever...;
fileid | jobid | del | size | mtime | filename
-+---+-+--++
-+---+-+--++
-+---+-+--++
2155605 | 1750 | | 39656 | 2011-10-06 21:18:17-06 |
/srv/backup/files-sdb1.txt
2155606 | 1750 | | 4096 | 2011-10-06 21:18:35-06 | /srv/backup/
2155607 | 1750 | X | 0 | 2011-10-05 19:59:34-06 |
/home/stuart/Maildir/new/1317866374.V803I580003M622752.soga.home
2155571 | 1749 | | 39553788 | 2011-10-05 21:24:16-06 |
/var/spool/bacula/bacula.dmp
2155565 | 1748 | | 39424 | 2011-10-05 20:24:49-06 |
c:/stuart/pmt.xls
2155566 | 1748 | | 1365 | 2011-10-05 21:22:42-06 |
c:/Local/bacula/data/pg_global.sql
Re: [Bacula-users] query for file sizes in a job
2011/10/6 Jeff Shanholtz jeffs...@shanholtz.com: I’m currently tuning my exclude rules and one of the things I want to do is make sure I’m not backing up any massive files that don’t need to be backed up. Is there any way to get bacula to list file sizes along with the file names since llist doesn’t do this? Google search for bacula base64 John -- All of the data generated in your IT infrastructure is seriously valuable. Why? It contains a definitive record of application performance, security threats, fraudulent activity, and more. Splunk takes this data and makes sense of it. IT sense. And common sense. http://p.sf.net/sfu/splunk-d2dcopy2 ___ Bacula-users mailing list Bacula-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/bacula-users
Re: [Bacula-users] query for file sizes in a job
I appreciate that, but either you misunderstood what I'm trying to do or I just can't seem to make sense of the search results I'm getting as they apply to my issue. I did see one web page that decodes the base64 string from a member of this mailing list, but that operates on a single base64 string, not on a whole job (and even if it did, I don't know how to get bacula to tell me the base64 strings). I want to either get a full list of files from a job complete with file sizes so I can sort on the file sizes, or query for files greater than a certain size. I also probably should have mentioned that I'm stuck on Bacula v3.03 because it runs on a windows server. Could you be a little more specific on what kind of answer I'm looking for in the google results? Thanks! -Original Message- From: John Drescher [mailto:dresche...@gmail.com] Sent: Friday, October 07, 2011 6:12 AM To: Jeff Shanholtz; bacula-users Subject: Re: [Bacula-users] query for file sizes in a job 2011/10/6 Jeff Shanholtz jeffs...@shanholtz.com: I'm currently tuning my exclude rules and one of the things I want to do is make sure I'm not backing up any massive files that don't need to be backed up. Is there any way to get bacula to list file sizes along with the file names since llist doesn't do this? Google search for bacula base64 John -- All of the data generated in your IT infrastructure is seriously valuable. Why? It contains a definitive record of application performance, security threats, fraudulent activity, and more. Splunk takes this data and makes sense of it. IT sense. And common sense. http://p.sf.net/sfu/splunk-d2dcopy2 ___ Bacula-users mailing list Bacula-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/bacula-users
Re: [Bacula-users] query for file sizes in a job
On Fri, Oct 7, 2011 at 12:51 PM, Jeff Shanholtz jeffs...@shanholtz.com wrote: I appreciate that, but either you misunderstood what I'm trying to do or I just can't seem to make sense of the search results I'm getting as they apply to my issue. I did see one web page that decodes the base64 string from a member of this mailing list, but that operates on a single base64 string, not on a whole job (and even if it did, I don't know how to get bacula to tell me the base64 strings). I want to either get a full list of files from a job complete with file sizes so I can sort on the file sizes, or query for files greater than a certain size. I also probably should have mentioned that I'm stuck on Bacula v3.03 because it runs on a windows server. Could you be a little more specific on what kind of answer I'm looking for in the google results? I believe you need to write a query that for every file it decodes the base64 strings. I remember this discussion although it has been a long time so I do not remember the details. I would normally try to track this down and help you out however I am swamped so for now this is all I can do.. John -- All of the data generated in your IT infrastructure is seriously valuable. Why? It contains a definitive record of application performance, security threats, fraudulent activity, and more. Splunk takes this data and makes sense of it. IT sense. And common sense. http://p.sf.net/sfu/splunk-d2dcopy2 ___ Bacula-users mailing list Bacula-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/bacula-users
Re: [Bacula-users] query for file sizes in a job
On 10/06/2011 12:36 PM, Jeff Shanholtz wrote: I’m currently tuning my exclude rules and one of the things I want to do is make sure I’m not backing up any massive files that don’t need to be backed up. Is there any way to get bacula to list file sizes along with the file names since llist doesn’t do this? The filesize and other file attributes are stored in (psuedo?-)base-64 encoded form in the lstat field of the 'file' table of the catalog database. I ran into the same problem and, since I'm using Postgresql for my catalogs, wrote a little pg extension function in C that is called with an lstat value and the index number of the stat field wanted. This is used as a base to define some one-line convenience functions like lstat_size(text), lstat_mtime(text), etc, which then allows one to define views like: CREATE VIEW v_files AS ( SELECT f.fileid, f.jobid, CASE fileindex WHEN 0 THEN 'X' ELSE ' ' END AS del, lstat_size (lstat) AS size, TIMESTAMP WITH TIME ZONE 'epoch' + lstat_mtime (lstat) * INTERVAL '1 second' AS mtime, p.path||n.name AS filename FROM file f JOIN path p ON p.pathid=f.pathid JOIN filename n ON n.filenameid=f.filenameid); which generates results like: SELECT * FROM v_files WHERE ...whatever...; fileid | jobid | del | size | mtime | filename -+---+-+--++ 2155605 | 1750 | |39656 | 2011-10-06 21:18:17-06 | /srv/backup/files-sdb1.txt 2155606 | 1750 | | 4096 | 2011-10-06 21:18:35-06 | /srv/backup/ 2155607 | 1750 | X |0 | 2011-10-05 19:59:34-06 | /home/stuart/Maildir/new/1317866374.V803I580003M622752.soga.home 2155571 | 1749 | | 39553788 | 2011-10-05 21:24:16-06 | /var/spool/bacula/bacula.dmp 2155565 | 1748 | |39424 | 2011-10-05 20:24:49-06 | c:/stuart/pmt.xls 2155566 | 1748 | | 1365 | 2011-10-05 21:22:42-06 | c:/Local/bacula/data/pg_global.sql 2155567 | 1748 | | 45197314 | 2011-10-05 21:23:07-06 | c:/Local/bacula/data/pg_jmdict.dmp I've found it very convenient and will be happy to pass it on to anyone interested but have to add a disclaimer is that this was the first time I've used C in 20 years, first time I ever wrote a PG extension function and first time I ever looked at the Bacula source code, so be warned. :-) -- All of the data generated in your IT infrastructure is seriously valuable. Why? It contains a definitive record of application performance, security threats, fraudulent activity, and more. Splunk takes this data and makes sense of it. IT sense. And common sense. http://p.sf.net/sfu/splunk-d2dcopy2 ___ Bacula-users mailing list Bacula-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/bacula-users
Re: [Bacula-users] query for file sizes in a job
Am 07.10.2011 19:43, schrieb John Drescher: On Fri, Oct 7, 2011 at 12:51 PM, Jeff Shanholtz jeffs...@shanholtz.com wrote: I appreciate that, but either you misunderstood what I'm trying to do or I just can't seem to make sense of the search results I'm getting as they apply to my issue. I did see one web page that decodes the base64 string from a member of this mailing list, but that operates on a single base64 string, not on a whole job (and even if it did, I don't know how to get bacula to tell me the base64 strings). I want to either get a full list of files from a job complete with file sizes so I can sort on the file sizes, or query for files greater than a certain size. I also probably should have mentioned that I'm stuck on Bacula v3.03 because it runs on a windows server. Could you be a little more specific on what kind of answer I'm looking for in the google results? I believe you need to write a query that for every file it decodes the base64 strings. I remember this discussion although it has been a long time so I do not remember the details. I would normally try to track this down and help you out however I am swamped so for now this is all I can do.. John You are correct. There is a field called 'lstat' in the 'file' table that contains base64 encoded file attributes. The file size is somewhere in there. I think the function in the bacula source to decode that base64 string is called 'decode_stat' (don't know where it sits exactly; grep should help). Regards, Christian Manal -- All of the data generated in your IT infrastructure is seriously valuable. Why? It contains a definitive record of application performance, security threats, fraudulent activity, and more. Splunk takes this data and makes sense of it. IT sense. And common sense. http://p.sf.net/sfu/splunk-d2dcopy2 ___ Bacula-users mailing list Bacula-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/bacula-users
Re: [Bacula-users] query for file sizes in a job
Thanks guys. I'm pretty sure I'm using sqlite (having a hard time determining that definitively, but I don't think I did anything from an installation point of view beyond just installing bacula). I assume this script is postgresql specific. Looks like the fastest option for me is going to be to simply search the drives of my 3 client systems for large files and then check to see if any of those files are being backed up when they don't need to be. -Original Message- From: Stuart McGraw [mailto:smcg4...@frii.com] Sent: Friday, October 07, 2011 10:30 AM To: Bacula-users@lists.sourceforge.net Subject: Re: [Bacula-users] query for file sizes in a job On 10/06/2011 12:36 PM, Jeff Shanholtz wrote: I'm currently tuning my exclude rules and one of the things I want to do is make sure I'm not backing up any massive files that don't need to be backed up. Is there any way to get bacula to list file sizes along with the file names since llist doesn't do this? The filesize and other file attributes are stored in (psuedo?-)base-64 encoded form in the lstat field of the 'file' table of the catalog database. I ran into the same problem and, since I'm using Postgresql for my catalogs, wrote a little pg extension function in C that is called with an lstat value and the index number of the stat field wanted. This is used as a base to define some one-line convenience functions like lstat_size(text), lstat_mtime(text), etc, which then allows one to define views like: CREATE VIEW v_files AS ( SELECT f.fileid, f.jobid, CASE fileindex WHEN 0 THEN 'X' ELSE ' ' END AS del, lstat_size (lstat) AS size, TIMESTAMP WITH TIME ZONE 'epoch' + lstat_mtime (lstat) * INTERVAL '1 second' AS mtime, p.path||n.name AS filename FROM file f JOIN path p ON p.pathid=f.pathid JOIN filename n ON n.filenameid=f.filenameid); which generates results like: SELECT * FROM v_files WHERE ...whatever...; fileid | jobid | del | size | mtime | filename -+---+-+--++ -+---+-+--++ -+---+-+--++ 2155605 | 1750 | |39656 | 2011-10-06 21:18:17-06 | /srv/backup/files-sdb1.txt 2155606 | 1750 | | 4096 | 2011-10-06 21:18:35-06 | /srv/backup/ 2155607 | 1750 | X |0 | 2011-10-05 19:59:34-06 | /home/stuart/Maildir/new/1317866374.V803I580003M622752.soga.home 2155571 | 1749 | | 39553788 | 2011-10-05 21:24:16-06 | /var/spool/bacula/bacula.dmp 2155565 | 1748 | |39424 | 2011-10-05 20:24:49-06 | c:/stuart/pmt.xls 2155566 | 1748 | | 1365 | 2011-10-05 21:22:42-06 | c:/Local/bacula/data/pg_global.sql 2155567 | 1748 | | 45197314 | 2011-10-05 21:23:07-06 | c:/Local/bacula/data/pg_jmdict.dmp I've found it very convenient and will be happy to pass it on to anyone interested but have to add a disclaimer is that this was the first time I've used C in 20 years, first time I ever wrote a PG extension function and first time I ever looked at the Bacula source code, so be warned. :-) -- All of the data generated in your IT infrastructure is seriously valuable. Why? It contains a definitive record of application performance, security threats, fraudulent activity, and more. Splunk takes this data and makes sense of it. IT sense. And common sense. http://p.sf.net/sfu/splunk-d2dcopy2 ___ Bacula-users mailing list Bacula-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/bacula-users -- All of the data generated in your IT infrastructure is seriously valuable. Why? It contains a definitive record of application performance, security threats, fraudulent activity, and more. Splunk takes this data and makes sense of it. IT sense. And common sense. http://p.sf.net/sfu/splunk-d2dcopy2 ___ Bacula-users mailing list Bacula-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/bacula-users
[Bacula-users] query for file sizes in a job
I'm currently tuning my exclude rules and one of the things I want to do is make sure I'm not backing up any massive files that don't need to be backed up. Is there any way to get bacula to list file sizes along with the file names since llist doesn't do this? -- All the data continuously generated in your IT infrastructure contains a definitive record of customers, application performance, security threats, fraudulent activity and more. Splunk takes this data and makes sense of it. Business sense. IT sense. Common sense. http://p.sf.net/sfu/splunk-d2dcopy1___ Bacula-users mailing list Bacula-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/bacula-users
