Re: Can i do that?

10 Apr 2007

      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