-----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Graeme Lee Sent: 24 February 2005 02:52 AM To: [email protected] 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 radacct<no> 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 1111 (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
