I noticed that the stop query changed at some point between 0.9.3 and
1.0.1. Here are the queries:
## 0.9.3 ##
accounting_stop_query = "UPDATE ${acct_table2}
SET
AcctStopTime = '%S',
AcctSessionTime = '%{Acct-Session-Time}',
AcctInputOctets = '%{Acct-Input-Octets}',
AcctOutputOctets = '%{Acct-Output-Octets}',
AcctTerminateCause = '%{Acct-Terminate-Cause}',
AcctStopDelay = '%{Acct-Delay-Time}',
ConnectInfo_stop = '%{Connect-Info}'
WHERE
AcctSessionId = '%{Acct-Session-Id}' AND
UserName = '%{SQL-User-Name}' AND
NASIPAddress = '%{NAS-IP-Address}' AND
AcctStopTime = 0"
## 1.0.1 (same as 1.0.5) ##
accounting_stop_query = "UPDATE ${acct_table2}
SET
AcctStopTime = '%S',
AcctSessionTime = '%{Acct-Session-Time}',
AcctInputOctets = '%{Acct-Input-Octets}',
AcctOutputOctets = '%{Acct-Output-Octets}',
AcctTerminateCause = '%{Acct-Terminate-Cause}',
AcctStopDelay = '%{Acct-Delay-Time}',
ConnectInfo_stop = '%{Connect-Info}'
WHERE
AcctSessionId = '%{Acct-Session-Id}' AND
UserName = '%{SQL-User-Name}' AND
NASIPAddress = '%{NAS-IP-Address}'"
Notice the last item in the WHERE clause is missing in the later
version. After looking at the data in my own radacct table, it looks
like the change would cut down on duplicates, but at the cost of
modifying rows that are potentially days or weeks old with new stoptime
data.
If the NAS reuses SessionID for the same user 2 weeks after the initial
use, then the user will end up with a 2 week long session and if
sqlcounter is in use, will likely be shutoff.
It seems the 0.9.3 version is safer. I don't see specific mention of
the change in the ChangeLog. Was there a reason for the change?
I am working out the details of a modification of my own. At this point
the WHERE clause looks like this (untested):
WHERE AcctUniqueId = '%{Acct-Unique-Session-Id}' AND
ABS((UNIXTIMESTAMP(AcctStartTime) -
UNIXTIMESTAMP(DATE_SUB('%S',INTERVAL (%{Acct-Session-Time:-0} +
%{Acct-Delay-Time:-0}) SECOND))) < 3600)"
Basically, it uses AcctUniqueId which is a hash of AcctSessionId (from
the NAS), the NAS-IP, UserName, and Nas-Port and then makes sure the
AcctStarttime already in the table is within an hour of the derrived
time based on the stoptime and the session length.
NOTE: Those using MySQL 5.0+ can use TIMESTAMPDIFF instead of converting
and subtracting.
I think this change should help reduce duplicate accounting entries. It
will incurr some added load for the date calc, but I'm hoping not too
much given the AcctUniqueId should be indexed and have a low number of
duplicates.
Any thoughts?
--
Dennis Skinner
Systems Administrator
BlueFrog Internet
http://www.bluefrog.com
-
List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
