Hi all, I'm curious to know what you use to manage your RADIUS database, particularly the accounting tables. For quite some time, I was using severely hacked versions of the dialup_admin scripts. Since then, I've written (ie. been writing) an ISP mgmt/accounting system that relies very heavily on the RADIUS accounting information for billing and statistics, but only after the data has been aggregated. During this project, I've also written a basic object-oriented RADIUS module that now houses the aggregation functionality, including being able to go back in time to re-aggregate and re-archive. Because I need to make some functional changes to it, I thought I'd ask here if others would be interested in such a Perl module (and its scripts), and if so, I'll extend it with further functionality after updating the code with the stuff I just didn't know then ;) Currently, it uses plain DBI and only supports MySQL, but I'm going to incorporate much from my other work whereby DBIx::Class is used (to more easily support multiple dbs), expand on the config file, the installer and the documentation. Is using RADIUS accounting stats for billing/logging even done anymore? Steve
Steve Bertrand wrote:
I'm curious to know what you use to manage your RADIUS database, particularly the accounting tables.
"cron", and custom Perl scripts.
Since then, I've written (ie. been writing) an ISP mgmt/accounting system that relies very heavily on the RADIUS accounting information for billing and statistics, but only after the data has been aggregated.
That would make about 4 admin systems, I think. It would be good to consolidate them. We could then replace the un-maintained "dialup-admin" with a newer, and maintained version.
Because I need to make some functional changes to it, I thought I'd ask here if others would be interested in such a Perl module (and its scripts), and if so, I'll extend it with further functionality after updating the code with the stuff I just didn't know then ;)
Yes. If it's useful, we'll add it to the main FreeRADIUS repository.
Currently, it uses plain DBI and only supports MySQL, but I'm going to incorporate much from my other work whereby DBIx::Class is used (to more easily support multiple dbs), expand on the config file, the installer and the documentation.
Is using RADIUS accounting stats for billing/logging even done anymore?
Many, many, people use it. But a lot of people have "historical" solutions that they are used to, so they don't develop newer solutions. Alan DeKok.
Steve Bertrand wrote:
Hi all,
I'm curious to know what you use to manage your RADIUS database, particularly the accounting tables.
For quite some time, I was using severely hacked versions of the dialup_admin scripts.
Since then, I've written (ie. been writing) an ISP mgmt/accounting system that relies very heavily on the RADIUS accounting information for billing and statistics, but only after the data has been aggregated.
I'm rehashing this post for a couple of reasons. Very lengthy, read at your own risk. I've dedicated a lot of time toward the RADIUS accounting aggregation project, and the project is becoming slimmer and more streamlined by the day. This post is organized into sections, for those that are/can: - WARNING - INTERESTED IN BW/TIME BILLING - PROVIDE DATA FOR TESTING - Perl HACKERS WHO WILL FIX MY CODE - OTHER NOTES # WARNING (un)Fortunately, "I'm not a programmer, I'm but a humble network engineer (tm)". There may be references within my explanations that may sway toward the understanding of a programmer, in which Google will easily help. Again, I'm not a programmer, so the real developers will likely laugh. This is ok, so long as I can make myself understandable to the non-developer FreeRADIUS user. # INTERESTED IN BW/TIME BILLING The radacct table in the RADIUS database grows at an enormously fast rate. Regardless of server resources, trying to search or perform actions on this table can be a daunting task. My objective is to consolidate this data into daily database tables that aggregate the information by user and nas address. This daily info can then be aggregated further into a monthly table, which is ideal for ISP billing purposes. To further, I found that billing was easiest if NAS/RAS's could be 'classified' into a named group, based on their IP/IPv6 address and/or their DNS names into classes, so that for example, your ADSL access gear within 192.168.0.0/24 and 172.16.0.0/25 could be classified and then aggregated into the 'highspeed' class. This would allow the username 'steveb' to have all access data aggregated into a named class, so his high speed usage billing would be bandwidth based, but if he dialed in via traditional POTS with the same username as well, he could also be identified and billed as a dial-up user in within a different NAS pool as well. Much work has gone into the classification system, and user-defined 'classes' can be defined dynamically via a standard configuration file. The entire philosophy of this software is to make it easier for *SPs to better manage their accounting data, and make billing and reporting as simple as possible. # PROVIDE DATA FOR TESTING I've tested, updated POD, tested, updated more POD, made revision after revision, and tested again using FreeRADIUS data that dates back to 2004. The data I've been testing with is our own. Much of it has been through multiple schema changes, server relocations etc, but it still works. I'm looking for data (preferably dumped from mysqladmin, or entire tables), that I can throw at this software so I'm not continuously working with my own data. Raw data is preferable, but I can work with sanitized data. I'm also willing to work with an NDA for testing, and will even guarantee that data will be tested in a non-connected environment if specified. Data from the radacct table is all that is required. I can specify SSH/PGP etc if required. Email me off-list if you can help. # Perl HACKERS WHO WILL FIX MY CODE ...will receive SVN access to the repo, so long as they are willing to accept the fact that I'm not a programmer, and that I have my own bugzilla for bugs that I'll want to enter in manually. It will take me time to learn the development ropes. - the SQL is dbic'd, all but the 'archive' code, because I don't know how to create tables with dynamic names using DBIX::Class - MySQL replication can be configured via config file, so long as MySQL replication is pre-configured in MySQL - slave db servers can be added dynamically via config file - master write locking is possible via config file, and pushes reads to slave servers - alternate config files can be supplied via param or environment var - Perl 'make test' et-al occurs against a pre-defined dataset within an SQLite3 database that is generated via `perl Makefile.PL` - MySQL and SQLite have been thoroughly tested (for the archive tables. The read production radacct table is in MySQL) - currently, the software assumes that both the live radacct table, and the aggregation tables are in the same db (this will be changed tomorrow..?) - relatively full documentation, OO interface etc # OTHER NOTES Since I have more than this on the go, I've applied to PAUSE for a new top-level namespace (ISP::), but haven't heard back yet. I am seriously dedicated to my ISP project, and as such, creating a project that fulfills the need of *SPs billing purposes is of utmost importance. I hope that the simple Perl code I've written so far will inspire those who see it to help create a decisive method for RADIUS billing for the long term. I've used FreeRADIUS for years, and my ultimate hope is that what I'm trying to do will eventually help out a portion of the community. Thanks to Alan for his dedication to this project, Ivan for his seemingly endless supply of support, and everyone else here who supports this tremendously critical piece of infrastructure. Steve
Steve Bertrand wrote:
Again, I'm not a programmer, so the real developers will likely laugh.
Why? If it works... ship it. :)
The radacct table in the RADIUS database grows at an enormously fast rate. Regardless of server resources, trying to search or perform actions on this table can be a daunting task.
Yup. In some cases, people are required to keep this data for months.
Much work has gone into the classification system, and user-defined 'classes' can be defined dynamically via a standard configuration file.
Nice!
I'm looking for data (preferably dumped from mysqladmin, or entire tables), that I can throw at this software so I'm not continuously working with my own data. Raw data is preferable, but I can work with sanitized data. I'm also willing to work with an NDA for testing, and will even guarantee that data will be tested in a non-connected environment if specified.
See "radperf" (http://networkradius.com). It can generate fake login traffic, including accounting packets. It's a good tool for automated testing.
# Perl HACKERS WHO WILL FIX MY CODE
...will receive SVN access to the repo, so long as they are willing to accept the fact that I'm not a programmer, and that I have my own bugzilla for bugs that I'll want to enter in manually. It will take me time to learn the development ropes.
SVN? Uh... I'm not as mean as Linus is about it. But people are abandoning SVN in droves to move to git. The benefit is that you don't have to "give people access". Just put a repository on github, let people fork it, and the pull in the changes. Or, if you think this is useful to a wider community, we can host it on freeradius.org, and integrate it with the main releases.
- relatively full documentation, OO interface etc
Nice! Release it early, and get feedback from the community.
Since I have more than this on the go, I've applied to PAUSE for a new top-level namespace (ISP::), but haven't heard back yet. I am seriously dedicated to my ISP project, and as such, creating a project that fulfills the need of *SPs billing purposes is of utmost importance.
Leverage the FreeRADIUS name. :) Aln DeKok.
Alan DeKok wrote:
Steve Bertrand wrote:
The radacct table in the RADIUS database grows at an enormously fast rate. Regardless of server resources, trying to search or perform actions on this table can be a daunting task.
Yup. In some cases, people are required to keep this data for months.
I have all radacct information back to 1999 readily available, and data before that archived. It wasn't until ~2003 or so that I changed to using SQL. It didn't take long to realize that the SQL server became useless after only a few months.
I'm looking for data (preferably dumped from mysqladmin, or entire tables), that I can throw at this software so I'm not continuously working with my own data. Raw data is preferable, but I can work with sanitized data. I'm also willing to work with an NDA for testing, and will even guarantee that data will be tested in a non-connected environment if specified.
See "radperf" (http://networkradius.com). It can generate fake login traffic, including accounting packets. It's a good tool for automated testing.
Nice!
Or, if you think this is useful to a wider community, we can host it on freeradius.org, and integrate it with the main releases.
After thinking about it, this situation may be best. Considering that the project objective and purpose is designed around FreeRADIUS and it's SQL database, and really doesn't have much use elsewhere.
Leverage the FreeRADIUS name. :)
Net::FreeRADIUS::Management ? Steve
Steve Bertrand <steve@ibctech.ca> wrote:
The radacct table in the RADIUS database grows at an enormously fast rate. Regardless of server resources, trying to search or perform actions on this table can be a daunting task.
Yup. In some cases, people are required to keep this data for months.
I have all radacct information back to 1999 readily available, and data before that archived. It wasn't until ~2003 or so that I changed to using SQL. It didn't take long to realize that the SQL server became useless after only a few months.
I guess I am a 'small cheese' with only 2m rows in my authentication table? I am using PgSQL and have btree'd my timestamp columns, but it still takes only 3.6s to pull 10k rows worth of just todays data. I thought the point of index'ing your columns (sensibly) meant that the size of the table was not meant to really be too much of a killer? Cheers -- Alexander Clouter .sigmonster says: Cure the disease and kill the patient. -- Francis Bacon
Alexander Clouter wrote:
Steve Bertrand <steve@ibctech.ca> wrote:
The radacct table in the RADIUS database grows at an enormously fast rate. Regardless of server resources, trying to search or perform actions on this table can be a daunting task. Yup. In some cases, people are required to keep this data for months. I have all radacct information back to 1999 readily available, and data before that archived. It wasn't until ~2003 or so that I changed to using SQL. It didn't take long to realize that the SQL server became useless after only a few months.
I guess I am a 'small cheese' with only 2m rows in my authentication table? I am using PgSQL and have btree'd my timestamp columns, but it still takes only 3.6s to pull 10k rows worth of just todays data.
I thought the point of index'ing your columns (sensibly) meant that the size of the table was not meant to really be too much of a killer?
A single select isn't an issue: select * from radacct where acctstoptime > '2010-01-01'; 15633 rows in set (0.84 sec) The problem I've noticed is in complex queries. For instance, in our accounting system, when we display a plan for a user, it used to pull out all radacct info for the user for an entire year, and display this info as aggregates per each month. This becomes a nightmare in cases where an ADSL user has a gateway that disconnects automatically every five minutes, but auto reconnects right after. Another situation is if I want to mysqldump the radacct table. It takes far less time to do this on a table with three months of data as opposed to 12+. So, what I do is: - aggregate daily data into a separate table - aggregate the daily aggregated data into a separate monthly table - copy the radacct data to a new radacct-YYYYMM table monthly, then remove that months data from the radacct table (generally, I keep three months active) - the accounting system displays the user month totals from the month aggregated table, so it only has to select 12 rows. The month archive is run daily Steve
Hi,
I guess I am a 'small cheese' with only 2m rows in my authentication table? I am using PgSQL and have btree'd my timestamp columns, but it still takes only 3.6s to pull 10k rows worth of just todays data.
ah..you have the immediate advantage of postgreSQL
I thought the point of index'ing your columns (sensibly) meant that the size of the table was not meant to really be too much of a killer?
aye - but you also need a DB system that doesnt lock everything just because you looked at it (quick, take a peek while its not looking! ;)) it didnt take too long for me to suffer with MySQL as supplied so often by default - then i researched and moved to eg InnoDB engine...then I learnt about choosing indexes carefully - and how too many can kill you too. ...that came alongside the 'explain' command. then i moved to postgres - with that powering the system theres far less to wory about (espcially later versions that dont need a monthly VACUUM 8-) alan
Alan Buxey <A.L.M.Buxey@lboro.ac.uk> wrote:
I guess I am a 'small cheese' with only 2m rows in my authentication table? I am using PgSQL and have btree'd my timestamp columns, but it still takes only 3.6s to pull 10k rows worth of just todays data.
ah..you have the immediate advantage of postgreSQL
For me it's the CIDR syntax: SELECT * FROM dot1x_auth WHERE nas_ip_address <<= 1.2.0.0/16 Cheers -- Alexander Clouter .sigmonster says: The price of greatness is responsibility.
Steve Bertrand wrote:
Considering that the project objective and purpose is designed around FreeRADIUS and it's SQL database, and really doesn't have much use elsewhere.
Leverage the FreeRADIUS name. :)
Net::FreeRADIUS::Management ?
I'd say "Net::RADIUS::Management". Design the schema, etc. to deal with data from the protocol. Have the documentation, examples, etc. make it clear that the best product is FreeRADIUS. :) Alan DeKok.
Alan DeKok wrote:
Steve Bertrand wrote:
Considering that the project objective and purpose is designed around FreeRADIUS and it's SQL database, and really doesn't have much use elsewhere.
Leverage the FreeRADIUS name. :) Net::FreeRADIUS::Management ?
I'd say "Net::RADIUS::Management". Design the schema, etc. to deal with data from the protocol. Have the documentation, examples, etc. make it clear that the best product is FreeRADIUS. :)
Ok. I've bundled up what I have so far. If possible, it would be great if a few could install the software, if even to provide feedback on the documentation. Link at bottom of this message to tarball. Until I can confirm a namespace (I will go for Net::RADIUS::Management), I've left the namespace as ISP::RADIUS. The README contains info regarding the table schemas, and all the various bits of documentation for the module itself, the config file and the included utility scripts. There are some caveats at this time: - MySQL and SQLite are the only systems that have been tested. The SQL schemas themselves are for MySQL only. With that said, I can't see why it won't work with other SQL servers. (I plan on setting up a Postgres server to test with, but I've never used it before, so it may take me time). - the archive_radacct script (well, the archive_radacct() method) is not in a working state currently, until I can find a way to use DBIx::Class to create tables based on dynamic names - although I'm using this software in production, I wouldn't recommend using it on a critical production box until it has exposure in the field, and can be reviewed by Real Programmers ;) - the config file is generated during the Makefile.PL routine, and currently does not get written with an overly pretty format. This will be fixed shortly. With that said, the config file does have a man page - the nas classification config directives are not 100% accurate. The SQL lookups are done with a like% search, which is inaccurate. What I need to do implement a feature to be able to identify CIDR This is the first software I've ever put out to a large audience. All feedback and criticism is extremely welcome and hoped for. http://ipv6canada.com/ISP-RADIUS-0.0.2.tar.gz Cheers! Steve
participants (4)
-
Alan Buxey -
Alan DeKok -
Alexander Clouter -
Steve Bertrand