Get number of users logged in via mySQL

classic Classic list List threaded Threaded
6 messages Options
| Threaded
Open this post in threaded view
|

Get number of users logged in via mySQL

Renato Sousa
Hello All,

I would like to get the number of logged in users on freeradius server
(3.0.12).
Looking at the mySQL accounting table, the connections with the NULL value
for the acctstoptime field, I see some connections with the same user and
even  device mac-address with the NULL acctstoptime field.
How to get this precise value through mySQL tables?
How to delete these open sessions?

Best Regards
-
List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
| Threaded
Open this post in threaded view
|

Re: Get number of users logged in via mySQL

Alan DeKok-2
On Dec 17, 2018, at 7:24 AM, Renato Sousa <[hidden email]> wrote:
> I would like to get the number of logged in users on freeradius server
> (3.0.12).

  Count the rows in the radacct which have a NULL acctstoptime.

> Looking at the mySQL accounting table, the connections with the NULL value
> for the acctstoptime field, I see some connections with the same user and
> even  device mac-address with the NULL acctstoptime field.

  The server just records what the NAS sends it.  If the NAS doesn't tell the server those sessions are closed, then the server doesn't know.

> How to get this precise value through mySQL tables?

  Fix that NAS so that it closes old sessions.

  The server isn't magic.  It only knows what the NAS sends it.  If the NAS tells it that the same user logged in 4 times, well, why would FreeRADIUS think any differently?

> How to delete these open sessions?

  SQL statements.

  Alan DeKok.


-
List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
| Threaded
Open this post in threaded view
|

Re: Get number of users logged in via mySQL

Renato Sousa
>
> Hello  Alan DeKok,
>


>   Count the rows in the radacct which have a NULL acctstoptime.
>
>> Yes ... If the information provided by NAS were correct a sentence as
>> below would solve the question:
>
> select count (*) from radaact where acctstoptime is null;
>
>
>> However, when I go to analyze the selected records, I see repeated
>> records with the same device mac-address.
>
> Right now I saw that my user does not have an open session on radius. :(
>
>

>   The server just records what the NAS sends it.  If the NAS doesn't tell
> the server those sessions are closed, then the server doesn't know.
>
> > How to get this precise value through mySQL tables?
>
>   Fix that NAS so that it closes old sessions.
>
>   The server isn't magic.  It only knows what the NAS sends it.  If the
> NAS tells it that the same user logged in 4 times, well, why would
> FreeRADIUS think any differently?
>
Yes, exactly!
Most of my APs are Ubiquiti, but I also have Cisco, HP, and Aruba on my
infraestructure. Is there any incompatibility with these manufacturers when
users move from one to another?
Anyone have any tips to improve this diagnosis?

>
> > How to delete these open sessions?
>
>   SQL statements.
>
 I have already done an UPDATE in the tables to insert date in the old
records with acctstoptime equal to NULL, however they continue to be
generated.

>
>   Alan DeKok.
>
>
> Best Regards,

Renato Sousa
-
List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
| Threaded
Open this post in threaded view
|

Re: Get number of users logged in via mySQL

Alan DeKok-2
On Dec 17, 2018, at 11:54 AM, Renato Sousa <[hidden email]> wrote:
>>> Yes ... If the information provided by NAS were correct a sentence as
>>> below would solve the question:
>>
>> select count (*) from radaact where acctstoptime is null;

  If the NAS is lying to FreeRADIUS, then there is little you can do.

>>> However, when I go to analyze the selected records, I see repeated
>>> records with the same device mac-address.
>>
>> Right now I saw that my user does not have an open session on radius. :(

  I don't know what that means.  More correctly, I don't know what *you* think that means.

  The radacct table just stores the data sent by the NAS.  If there are multiple session in the DB for a user, then:

a) the user has multiple sessions

  or

b) the NAS isn't sending FreeRADIUS "stop" packets when a session closes.

  There really aren't any other options.

> Most of my APs are Ubiquiti, but I also have Cisco, HP, and Aruba on my
> infraestructure. Is there any incompatibility with these manufacturers when
> users move from one to another?

  No.  They just send RADIUS packets.

> Anyone have any tips to improve this diagnosis?

  Look on the NAS to see which users are online.  Most NASes should provide an admin interface to do this.

