[Bacula-users] decoding lstat data

[Kelly, Brian]

I've seen lots of posts regarding the decoding of Lstat data. I'm currently
using a few functions to decoded the lstat data and make it human readable.
These functions have been tested and are known to work with mysql 5.0.70

delimiter |

DROP FUNCTION IF EXISTS MYBASE64_DECODE |

CREATE FUNCTION MYBASE64_DECODE (input BLOB)
        RETURNS BIGINT
        CONTAINS SQL
        DETERMINISTIC
        SQL SECURITY INVOKER
        
BEGIN
        IF input IS NULL THEN
                RETURN NULL;
        END IF;
        
        BEGIN
        DECLARE base10 BIGINT DEFAULT 0;
        DECLARE i INT DEFAULT 1;
        DECLARE length BIGINT DEFAULT 0;
        DECLARE tempval BIGINT UNSIGNED;
        DECLARE b64 VARCHAR(65) DEFAULT
'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvfwxyz0123456789+/';
        SET length = CHAR_LENGTH(input);
        
        WHILE i < length + 1 DO
                SET base10 = base10 + ((INSTR(b64, substring(input, i, 1)) -
1) * POWER(64, (length - i)));
                SET i = i + 1;
        END WHILE;
        
        RETURN base10;
        END;
END |


-- in the query pass it in the substring of the blob that you want


delimiter |

DROP FUNCTION IF EXISTS HUMAN_READABLE |

CREATE FUNCTION HUMAN_READABLE (bytes FLOAT(20, 2))
        RETURNS VARCHAR(50)
        CONTAINS SQL
        DETERMINISTIC
        SQL SECURITY INVOKER
        
BEGIN
        DECLARE frmtd VARCHAR(50);
        DECLARE tmpfloat FLOAT(20, 2);
        
        IF bytes < 1024 THEN 
                SET tmpfloat = bytes;
                SET frmtd = CONCAT(tmpfloat, ' B');
        ELSEIF bytes < 1048576 THEN     
                SET tmpfloat = bytes / 1024;
                SET frmtd = CONCAT(tmpfloat, ' KB');
        ELSEIF bytes < 1073741824 THEN 
                SET tmpfloat = bytes / 1048576;
                SET frmtd = CONCAT(tmpfloat, ' MB');
        ELSE 
                SET tmpfloat = bytes / 1073741824;
                SET frmtd = CONCAT(tmpfloat, ' GB');

        END IF;
        
        RETURN frmtd;
END ;

Here is an example. I have many Windows clients and I have a need to know
what file types a particular users Full backup consists. In this particular
situation it's too bad the MIME type for each file isn't stored in the
catalog.

# 36
:Full backup counts and sizes per Extension Type
*Enter Client Name:
select if (char_length(substring_index(Filename.Name, '.', -1)) in (3,4),
substring_index(Filename.Name, '.', -1), 'Invalid Extension') as Extension,
        count(*) as Count,
 
human_readable(sum(mybase64_decode(substring_index(substring_index(File.LSta
t, ' ', 8), ' ', -1)))) as Size
from (((select Job.JobId
        from Job join Client on Job.ClientId = Client.ClientId
        where Job.EndTime > (select Job.StartTime
                        from Job join Client on Job.ClientId =
Client.ClientId
                        where Client.Name = '%1'
                                and Job.Level = 'F'
                                and Job.JobStatus = 'T'
                        order by Job.StartTime desc
                        limit 1)
        and Client.Name = '%1') f1 join
                File on f1.JobId = File.JobId) join
                        Filename on File.FilenameId = Filename.FilenameId)
join
                                Path on File.PathId = Path.PathId
group by Extension order by
sum(mybase64_decode(substring_index(substring_index(File.LStat, ' ', 8), '
', -1))) desc;

Brian Kelly

smime.p7s
Description: S/MIME cryptographic signature

------------------------------------------------------------------------------
This SF.net email is sponsored by:
High Quality Requirements in a Collaborative Environment.
Download a free trial of Rational Requirements Composer Now!
http://p.sf.net/sfu/www-ibm-com
_______________________________________________
Bacula-users mailing list
Bacula-users@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/bacula-users