I am trying to minimize the failure rate for detecting a user online status using FR105 & MySQL411. Sometimes the AcctStopTime in the radacct table remains 0 even the user is not anymore online for whatever reason (reboot, connection lost or ...). I can therefore not just check if the AcctStopTime for a particular user is 0. Since there is no record when the NAS unit sent the last update to FR and it is not recorded in the radacct table ... how do I know that the AcctStopTime=0 is not a 'leftover' ... Would it make sense to add a TIMESTAMP to the radacct table to record the last update? And would the use of TIMESTAMP for the radacct table produce some form of performance degrade? A TIMESTAMP would allow me to see if the row was updated within Idle-Timeout. Any hints from experience? Thanks, Gunther
"Gunther" <freeradius@caribsms.com> wrote:
how do I know that the AcctStopTime=0 is not a 'leftover' ... Would it make sense to add a TIMESTAMP to the radacct table to record the last update?
Sure. If the NAS is supposed to send accounting updates every 15 minutes, and it's been an hour since the last one, you can guess that the user isn't logged in any more.
And would the use of TIMESTAMP for the radacct table produce some form of performance degrade?
I doubt it. Alan DeKok.
Alan DeKok wrote:
"Gunther" wrote:
how do I know that the AcctStopTime=0 is not a 'leftover' ... Would it make sense to add a TIMESTAMP to the radacct table to record the last update?
Sure. If the NAS is supposed to send accounting updates every 15 minutes, and it's been an hour since the last one, you can guess that the user isn't logged in any more.
And would the use of TIMESTAMP for the radacct table produce some form of performance degrade?
I doubt it.
Alan DeKok.
Thanks! It works very well! Gunther
For those interested, here the solution I am using now: I am using FR 1.0.5 with MySQL 4.1.10a, PHP and no flat files, except for the DEFAULT values and the standard configuration files radiusd.conf, sql.conf (my NAS list is also in a SQL table). I also changed most of the SQL column names, by adding a prefix. For the examples I changed it back to the original names. How to get user online status: ============================== 1. Add the TIMESTAMP column to the radacct table `radacct_mdate` timestamp NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP 2. To get the online status set up a query something like this (using PHP): define('RAD_ONLINE_TIMEOUT', 15); // 15 min since last timestamp from NAS, assuming connection dead $query = "SELECT COUNT(*) FROM radacct WHERE UserName = ' . $username . ' AND AcctStopTime=0 AND DATE_SUB(NOW(),INTERVAL ' . RAD_ONLINE_TIMEOUT .' MINUTE) <= radacct_mdate ORDER BY AcctStartTime DESC LIMIT 1)"; My actual query is a bit more complicated as I verify against a user table if they are actually allowed to be online ( Starttime < ActualTime < Stoptime) How to get Simultaneous-Use with FR & MySQL working: ==================================================== 1. I setup my defaults in the raddb/users file (at the very end and nothing else beside localhost) DEFAULT Simultaneous-Use := 1, Auth-Type := SQL (this seems to be the only way) Idle-Timeout = 3600, Acct-Interim-Interval = 180 Note: You can override the default Simultaneous-Use attribute for a user or a group by setting it in radcheck or radgroupcheck table (groupname Simultaneous-Use := 3). 2. In raddb/radiusd.conf Instead of using radutmp, I am using SQL: accounting { . . sql } session { # See "Simultaneous Use Checking Querie" in sql.conf sql } 3. In raddb/sql.conf simul_count_query = "SELECT COUNT(*) FROM ${acct_table1} WHERE UserName='%{SQL-User-Name}' AND AcctStopTime=0 AND CallingStationId <> '%{Calling-Station-Id}' AND DATE_SUB(NOW(),INTERVAL 15 MINUTE) <= radacct_mdate" simul_verify_query = "SELECT RadAcctId, AcctSessionId, UserName, NASIPAddress, NASPortId, FramedIPAddress, CallingStationId, FramedProtocol FROM ${acct_table1} WHERE UserName='%{SQL-User-Name}' AND AcctStopTime = 0 AND CallingStationId <> '%{Calling-Station-Id}' AND DATE_SUB(NOW(),INTERVAL 15 MINUTE) <= radacct_mdate" Not sure that this is all 100% like the developers intended it to be, but it works. Gunther
participants (2)
-
Alan DeKok -
Gunther