[isf-wifidog] Authserver database and user online/offline status?

[Andrew Hodel]

Upon further inspection it seems to me that the auth server is
mis-reporting the number of online users due to multiple users using
the same login.

The default network wide user count sql in classes/Network.php is:

$sql = "SELECT ((SELECT COUNT(DISTINCT users.user_id) as count FROM
users,connections JOIN tokens USING (token_id) NATURAL JOIN nodes JOIN
networks ON (nodes.network_id=networks.network_id AND
networks.network_id='$network_id') WHERE
tokens.token_status='".TOKEN_INUSE."' AND
users.user_id=connections.user_id AND
users.user_id!='{$splashOnlyUserId}') + (SELECT COUNT(DISTINCT
connections.user_mac) as count FROM users,connections JOIN tokens
USING (token_id) NATURAL JOIN nodes JOIN networks ON
(nodes.network_id=networks.network_id AND
networks.network_id='$network_id') WHERE
tokens.token_status='".TOKEN_INUSE."' AND
users.user_id=connections.user_id AND
users.user_id='{$splashOnlyUserId}')) AS count";

This translates to:

SELECT ((SELECT COUNT(DISTINCT users.user_id) as count FROM
users,connections JOIN tokens USING (token_id) NATURAL JOIN nodes JOIN
networks ON (nodes.network_id=networks.network_id AND
networks.network_id='default-network') WHERE
tokens.token_status='INUSE' AND users.user_id=connections.user_id AND
users.user_id!='8176e0aef48738751fcbfd6a59250b3a') + (SELECT
COUNT(DISTINCT connections.user_mac) as count FROM users,connections
JOIN tokens USING (token_id) NATURAL JOIN nodes JOIN networks ON
(nodes.network_id=networks.network_id AND
networks.network_id='default-network') WHERE
tokens.token_status='INUSE' AND users.user_id=connections.user_id AND
users.user_id='8176e0aef48738751fcbfd6a59250b3a')) AS count;

if you substitute all the variables for real data.

If I run that sql statement, I get the exact same number of online
users which wifidog is reporting (network wide).  It also seems that
classes/Node.php uses this same sql logic when determining the number
of users on each particular node.  This is also reporting incorrectly
for me.


Most of our nodes are either splash only, or they may have 20+ users
who all use the same username and password.  Could this be a problem
with the user counting?

I am mainly concerned with this because many of my nodes show up as
only having one user online.  However if I ssh over to them and see
who is connected on the 802.11 layer, there are usually 15+ clients
actively passing traffic.  They could not be passing traffic without
being logged in to the auth server.

If anyone is willing to help I would be more then willing to send out
a copy of my current wifidog database along with the number of users
online wifidog is reporting for the network and each node at that
time.

Actually, I just took a moment to look over each node.  Every node
which is splash only is correctly reporting the number of users.  The
nodes where a login is required (keep in mind each user on these nodes
would be using the same login) all show a user count of 1.  Many of
these nodes currently have 5+ people actively passing traffic!!



Regards,
Andrew Hodel





On Mon, Mar 16, 2009 at 9:21 PM, Andrew Hodel <andrewhodel at gmail.com> wrote:
> Can anyone enlighten me as to how the auth server knows if someone is
> online or offline at any given moment?
>
> At first I thought that if a row from the connections table had a
> timestamp_out value of NULL that would mean the connection is still
> online.  That does not seem to be true by any means.
>
> Second I thought that if a connections token 'token_status' in the
> tokens table was INUSE it would mean the connection is still online.
> That does not seem to be true either.
>
> Anyone have any insight into this, we run a particularly large network
> and at the current time of running these queries on the live database
> there were only 44 users (users by mac address) online at that point
> in time.  However if I were to count online users by the timestamp_out
> column of the connections table it would lead me to believe that 1818
> users were online.  Conversely if I were to calculate online users by
> the tokens table and token_status I would be left with 579 users
> online.  Neither of these are true.
>
> Data goes back 11 months on this system.  However if I test for just
> the past week there are discrepancies like the ones shown below:
>
>
> wifidog=# select count(*) from connections;
>  count
> --------
>  135490
> (1 row)
>
> wifidog=# select count(*) from connections where timestamp_out is null;
>  count
> -------
>  1818
> (1 row)
>
> wifidog=# select count(*) from connections where timestamp_out is not null;
>  count
> --------
>  133672
> (1 row)
>
> wifidog=# select count(*) from tokens;
>  count
> --------
>  162557
> (1 row)
>
> wifidog=# select count(*) from tokens where token_status = 'USED';
>  count
> --------
>  135379
> (1 row)
>
> wifidog=# select count(*) from tokens where token_status = 'INUSE';
>  count
> -------
>   579
> (1 row)
>
> wifidog=# select count(*) from tokens where token_status = 'UNUSED';
>  count
> -------
>  26600
> (1 row)
>
>
>
>
> Regards,
> Andrew Hodel
>