Why you don't use triggers? you can add a triger witch updates radreply table
automatically when you update radacct.
by the way, which client do you use for this schema? and which db server? do
you have any problem with postgresql?
On Tuesday 22 July 2003 04:05 am, Alex Chen wrote:
> I am trying to use Session-Timeout as the way to account for how much total
> time
> the user is allowed to stay connect, and Acct-Session-Time for the time the
> user
> spend on a session. Once the summation of the Acct-Session-Time for all
> sessions
> of a user reaches the Session-Timeout, the user is denied service.
>
> The scheme is like this:
> radreply has the Session-Timeout attribute. When the user is accepted,
> the value is sent to the client. The Radius client starts accounting if
> the Session-Timeout is > 0. When the user disconnects, the Radius client
> stops the accounting and sends back the new Session-Timeout value, which is
> the difference of the original value minus the time the user spent.
> I modified the server's SQL accounting_stop_query to update not only
> 'radacct', but
> also 'radreply':
>
> 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; UPDATE ${authreply_table} SET Value =
> %{Session-Timeout} WHERE UserName = '%{SQL-User-Name}'
> AND Attribute = 'Session-Timeout'"
>
> Notice there are two UPDATE statements here.
>
> But I got the following error in the debug output:
>
> radius_xlat: 'Alex'
> rlm_sql (sql): sql_set_user escaped user --> 'Alex'
> radius_xlat: 'UPDATE radacct SET AcctStopTime = '2003-07-21 14:57:48',
> AcctSessionTime = '5', AcctInputOctets = '', AcctOutputOctets = '',
> AcctTerminateCause = '', AcctStopDelay = '', ConnectInfo_stop = '' WHERE
> AcctSessionId = '1' AND UserName = 'Alex' AND NASIPAddress = '192.168.2.1'
> AND AcctStopTime = 0; UPDATE radreply SET Value = 0 WHERE UserName = 'Alex'
> AND Attribute = 'Session-Timeout''
> rlm_sql (sql): Reserving sql socket id: 3
> rlm_sql_mysql: MYSQL check_error: 1064 received
> rlm_sql (sql): Couldn't update SQL accounting STOP record - You have an
> error in your SQL syntax near '; UPDATE radreply SET Value = 0 WHERE
> UserName = 'Alex' AND Attribute = 'Session' at line 1
> rlm_sql (sql): Released sql socket id: 3
> modcall[accounting]: module "sql" returns fail
> modcall: group accounting returns fail
>
> If I manually type in the second update statement to change the
> Session-Timeout value in the radreply, it worked fine.
>
> What could be the problem?
>
> If this does not work as the way I expected, how can I get the server to
> update the total time allowed for the user
> after each accounting start-stop session from within the server?
>
>
>
> -
> List info/subscribe/unsubscribe? See
> http://www.freeradius.org/list/users.html
-
List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
