Thanks a ton for your reply... I have testing it on test bed and results are satisfactory.
With previous version of script and after seeing your reply i was not able to come at comfortable level and hence need to validate before implementing on production from somebody. Thanks for sending the whole modified script. Actully i want to know the utilization of all the clients in retention window so that plan for expansion by knowing the weekly growth pattern and also keep a check if somebody abusing the resources. Regards, Vishal Veerkar On Fri, Jan 8, 2010 at 9:07 PM, Mike Holden <bac...@mikeholden.org> wrote: > vishal veerkar wrote: > > On Fri, Dec 18, 2009 at 5:13 PM, Mike Holden <bac...@mikeholden.org> > wrote: > > > >> vishal veerkar wrote: > >> > 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; > >> > >> You still have Pool.Name listed in the SELECT list but not in the GROUP > BY > >> clause. I > >> would suggest (to give standard SQL code), that you change the "GROUP BY > >> clause to > >> "GROUP BY Client.Name, Pool.Name". You might want to update the ORDER BY > in > >> the same > >> way. > > Hi, > > > > Actually i am not a SQL expert and hence if somebody would help me > modifying > > the query for getting the result it would be greatly helpful. > > > > Regards, > > > > Vishal Veerkar > > > > I recall that there were 3 or 4 replies to the thread after that one. Did > they not help > you? I thought a full query had been posted already. > > That was weeks ago, and I can't remember exactly what you were trying to > achieve. > > Based purely on the query in the code you quoted below, and my comments to > that, I would > suggest the following: > > 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.Name, Pool.Name > ORDER by Client.Name, Pool.Name; > > If I comment out the JobStatus and StartTime clauses, I get the following > output from my > bacula database: > +-----------+--------------+---------+-------------+ > | Client | Pool | Files | TotalSizeGb | > +-----------+--------------+---------+-------------+ > | bacula-fd | Default | 0 | 0.0000 | > | jewel-fd | Catalog | 62 | 9.1220 | > | jewel-fd | Daily | 223399 | 2.6881 | > | jewel-fd | Default | 70949 | 0.6031 | > | jewel-fd | Monthly | 2108854 | 51.6399 | > | jewel-fd | PhotoDaily | 12155 | 0.5386 | > | jewel-fd | PhotoMonthly | 265397 | 86.1008 | > | jewel-fd | PhotoWeekly | 200470 | 8.0242 | > | jewel-fd | Weekly | 340392 | 6.1736 | > | puzzle-fd | Daily | 4421 | 0.1966 | > | puzzle-fd | Monthly | 741538 | 6.7953 | > | puzzle-fd | Weekly | 3829 | 0.2202 | > | tux2-fd | Daily | 2216 | 0.0895 | > | tux2-fd | Default | 2418 | 1.1652 | > | tux2-fd | Monthly | 1360043 | 26.5587 | > | tux2-fd | Weekly | 337600 | 6.6119 | > +-----------+--------------+---------+-------------+ > > > However, like I say, I can't recall what you were trying to achieve, so I > can't say > whether that will give you what you want. If that doesn't help, then let me > know EXACTLY > what you are trying to achieve. > -- > Mike Holden > > -- 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
