Re: SQL Query to get total bandwidth used per user per month.
Hyperlink Admin wrote: 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 radacctno each month ? Maybe a second table for radacct_historical_data would be suitalbe? Move the data to the historical table and then delete it from your working radacct table. So for example: radacct and radacct_historical_data and then have a script just adding the data to the end of the radacct_historical_data table each month, and clearing the radacct table ? Would I do this with a SQL query ? If so, what would the query look like ? Then I can just create a script executing the query every month. that would be something like: INSERT INTO radacct_historical SELECT * FROM radacct WHERE acctstoptime whenever; of course, it's still neccessary to remove the data from radacct. I'll let you figure that out. However, I think the whole exercise is possibly overkill. 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 SELECT * FROM (SELECT username, sum(acctinputoctets+acctoutputoctets) AS total FROM radacct GROUP BY username) AS fooradius- WHERE total 3221225472; Works great !!! Thank you ! 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. Let me rephrase - Ive never used any sort of sql. Most Ive done with databases is a little access database. 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. Well I'd suggest triggers which get fired on updates on the radacct table, which update a separate table with username, month, and data (plus whatever else you need to keep on a monthly basis). Your trigger would have the benefit of being fired automatically rather than relying on human intervention. Or you could use cron, and have an external script which checks the db regularly, and massages the data according to your needs. Ok, im kinda lost. Triggers ? How would I add a trigger ? How does it work ? Sorry, Like I said, im kind new to this The I would suggest you look a the documentation of your database of choice. If triggers are beyond your ken, use batch processing and cron. Thanks, Graeme - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
RE: SQL Query to get total bandwidth used per user per month.
-Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Graeme Lee Sent: 24 February 2005 02:52 AM To: freeradius-users@lists.freeradius.org Subject: Re: SQL Query to get total bandwidth used per user per month. Hyperlink Admin wrote: 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 radacctno each month ? Maybe a second table for radacct_historical_data would be suitalbe? Move the data to the historical table and then delete it from your working radacct table. So for example: radacct and radacct_historical_data and then have a script just adding the data to the end of the radacct_historical_data table each month, and clearing the radacct table ? Would I do this with a SQL query ? If so, what would the query look like ? Then I can just create a script executing the query every 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 (HY000): Invalid use of group function You need a sub-select SELECT * FROM (SELECT username, sum(acctinputoctets+acctoutputoctets) AS total FROM radacct GROUP BY username) AS foo radius- WHERE total 3221225472; Works great !!! Thank you ! 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. I'm using postgresql. Let me rephrase - Ive never used any sort of sql. Most Ive done with databases is a little access database. 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. Well I'd suggest triggers which get fired on updates on the radacct table, which update a separate table with username, month, and data (plus whatever else you need to keep on a monthly basis). Your trigger would have the benefit of being fired automatically rather than relying on human intervention. Or you could use cron, and have an external script which checks the db regularly, and massages the data according to your needs. Ok, im kinda lost. Triggers ? How would I add a trigger ? How does it work ? Sorry, Like I said, im kind new to this But thanks for the advise so far, I really appreciate it. 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 - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
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 radacctno 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 (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 (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 (HY000): Invalid use of group function SELECT username,sum(acctinputoctets+acctoutputoctets) FROM radacct WHERE sum(acctinputoctets+acctoutputoctets) 3221225472 GROUP BY username; ERROR (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
Re: SQL Query to get total bandwidth used per user per month.
Hyperlink Admin wrote: 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 radacctno each month ? Maybe a second table for radacct_historical_data would be suitalbe? Move the data to the historical table and then delete it from your working radacct table. 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 (HY000): Invalid use of group function You need a sub-select SELECT * FROM (SELECT username, sum(acctinputoctets+acctoutputoctets) AS total FROM radacct GROUP BY username) AS foo radius- WHERE total 3221225472; 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. I'm using postgresql. 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. Well I'd suggest triggers which get fired on updates on the radacct table, which update a separate table with username, month, and data (plus whatever else you need to keep on a monthly basis). Your trigger would have the benefit of being fired automatically rather than relying on human intervention. Or you could use cron, and have an external script which checks the db regularly, and massages the data according to your needs. 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