>>> How to delete these open sessions?
>>
>>  SQL statements.
>>
> I have already done an UPDATE in the tables to insert date in the old
> records with acctstoptime equal to NULL, however they continue to be
> generated.

  Then the user has multiple sessions.  You will need to configure Simultaneous-Use to ensure that each user has only one session active at a time.

  Alan DeKok.


-
List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
| Threaded
Open this post in threaded view
|

Re: [EXTERNAL] Re: Get number of users logged in via mySQL

Users mailing list
Remember that for an AP the concept of when a session stops is not as clear cut as you might imagine. Worst case is you won't see a stop until the DHCP lease expires or perhaps when the AP gives up on a devices association having not seen it for a while. What this normally means is that there can be many 'sessions' for a single device in your network. If you can guarantee that one identity can NEVER appear in two places then just change your SQL to only count the last session to start for each unique identity. Here remember if AP's are close enough together devices could 'flip-flop' between them so concepts like never tend to be more or less impossible to guarantee.

--
Alister


On 17/12/2018, 19:51, "Freeradius-Users on behalf of Alan DeKok" <freeradius-users-bounces+alister.winfield=[hidden email] on behalf of [hidden email]> wrote:

    On Dec 17, 2018, at 11:54 AM, Renato Sousa <[hidden email]> wrote:
    >>> Yes ... If the information provided by NAS were correct a sentence as
    >>> below would solve the question:
    >>
    >> select count (*) from radaact where acctstoptime is null;

      If the NAS is lying to FreeRADIUS, then there is little you can do.

    >>> However, when I go to analyze the selected records, I see repeated
    >>> records with the same device mac-address.
    >>
    >> Right now I saw that my user does not have an open session on radius. :(

      I don't know what that means.  More correctly, I don't know what *you* think that means.

      The radacct table just stores the data sent by the NAS.  If there are multiple session in the DB for a user, then:

    a) the user has multiple sessions

      or

    b) the NAS isn't sending FreeRADIUS "stop" packets when a session closes.

      There really aren't any other options.

    > Most of my APs are Ubiquiti, but I also have Cisco, HP, and Aruba on my
    > infraestructure. Is there any incompatibility with these manufacturers when
    > users move from one to another?

      No.  They just send RADIUS packets.

    > Anyone have any tips to improve this diagnosis?

      Look on the NAS to see which users are online.  Most NASes should provide an admin interface to do this.

    >>> How to delete these open sessions?
    >>
    >>  SQL statements.
    >>
    > I have already done an UPDATE in the tables to insert date in the old
    > records with acctstoptime equal to NULL, however they continue to be
    > generated.

      Then the user has multiple sessions.  You will need to configure Simultaneous-Use to ensure that each user has only one session active at a time.

      Alan DeKok.


    -
    List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
    --------------------------------------------------------------------
    This email is from an external source. Please do not open attachments or click links from an unknown or suspicious origin. Phishing attempts can be reported by sending them to [hidden email] as attachments. Thank you
    --------------------------------------------------------------------



Information in this email including any attachments may be privileged, confidential and is intended exclusively for the addressee. The views expressed may not be official policy, but the personal views of the originator. If you have received it in error, please notify the sender by return e-mail and delete it from your system. You should not reproduce, distribute, store, retransmit, use or disclose its contents to anyone. Please note we reserve the right to monitor all e-mail communication through our internal and external networks. SKY and the SKY marks are trademarks of Sky plc and Sky International AG and are used under licence.

Sky UK Limited (Registration No. 2906991), Sky-In-Home Service Limited (Registration No. 2067075), Sky Subscribers Services Limited (Registration No. 2340150) and Sky CP Limited (Registration No. 9513259) are direct or indirect subsidiaries of Sky plc (Registration No. 2247735). All of the companies mentioned in this paragraph are incorporated in England and Wales and share the same registered office at Grant Way, Isleworth, Middlesex TW7 5QD

-
List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
| Threaded
Open this post in threaded view
|

Re: [EXTERNAL] Re: Get number of users logged in via mySQL

Renato Sousa
Hello Alister,

Is it possible then to end these sessions by setting a maximum duration for
the session?
Something like a script running periodically by setting NOW () the
acctstoptime field for sessions longer than X hours.

