Scenario: Freeradius server 2.2.8 as billing system on Ubuntu Server, NAS is Mikrotik Router ccr1036 v 6.42.4
Problem: on NAS i can see 900 active/online users, where as on freeradius I see 500 sessions (where acctstop time is NULL)
I use following query to calculate online number of users in FR.
mysql -uroot -s -e "use radius; select * from radacct WHERE acctstoptime IS NULL;" |wc -l
I tried to disconnect all users on NAS, clear all sessions from freeradius, users reconnect fine, but still number of users on NAS vs freeradius doesnt matches. What should I check?
This is a fundamental issue with the fact that RADIUS is a lossy protocol. If a user goes offline, and for some reason, the RADIUS server doesn't receive the Acct-Status-Type=Stop packet, then the session will be permanently marked as open.
If you use the SQL queries from FreeRADIUS v3, you'll see they have an AcctUpdateTime field. This AcctUpdateTime field is set to NOW() every time a session is changed.
The purpose of this field is to make it easy for external scripts to go in and correct sessions which are obviously stale, by looking at when the session was last updated.
If you want to fix this, I'd either updated to FreeRADIUS v3.0.x (2.2.8 hasn't been supported for a while now), or use the v3.0.x schema and queries (they should mostly work).
Then just run a query every 10 minutes to updates rows with a NULL AcctStopTime, where the difference between NOW() and AcctUpdateTime is > <NAS Interim Interval> * 2, setting AcctStopTime to NOW().
For this to work, Interim-Updates will need to be enabled on your NAS. These are Accounting-Requests sent periodically from the NAS to update things like traffic counters, but in this case, they can also be used as useful heartbeat messages.
If you can't figure out where the static config option for this is on your NAS, it can sometimes be enabled by returning the Acct-Interim-Interval attribute in your Access-Accept, which'll specify the interim interval in seconds.