calculating daily and monthly totals (SQL)

RouterOS General
1

Hi all,

We have a few MT’s in the field that use Radius (freeradius 1.04 and mysql) for accounting and PPPOE.

We set speeds, usernames and passwords in radcheck and radreply, all usage is also logged in the radacct table.

I’m trying to work out how the best way to retrieve daily totals is.

I read that some people run a script every day to terminate the PPPOE connection which then begins a new row in SQL, we’d rather not do this.

Does anyone have any advice or how do you do it ?

Thanks !

–
geebs.

2

Set a Radius Update interval, it will send updated radius accounting data every x seconds. We normally run on 5 minutes which works good.

Get some MySQL Queries going to query the data from your mysql accounting tables.

You’re done. Nothing required on MT.

3

yep I have the data being updated every 5 mins.

Only thing that worries me is that the session radacct table is constantly being updated and does not stop until the client dsconnects.

Let’s say someone has been connected for a week ?
Do you query SQL for 1 day, then query it the next day and subtract the next days total from the 2day totals to get your daily total ?

It seems like that is the long way if you know what I mean :slight_smile:

Isn’t there some sort of script or way I can get the radacct table to to move to the next row after midnight ?
That way if a client is connected for weeks, they would have a row for every day, makes it nice and easy !


Hope that all makes sense :slight_smile:

4

It’s SQL. You can do anything you want with the data… Think about it

Customer connects (RadAcct gets new row)
Midnight, Active RadAcct data gets copied to different table
Midnight, Active RadAcct data gets copied to different table
Midnight, Active RadAcct data gets copied to different table
Midnight, Active RadAcct data gets copied to different table
Midnight, Active RadAcct data gets copied to different table
…

Stats run on the different table, not RadAcct… It’s really simple. Just think for a while… No one is going to do your homework for you :slight_smile: You have more than enough data in RadAcct to ensure you map propper accounting records (complete or incomplete) with the correct connection in different table…

5

The dialupadmin web interface that comes with FreeRADIUS has scripts that do just that, hourly, daily and monthly.

6

OMG it’s been there all the time !
I just had a look in the dialupadmin folder :slight_smile:

Thanks for that !

7

I know where this guy is coming from…
We had and still have the same problem.

What we do is have all the records for Start, Checkpoint and Stop packets inserted into a MySQL table and run a script on it every 5 min that adds them up and subtracts one row to the next if the client never disconnected.

If he has then we reset the values in our temp table.

But we have a problem, every end of the month we need to reset all the temp values no mater what because hey it’s the end of the month and time for new records right?

But if someone has not disconnected in weeks and we have our temp records reset, when the Router send the new acct values it still has accumulated values from week ago if a client has never disconnected and this makes him look like he downloaded like 10gigs in like 5 min. and thus our script thinks he has gone over his limit and slows him down to turtle speed.

What i have been looking for, for a long time but can’t find it is a RouterOS script that can either disconnect all clients a midnight at the end of each month or reset all the counters on the Router ever so often.

This would be the end of it.

If someone can share this with me I would be glad to share our nifty script that we have worked on for months, it comes complete with STATS time online Uploaded, Downloaded keeps a record for each month so the user can go back and view past history and so on.

Thanks
Sorry for rambling on forever.

8

I’m running these scripts now, still seems not to tally up users who have not disconnected :frowning:

I’d love a script that disconnects users on a monthly basis.
Anyone have one ?

9

Start reading some FreeRadius documentation for a change.

When you’ve done that, look at rlm_sqlcounter, then, look at the exampe configurations that is provided with FreeRadius.

Then, grep for AcctMonthlyTotals, and have a look at that.

Then, you’d propably want to add the relavent attributes to your users’ profiles.

Then, read the entire documentation again, and memorise it whilst you’re busy.