Regards


Em ter, 18 de dez de 2018 às 05:59, Winfield, Alister via Freeradius-Users <
[hidden email]> escreveu:

> Remember that for an AP the concept of when a session stops is not as
> clear cut as you might imagine. Worst case is you won't see a stop until
> the DHCP lease expires or perhaps when the AP gives up on a devices
> association having not seen it for a while. What this normally means is
> that there can be many 'sessions' for a single device in your network. If
> you can guarantee that one identity can NEVER appear in two places then
> just change your SQL to only count the last session to start for each
> unique identity. Here remember if AP's are close enough together devices
> could 'flip-flop' between them so concepts like never tend to be more or
> less impossible to guarantee.
>
> --
> Alister
>
>
> On 17/12/2018, 19:51, "Freeradius-Users on behalf of Alan DeKok"
> <freeradius-users-bounces+alister.winfield=[hidden email] on
> behalf of [hidden email]> wrote:
>
>     On Dec 17, 2018, at 11:54 AM, Renato Sousa <[hidden email]> wrote:
>     >>> Yes ... If the information provided by NAS were correct a sentence
> as
>     >>> below would solve the question:
>     >>
>     >> select count (*) from radaact where acctstoptime is null;
>
>       If the NAS is lying to FreeRADIUS, then there is little you can do.
>
>     >>> However, when I go to analyze the selected records, I see repeated
>     >>> records with the same device mac-address.
>     >>
>     >> Right now I saw that my user does not have an open session on
> radius. :(
>
>       I don't know what that means.  More correctly, I don't know what
> *you* think that means.
>
>       The radacct table just stores the data sent by the NAS.  If there
> are multiple session in the DB for a user, then:
>
>     a) the user has multiple sessions
>
>       or
>
>     b) the NAS isn't sending FreeRADIUS "stop" packets when a session
> closes.
>
>       There really aren't any other options.
>
>     > Most of my APs are Ubiquiti, but I also have Cisco, HP, and Aruba on
> my
>     > infraestructure. Is there any incompatibility with these
> manufacturers when
>     > users move from one to another?
>
>       No.  They just send RADIUS packets.
>
>     > Anyone have any tips to improve this diagnosis?
>
>       Look on the NAS to see which users are online.  Most NASes should
> provide an admin interface to do this.
>
>     >>> How to delete these open sessions?
>     >>
>     >>  SQL statements.
>     >>
>     > I have already done an UPDATE in the tables to insert date in the old
>     > records with acctstoptime equal to NULL, however they continue to be
>     > generated.
>
>       Then the user has multiple sessions.  You will need to configure
> Simultaneous-Use to ensure that each user has only one session active at a
> time.
>
>       Alan DeKok.
>
>
>     -
>     List info/subscribe/unsubscribe? See
> http://www.freeradius.org/list/users.html
>     --------------------------------------------------------------------
>     This email is from an external source. Please do not open attachments
> or click links from an unknown or suspicious origin. Phishing attempts can
> be reported by sending them to [hidden email] as attachments. Thank you
>     --------------------------------------------------------------------
>
>
>
> Information in this email including any attachments may be privileged,
> confidential and is intended exclusively for the addressee. The views
> expressed may not be official policy, but the personal views of the
> originator. If you have received it in error, please notify the sender by
> return e-mail and delete it from your system. You should not reproduce,
> distribute, store, retransmit, use or disclose its contents to anyone.
> Please note we reserve the right to monitor all e-mail communication
> through our internal and external networks. SKY and the SKY marks are
> trademarks of Sky plc and Sky International AG and are used under licence.
>
> Sky UK Limited (Registration No. 2906991), Sky-In-Home Service Limited
> (Registration No. 2067075), Sky Subscribers Services Limited (Registration
> No. 2340150) and Sky CP Limited (Registration No. 9513259) are direct or
> indirect subsidiaries of Sky plc (Registration No. 2247735). All of the
> companies mentioned in this paragraph are incorporated in England and Wales
> and share the same registered office at Grant Way, Isleworth, Middlesex TW7
> 5QD
>
> -
> List info/subscribe/unsubscribe? See
> http://www.freeradius.org/list/users.html
-
List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html