If you have any method of graphing resulting data, I would appreciate a holler
Joe
Dave Seddon wrote:
Greetings,
I'm new to the list. I have two issues:
-Problem logging accounting -Alive packet processing and integration with dial-up billing
I use Freeradius(7.0) with MySQL(3.23.54) on FreeBSD(4.7) to authenticate lots of xDSL PPP sessions via an L2TP tunnel terminated on a big Cisco box. It works very well, however for some reason accounting records do not get put in the 'radacct' mysql table. There are some records in the table, but no where near as many as their should be since Interim updates or Alive packets get sent by the Cisco every 10 minutes. However I do get all the accouting records in /var/log/radacct/ip_address/detail.
Here is some of the /usr/local/etc/raddb/radius.conf. The accounting section seems correct. The sql.conf is untouched from the example (except for the password and username).
----------------------------------------
authorize {
preprocess
suffix
sql
files
}
authenticate {
}
preacct {
preprocess
suffix
files
}
accounting {
detail
# unix
sql
radutmp
}
----------------------------------------
So what could be wrong?
To see what data I was getting in the detail log, I wote a little perl script to parse the detail log and stick the data in MySQL so I could easily do select statements. I discovered that the records I created where structured differently, so perhaps that's why it's not going to the Freeradius radacct table? Essentially, the difference is the "Tunnel" attributes.
The database structure I created is:
-----------------------------
drop database radiusaccounting;
create database radiusaccounting;
use radiusaccounting;
CREATE TABLE radacct (
RadAcctId int unsigned NOT NULL auto_increment,
NASIPAddress varchar(15) NOT NULL default '',
NASPortId tinyint unsigned default NULL,
NASPortType varchar(32) default NULL,
UserName varchar(64) NOT NULL default '',
AcctStatusType varchar(20) NOT NULL default '',
AcctAuthentic varchar(20) NOT NULL default '',
ServiceType varchar(32) default NULL,
AcctSessionID varchar(12) NOT NULL default '',
FramedProtocol varchar(6) default NULL,
TunnelServerEndpoint varchar(15) NOT NULL default '',
TunnelClientEndpoint varchar(15) NOT NULL default '',
TunnelType varchar(10) NOT NULL default '',
TunnelClientAuthID varchar(25) NOT NULL default '',
TunnelServerAuthID varchar(25) NOT NULL default '',
AcctTunnelConnection int unsigned default NULL,
FramedIPAddress varchar(15) NOT NULL default '',
AcctInputOctets int unsigned default NULL,
AcctOutputOctets int unsigned default NULL,
AcctInputPackets int unsigned default NULL,
AcctOutputPackets int unsigned default NULL,
AcctSessionTime int unsigned default NULL,
AcctDelayTime int unsigned default NULL,
ClientIPAddress varchar(15) NOT NULL,
TimeStamp bigint unsigned default NULL,
HumanTime varchar(10) default NULL,
PRIMARY KEY (RadAcctId),
KEY UserName (UserName)
);
---------------------
So I've kind of solved the problem of getting the accouting data into the MySQL database, however it's a bit crap cos I need to process the logs with a cron job, instead of automatically inserting from FreeRadius.
My company has lots of dialup also, and an ISP billing system called Extent (with built in radius) that works fine fo these dialup customers, however is unaware of 'Alive' packets. I'd really like to feed the accounting data from Freeradius to the Extent billing package. I'm thinking that for every "Alive" packet recieved from the RAS box perhaps I could calculate the difference in Octets between now and the last 'Alive', and then send a fake radius start and stop record to Extent, such that Extent would think the DSL user had dialed up for 10 minutes, used X amount of data, and hungup. This way the standard way of calculating usage would occur, and usage graphs, etc, would all work fine. It would be very nice to build this functionality into Freeradius. -- Perhaps I should email the developers list about how to do this?
thanks,
Dave Seddon
---------------------------------------------------------------------
Would you like to receive faxes to your personal email address?
You can with mBox. Visit http://www.mbox.com.au/fax
- List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
- List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
