vishal veerkar wrote:
> On Fri, Dec 18, 2009 at 5:13 PM, Mike Holden <bacula AT mikeholden DOT 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