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

Andrew Hodel andrewhodel at gmail.com
Lun 16 Mar 23:16:08 EDT 2009


I think I figured it out, I am getting correct online user values by
modifying the following files:

classes/Network.php (ORIGINAL):

1199             $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=connect
ions.user_id AND users.user_id!='{$splashOnlyUserId}') + (SELECT
COUNT(DISTINCT connections.user_mac) as count FROM users,connections
JOIN tokens USIN     G (token_id) NATURAL JOIN nodes JOIN networks ON
(nodes.network_id=networks.network_id AND
networks.network_id='$network_id') WHERE tokens.token_statu
s='".TOKEN_INUSE."' AND users.user_id=connections.user_id AND
users.user_id='{$splashOnlyUserId}')) AS count";

classes/Network.php (MODIFIED):

1199             $sql = "SELECT ((SELECT COUNT(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=connect
ions.user_id AND users.user_id!='{$splashOnlyUserId}') + (SELECT
COUNT(DISTINCT connections.user_mac) as count FROM users,connections
JOIN tokens USIN     G (token_id) NATURAL JOIN nodes JOIN networks ON
(nodes.network_id=networks.network_id AND
networks.network_id='$network_id') WHERE tokens.token_statu
s='".TOKEN_INUSE."' AND users.user_id=connections.user_id AND
users.user_id='{$splashOnlyUserId}')) AS count";

classes/Node.php (ORIGINAL):

1624         $sql = "SELECT ((SELECT COUNT(DISTINCT users.user_id) as
count FROM users,connections JOIN tokens USING (token_id) WHERE
tokens.token_status='".TOKEN_I     NUSE."' AND
users.user_id=connections.user_id AND
connections.node_id='{$this->id}' AND
users.user_id!='{$splashOnlyUserId}') + (SELECT COUNT(DISTINCT
connections.user_mac) as count FROM users,connections JOIN tokens
USING (token_id) WHERE tokens.token_status='".TOKEN_INUSE."' AND
users.user_id=conn     ections.user_id AND
connections.node_id='{$this->id}' AND
users.user_id='{$splashOnlyUserId}')) AS count";

classes/Node.php (MODIFIED):

1624         $sql = "SELECT ((SELECT COUNT(users.user_id) as count
FROM users,connections JOIN tokens USING (token_id) WHERE
tokens.token_status='".TOKEN_I     NUSE."' AND
users.user_id=connections.user_id AND
connections.node_id='{$this->id}' AND
users.user_id!='{$splashOnlyUserId}') + (SELECT COUNT(DISTINCT
connections.user_mac) as count FROM users,connections JOIN tokens
USING (token_id) WHERE tokens.token_status='".TOKEN_INUSE."' AND
users.user_id=conn     ections.user_id AND
connections.node_id='{$this->id}' AND
users.user_id='{$splashOnlyUserId}')) AS count";

The only change is removing the DISTINCT from the users.user_id count().

Does anyone have any reason not to submit this as a bug?  My guess is
that the current assumption is that if a user is logged in twice on
the same node that it is the same user.  Seeing as you have to allow
multiple logins from the same username in the configuration it would
seem to me that this isn't actually the case.



Regards,
Andrew Hodel




On Mon, Mar 16, 2009 at 10:05 PM, Andrew Hodel <andrewhodel at gmail.com> wrote:
> 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
>>
>


Plus d'informations sur la liste de diffusion WiFiDog