|
Re: [Bacula-users] bconsole query for calculating client 7days utilization
2009-12-18 06:35:14
After trying some combination with other queries finally i got the break through...below query gives the output for All clients utilization in GB for specified time window...this may helpful for others
:List storage used by client and pool after specified time window
*Enter Start time in YYYY-MM-DD HH:MM:SS format:
*Enter End time in YYYY-MM-DD HH:MM:SS format:
SELECT Client.Name AS Client, Pool.Name as Pool,
sum(Job.JobFiles) as Files, sum(Job.JobBytes)/(1024*1024*1024) as TotalSizeGb
FROM Client, Job, Pool
WHERE Job.ClientID = Client.ClientID and Job.PoolID = Pool.PoolID
AND JobStatus='T'
AND Job.StartTime >= '%1'
AND Job.StartTime <= '%2'
GROUP by Client.ClientID
ORDER by Client.Name;
Cheers,
vishal
On Fri, Dec 18, 2009 at 4:31 PM, Dan Langille <dan AT langille DOT org> wrote:
vishal veerkar wrote:
Hi All,
I would like to calculate the last 7days total backup utilization of my clients. I presently using 2.2.8 with Mysql in catalog DB. I was trying to tweak one query from query.sql as below. Frankly speaking i am not a DB expert and just want to get the job done.
:List all backups for a Client after a specified time and size
*Enter Client Name:
*Enter time in YYYY-MM-DD HH:MM:SS format:
SELECT DISTINCT Job.JobId,Client.Name as Client,Level,StartTime,JobFiles,*sum(JobBytes)* as Totalsize,VolumeName
FROM Client,Job,JobMedia,Media
WHERE Client.Name='%1'
AND Client.ClientId=Job.ClientId
AND JobStatus='T'
AND JobMedia.JobId=Job.JobId AND JobMedia.MediaId=Media.MediaId
AND Job.StartTime >= '%2'
* GROUP BY Client.Name;*
Actually it gives the out put but the size does not match the actual size.
SELECT DISTINCT Job.JobId,Client.Name as Client,Level,StartTime,JobFiles,sum(JobBytes) as Totalsize,VolumeName
FROM Client,Job,JobMedia,Media
WHERE Client.Name='polo-fd'
AND Client.ClientId=Job.ClientId
AND JobStatus='T'
AND JobMedia.JobId=Job.JobId AND JobMedia.MediaId=Media.MediaId
AND Job.StartTime >= '2009-12-01'
GROUP BY Client.Name;
ERROR: column "job.jobid" must appear in the GROUP BY clause or be used in an aggregate function
It expected standard SQL.
So we change to Standard SQL by removing the columns we are not grouping on.
SELECT Client.Name as Client, sum(JobBytes) as Totalsize
FROM Client,Job,JobMedia,Media
WHERE Client.Name='polo-fd'
AND Client.ClientId=Job.ClientId
AND JobStatus='T'
AND JobMedia.JobId=Job.JobId AND JobMedia.MediaId=Media.MediaId
AND Job.StartTime >= '2009-12-01'
GROUP BY Client.Name;
client | totalsize
---------+-------------
polo-fd | 11971658478
(1 row)
--
With regards,
Vishal Veerkar
------------------------------------------------------------------------------
This SF.Net email is sponsored by the Verizon Developer Community
Take advantage of Verizon's best-in-class app development support
A streamlined, 14 day to market process makes app distribution fast and easy
Join now and get one step closer to millions of Verizon customers
http://p.sf.net/sfu/verizon-dev2dev _______________________________________________
Bacula-users mailing list
Bacula-users AT lists.sourceforge DOT net
https://lists.sourceforge.net/lists/listinfo/bacula-users
|
|
|
