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', '[EMAIL PROTECTED]', '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', '[EMAIL PROTECTED]', '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', '[EMAIL PROTECTED]', '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', '[EMAIL PROTECTED]', '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 = '[EMAIL PROTECTED]' 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 =
'[EMAIL PROTECTED]'")
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
/| /| / /__ [EMAIL PROTECTED] - Mark J Elkins, SCO ACE, Cisco CCIE
/ |/ |ARK \_/ /__ LKINS Tel: +27 12 807 0590 Cell: +27 82 601 0496
-
List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
