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 <d...@langille.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. >> > > MySQL silently does unexpected things. PostgreSQL does this: > > > 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@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/bacula-users
