RE: SQL Query to get total bandwidth used per user per month.

Hi Guys,

Ok, what I would like to do is the following:

I would like to create a seperate radacct for each month.

It would be nice to have them going radacct1, raddact2, and so on. and then
when it get to next year this time, just carry on with radacct13, radacct14
and so on, otherwise Ill just restart with radacct1 next year.

Does anybody know how I would go about setting this up ? Would it be
possible to do this automatically ? or would I have to change the config
file to write to a different radacct<no> each month ?

Then, my second question:

I need to get monthly total bandwidth transfer (both incomming and outgoing
traffic) for each user. This I got figured out with the following SQL query:

SELECT username, sum(acctinputoctets+acctoutputoctets) AS total FROM radacct
GROUP BY username;

It works great, but to sit and filter through all the users will be a pain
each day.

I need it to only print the users that is over a cetain amount.

I have tried the following:

SELECT username, sum(acctinputoctets+acctoutputoctets) AS total FROM radacct
WHERE sum(acctinputoctets+acctoutputoctets) > 3221225472 GROUP BY username;

and here is the error:

ERROR 1111 (HY000): Invalid use of group function

I have also tried the following variations: (with each error)

SELECT username, sum(acctinputoctets+acctoutputoctets) AS total FROM radacct
WHERE sum(acctinputoctets+acctoutputoctets) > 3*1024*1024*1024 GROUP BY
username;
ERROR 1111 (HY000): Invalid use of group function

SELECT username,sum(acctinputoctets+acctoutputoctets) FROM radacct WHERE
sum(acctinputoctets+acctoutputoctets) > 3*1024*1024*1024 GROUP BY username;
ERROR 1111 (HY000): Invalid use of group function

SELECT username,sum(acctinputoctets+acctoutputoctets) FROM radacct WHERE
sum(acctinputoctets+acctoutputoctets) > 3221225472 GROUP BY username;
ERROR 1111 (HY000): Invalid use of group function

SELECT username,sum(acctinputoctets+acctoutputoctets) FROM radacct WHERE
'sum(acctinputoctets+acctoutputoctets)' > 3*1024*1024*1024 GROUP BY
username;
Empty set (0.00 sec) (not really error, but not what I need)

SELECT username,sum(acctinputoctets+acctoutputoctets) FROM radacct WHERE
'sum(acctinputoctets+acctoutputoctets)' > 3221225472 GROUP BY username;
Empty set (0.00 sec) (not really error, but not what I need)

Im sure there is a way to do it, but I have never really worked with MySQL
or any queries. What I got here, I patched together from reading around on
the net and on this list.

Last thing. And here I think I am actually pushing my luck.

Will it be possible (when the previous query actaully works) to
automatically take all the users listed in the results of the previous query
and moved them from one group (profile) to another.



The thing is, we are reselling ADSL accounts for the local Telco company
here, and they are very strict on capping accounts on 3GB. So in order for
us to be able to provide the service I need to check total usage for each
user on a daily basis and if he is over the cap, he needs to be moved to a
much slower service.

I got the 2 different groups setup - capped and uncapped, and it is working
fine.

I would really appreciate it if someone could help me.

Thank you,

Jacqueco Peenz




-
List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html