ome 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,
'-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 = '-00-00 00:00:00';
INSERT INTO radius.dsl_accounting_details(
AcctUniqueId,
FragStartTime,
FragStopTime,
FragInputOctets,
FragOutputOctets)
VALUES(
AcctUniqueId_IN,
FragmentStopTime,
'-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 = '-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
-
List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html