Freeradius, MySQL and unique accounting records.

[Mark J Elkins]

I'm getting multiple "accounting_start_query" records in my "radacct" 
table. When a "Stop" arrives, each gets the same accounting info (time 
online, bytes in, bytes out - etc)...
However - when I run SQL queries - I do not get true stats - which is a 
problem.

My "accounting_start_query" are stock standard (I'm running an oldish 
version of radius though).

What is happening is that I'm getting multiple Start Records - and 
logging each one.

        accounting_start_query = "INSERT into ${acct_table1} 
(AcctSessionId, AcctUniqueId, UserName, Realm, NASIPAddress, NASPortId, 
NASPortType, AcctStartTime, AcctStopTime, AcctSessionTime, 
AcctAuthentic, ConnectInfo_start, ConnectInfo_stop, AcctInputOctets, 
AcctOutputOctets, CalledStationId, CallingStationId, AcctTerminateCause, 
ServiceType, FramedProtocol, FramedIPAddress, AcctStartDelay, 
AcctStopDelay) values('%{Acct-Session-Id}', '%{Acct-Unique-Session-Id}', 
'%{SQL-User-Name}', '%{Realm}', '%{NAS-IP-Address}', '%{NAS-Port}', 
'%{NAS-Port-Type}', '%S', '0', '0', '%{Acct-Authentic}', 
'%{Connect-Info}', '', '0', '0', '%{Called-Station-Id}', 
'%{Calling-Station-Id}', '', '%{Service-Type}', '%{Framed-Protocol}', 
'%{Framed-IP-Address}', '%{Acct-Delay-Time}', '0')"


INSERT into radacct_rad2 (AcctSessionId, AcctUniqueId, UserName, Realm, 
NASIPAddress, NASPortId, NASPortType, AcctStartTime, AcctStopTime, 
AcctSessionTime, AcctAuthentic, ConnectInfo_start, ConnectInfo_stop, 
AcctInputOctets, AcctOutputOctets, CalledStationId, CallingStationId, 
AcctTerminateCause, ServiceType, FramedProtocol, FramedIPAddress, 
AcctStartDelay, AcctStopDelay) values('7/0/3/5.117_18EB0F13', 
'ade8e732c3a7aef6', 'scubaw at pop.co.za', 'pop.co.za', '196.43.27.19', 
'1929707637', 'Virtual', '2006-12-20 06:03:25', '0', '0', 'RADIUS', 
'AutoShapedVC', '', '0', '0', '', '', '', 'Framed-User', 'PPP', 
'41.242.241.194', '0', '0');

INSERT into radacct_rad2 (AcctSessionId, AcctUniqueId, UserName, Realm, 
NASIPAddress, NASPortId, NASPortType, AcctStartTime, AcctStopTime, 
AcctSessionTime, AcctAuthentic, ConnectInfo_start, ConnectInfo_stop, 
AcctInputOctets, AcctOutputOctets, CalledStationId, CallingStationId, 
AcctTerminateCause, ServiceType, FramedProtocol, FramedIPAddress, 
AcctStartDelay, AcctStopDelay) values('7/0/3/5.117_18EB0F13', 
'ade8e732c3a7aef6', 'scubaw at pop.co.za', 'pop.co.za', '196.43.27.19', 
'1929707637', 'Virtual', '2006-12-20 06:03:29', '0', '0', 'RADIUS', 
'AutoShapedVC', '', '0', '0', '', '', '', 'Framed-User', 'PPP', 
'41.242.241.194', '5', '0');

INSERT into radacct_rad2 (AcctSessionId, AcctUniqueId, UserName, Realm, 
NASIPAddress, NASPortId, NASPortType, AcctStartTime, AcctStopTime, 
AcctSessionTime, AcctAuthentic, ConnectInfo_start, ConnectInfo_stop, 
AcctInputOctets, AcctOutputOctets, CalledStationId, CallingStationId, 
AcctTerminateCause, ServiceType, FramedProtocol, FramedIPAddress, 
AcctStartDelay, AcctStopDelay) values('7/0/3/5.117_18EB0F13', 
'e71dbe474c39274d', 'scubaw at pop.co.za', 'pop.co.za', '196.43.27.19', 
'1929707637', 'Virtual', '2006-12-20 06:03:35', '0', '0', 'RADIUS', 
'AutoShapedVC', '', '0', '0', '', '', '', 'Framed-User', 'PPP', 
'41.242.241.194', '15', '0');

INSERT into radacct_rad2 (AcctSessionId, AcctUniqueId, UserName, Realm, 
NASIPAddress, NASPortId, NASPortType, AcctStartTime, AcctStopTime, 
AcctSessionTime, AcctAuthentic, ConnectInfo_start, ConnectInfo_stop, 
AcctInputOctets, AcctOutputOctets, CalledStationId, CallingStationId, 
AcctTerminateCause, ServiceType, FramedProtocol, FramedIPAddress, 
AcctStartDelay, AcctStopDelay) values('7/0/3/5.117_18EB0F13', 
'db5198a07e9ebe18', 'scubaw at pop.co.za', 'pop.co.za', '196.43.27.19', 
'1929707637', 'Virtual', '2006-12-20 06:03:39', '0', '0', 'RADIUS', 
'AutoShapedVC', '', '0', '0', '', '', '', 'Framed-User', 'PPP', 
'41.242.241.194', '10', '0');

[the stop record]
UPDATE radacct_rad2 SET AcctStopTime = '2006-12-20 06:37:09', 
AcctSessionTime = '2040', AcctInputOctets = '2371574', AcctOutputOctets 
= '93521334', AcctTerminateCause = 'User-Request', AcctStopDelay = '0', 
ConnectInfo_stop = 'AutoShapedVC' WHERE  AcctSessionId = 
'7/0/3/5.117_18EB0F13' AND UserName = 'scubaw at pop.co.za' AND 
NASIPAddress = '196.43.27.19';

The Acct-Delay-Time is different for each record - increases by 5 
seconds - etc.

So what do people do to maintain accurate accounting records?
For example - to get the current monthly data-in+out total for an 
individual - I run....

MBYTE=$(mysql -u$USR -p$SQLPASS -h$DBHOST -B --skip-column-names $DB -e \
        "SELECT ROUND(SUM((AcctInputOctets + AcctOutputOctets)/1048576)) 
FROM radacct WHERE AcctStartTime > '$monthstart' AND username = 
'$username@$realm'")

Currently - this is quite quick - but would give me the wrong results 
for the above!

What about making the column "AcctSessionId" unique? (somehow) - or 
maybe a new column which contains AcctSessionId, UserName and 
NASIPAddress - or what do people suggest?

-- 
  .  .     ___. .__      Posix Systems - Sth Africa
 /| /|       / /__       mje at posix.co.za  -  Mark J Elkins, SCO ACE, Cisco CCIE
/ |/ |ARK \_/ /__ LKINS  Tel: +27 12 807 0590  Cell: +27 82 601 0496