O/H apolyxrono έγραψε:

Hi list,

I have freeradius-1.1.3 doing accounting with mysql. I am thinking to create 2 or 3 new tables in the radius db for my purposes and write in them the info i want every time freeradius writes data to the other tables (nas ,radcheck ,radacct etc.). freeradius only writes to radacct. It assumes someone else has written to nas/radcheck.

Is it possible to do that ? If yes how ? If yes is it possible to do it using php scripts and not perl (for example to call a php script to update my tables every time freeradius writting accounting data in the radius db)

Thanks a lot

- List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html

You can: 1. Use sql triggers if your sql server supports them 2. Just create 2-3 more sql module instances that will execute the queries you want 3. Create an external php script to do that. That will be slower though since you 'll have to execute it on every request and create a new sql connection (sql module uses a connection pool). I would suggest 2-1-3 (in that order). -- Kostas Kalevras Network Operations Center - National Technical University of Athens http://kkalev.wordpress.com

Hello, apolyxrono. OK, an example. I use freeradius server to do the accounting for my DSL clients. There are two tables in the accounting scheme: 1. dsl_accounting. This is a shorter version of radacct, containing only the fields I found to be useful to me. MySQL create statement: CREATE TABLE `radius`.`dsl_accounting` ( `RadAcctId` bigint(21) NOT NULL auto_increment, `AcctUniqueId` varchar(32) NOT NULL default '', `UserName` varchar(64) NOT NULL default '', `AcctStartTime` datetime NOT NULL default '0000-00-00 00:00:00', `AcctStopTime` datetime NOT NULL default '0000-00-00 00:00:00', `AcctSessionTime` int(12) default '0', `AcctInputOctets` bigint(12) default '0', `AcctOutputOctets` bigint(12) default '0', `CallingStationId` varchar(50) NOT NULL default '', `FramedIPAddress` varchar(15) NOT NULL default '', PRIMARY KEY (`RadAcctId`), KEY `UserName` (`UserName`), KEY `FramedIPAddress` (`FramedIPAddress`), KEY `AcctUniqueId` (`AcctUniqueId`), KEY `AcctStartTime` (`AcctStartTime`), KEY `AcctStopTime` (`AcctStopTime`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8; The logic: At the start of accounting session a new row is inserted with the following fields: RadAcctId = auto-incremented value; AcctUniqueId = %{Acct-Unique-Session-Id} variable passed by freeradius; UserName = %{SQL-User-Name} variable passed by freeradius; AcctStartTime = %S value (current time) passed by freeradius; AcctStopTime = '0000-00-00 00:00:00'; AcctSessionTime = 0; AcctInputOctets = 0; AcctOutputOctets = 0; CallingStationId = %{Calling-Station-Id} variable passed by freeradius; FramedIPAddress = %{Framed-IP-Address} variable passed by freeradius. With the arrival of Accounting Update packet the appropriate row is updated as follows: AcctSessionTime = %{Acct-Session-Time} variable passed by freeradius; AcctInputOctets = %{Acct-Input-Octets} variable passed by freeradius; AcctOutputOctets = %{Acct-Output-Octets} variable passed by freeradius. When the accounting session is finalized the appropriate row is updated as follows: AcctStopTime = %S value (current time) passed by freeradius; AcctSessionTime = %{Acct-Session-Time} variable passed by freeradius; AcctInputOctets = %{Acct-Input-Octets} variable passed by freeradius; AcctOutputOctets = %{Acct-Output-Octets} variable passed by freeradius. 2. dsl_accounting_details. This one stores information about every fragment of the accounting session (time between consecutive accounting packets arrival, 60 seconds in my case), and can be used for bandwidth usage graphs, statistical reports, etc. MySQL create statement: CREATE TABLE `radius`.`dsl_accounting_details` ( `AcctFragId` bigint(21) NOT NULL auto_increment, `AcctUniqueId` varchar(32) NOT NULL default '', `FragStartTime` datetime NOT NULL default '0000-00-00 00:00:00', `FragStopTime` datetime NOT NULL default '0000-00-00 00:00:00', `FragInputOctets` bigint(12) default NULL, `FragOutputOctets` bigint(12) default NULL, PRIMARY KEY (`AcctFragId`), KEY `FragStartTime` (`FragStartTime`), KEY `FragStopTime` (`FragStopTime`), KEY `AcctUniqueId` (`AcctUniqueId`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8; The logic: At the start of accounting session a new row is inserted with the following fields: AcctFragId = auto-incremented value; AcctUniqueId = %{Acct-Unique-Session-Id} variable passed by freeradius; FragStartTime = %S value (current time) passed by freeradius; FragStopTime = '0000-00-00 00:00:00'; FragInputOctets = 0; FragOutputOctets = 0. With the arrival of Accounting Update packet the appropriate row is updated as follows: FragStopTime = %S value (current time) passed by freeradius; FragInputOctets = input traffic since last accounting packet (a difference between current dsl_accounting.AcctInputOctets and a new value passed by freeradius in the %{Acct-Input-Octets} variable; FragOutputOctets = output traffic since last accounting packet (a difference between current dsl_accounting.AcctOutputOctets and a new value passed by freeradius in the %{Acct-output-Octets} variable; ... and then a new row is inserted (see above). When the accounting session is finalized the appropriate row is updated as follows: FragStopTime = %S value (current time) passed by freeradius; FragInputOctets = input traffic since last accounting packet (a difference between current dsl_accounting.AcctInputOctets and a new value passed by freeradius in the %{Acct-Input-Octets} variable; FragOutputOctets = output traffic since last accounting packet (a difference between current dsl_accounting.AcctOutputOctets and a new value passed by freeradius in the %{Acct-output-Octets} variable. You can't make several inserts/updates (and calculate traffic differences, which requires an additional select) with a single SQL query. This is where MySQL stored procedures come handy. To implement the described accounting scheme the following procedures can be used: 1. dsl_acct_start DELIMITER $$ DROP PROCEDURE IF EXISTS radius.dsl_acct_start $$ CREATE PROCEDURE radius.dsl_acct_start( IN AcctUniqueId_IN VARCHAR(32), IN UserName_IN VARCHAR(64), IN AcctStartTime_IN DATETIME, IN CallingStationId_IN VARCHAR(50), IN FramedIPAddress_IN VARCHAR(15)) LANGUAGE SQL NOT DETERMINISTIC SQL SECURITY INVOKER MODIFIES SQL DATA BEGIN INSERT INTO radius.dsl_accounting_details( AcctUniqueId, FragStartTime, FragStopTime, FragInputOctets, FragOutputOctets) VALUES( AcctUniqueId_IN, AcctStartTime_IN, '0000-00-00 00:00:00', 0, 0); INSERT INTO radius.dsl_accounting( AcctUniqueId, UserName, AcctStartTime, AcctStopTime, AcctSessionTime, AcctInputOctets, AcctOutputOctets, CallingStationId, FramedIPAddress) VALUES( AcctUniqueId_IN, UserName_IN, AcctStartTime_IN, '0', '0', '0', '0', CallingStationId_IN, FramedIPAddress_IN); END $$ DELIMITER ; 2. dsl_acct_update DELIMITER $$ DROP PROCEDURE IF EXISTS radius.dsl_acct_update $$ CREATE PROCEDURE radius.dsl_acct_update( IN AcctSessionTime_IN INT(12), IN AcctInputOctets_IN BIGINT(12), IN AcctOutputOctets_IN BIGINT(12), IN AcctUniqueId_IN VARCHAR(32), IN UserName_IN VARCHAR(64)) LANGUAGE SQL NOT DETERMINISTIC SQL SECURITY INVOKER MODIFIES SQL DATA BEGIN DECLARE SessionStartTime, FragmentStopTime DATETIME; DECLARE Prev_InputOctets, Prev_OutputOctets, New_InputOctets, New_OutputOctets BIGINT(12); # Fetch current values from radacct SELECT AcctStartTime, AcctInputOctets, AcctOutputOctets FROM radius.dsl_accounting WHERE AcctUniqueId = AcctUniqueId_IN AND UserName = UserName_IN INTO SessionStartTime, Prev_InputOctets, Prev_OutputOctets; # Calculate new values SET New_InputOctets = AcctInputOctets_IN - Prev_InputOctets; SET New_OutputOctets = AcctOutputOctets_IN - Prev_OutputOctets; SET FragmentStopTime = SessionStartTime + INTERVAL AcctSessionTime_IN SECOND; # Update tables UPDATE radius.dsl_accounting_details SET FragStopTime = FragmentStopTime, FragInputOctets = New_InputOctets, FragOutputOctets = New_OutputOctets WHERE AcctUniqueId = AcctUniqueId_IN AND FragStopTime = '0000-00-00 00:00:00'; INSERT INTO radius.dsl_accounting_details( AcctUniqueId, FragStartTime, FragStopTime, FragInputOctets, FragOutputOctets) VALUES( AcctUniqueId_IN, FragmentStopTime, '0000-00-00 00:00:00', 0, 0); UPDATE radius.dsl_accounting SET AcctSessionTime = AcctSessionTime_IN, AcctInputOctets = AcctInputOctets_IN, AcctOutputOctets = AcctOutputOctets_IN WHERE AcctUniqueId = AcctUniqueId_IN AND UserName = UserName_IN; END $$ DELIMITER ; 3. dsl_acct_stop DELIMITER $$ DROP PROCEDURE IF EXISTS radius.dsl_acct_stop $$ CREATE PROCEDURE radius.dsl_acct_stop( IN AcctStopTime_IN DATETIME, IN AcctSessionTime_IN INT(12), IN AcctInputOctets_IN BIGINT(12), IN AcctOutputOctets_IN BIGINT(12), IN AcctUniqueId_IN VARCHAR(32), IN UserName_IN VARCHAR(64)) LANGUAGE SQL NOT DETERMINISTIC SQL SECURITY INVOKER MODIFIES SQL DATA BEGIN DECLARE SessionStartTime DATETIME; DECLARE Prev_InputOctets, Prev_OutputOctets, New_InputOctets, New_OutputOctets BIGINT(12); # Fetch current values from radacct SELECT AcctStartTime, AcctInputOctets, AcctOutputOctets FROM radius.dsl_accounting WHERE AcctUniqueId = AcctUniqueId_IN AND UserName = UserName_IN INTO SessionStartTime, Prev_InputOctets, Prev_OutputOctets; # Calculate new values SET New_InputOctets = AcctInputOctets_IN - Prev_InputOctets; SET New_OutputOctets = AcctOutputOctets_IN - Prev_OutputOctets; # Update tables UPDATE radius.dsl_accounting_details SET FragStopTime = AcctStopTime_IN, FragInputOctets = New_InputOctets, FragOutputOctets = New_OutputOctets WHERE AcctUniqueId = AcctUniqueId_IN AND FragStopTime = '0000-00-00 00:00:00'; UPDATE radius.dsl_accounting SET AcctStopTime = AcctStopTime_IN, AcctSessionTime = AcctSessionTime_IN, AcctInputOctets = AcctInputOctets_IN, AcctOutputOctets = AcctOutputOctets_IN WHERE AcctUniqueId = AcctUniqueId_IN AND UserName = UserName_IN; END $$ DELIMITER ; To actually use these procedures you should replace the accounting query strings in your sql.conf with the following: accounting_start_query = "CALL dsl_acct_start ('%{Acct-Unique-Session-Id}', '%{SQL-User-Name}', '%S', '%{Calling-Station-Id}', '%{Framed-IP-Address}')" accounting_update_query = "CALL dsl_acct_update ('%{Acct-Session-Time}', '%{Acct-Input-Octets}', '%{Acct-Output-Octets}', '%{Acct-Unique-Session-Id}', '%{SQL-User-Name}')" accounting_stop_query = "CALL dsl_acct_stop('%S', '%{Acct-Session-Time}', '%{Acct-Input-Octets}', '%{Acct-Output-Octets}', '%{Acct-Unique-Session-Id}', '%{SQL-User-Name}')" Any questions/comments/suggestions are welcome. :) Best regards, Marat Rysbekov