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

[Graeme Lee]

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.
 

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