sql counter clarification and issues

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

sql counter clarification and issues

Ross Tsolakidis
Hi all,

I was hoping someone could shed some light on this problem I'm having.

FreeRadius 1.04
Compiled using:
./configure --with-experimental-modules
make
make install

Radius is connecting to a mysql backend.
The NAS units are logging to Radius and into SQL.

Running in debug mode I see this:

Module: Loaded Counter
 counter: filename = "/usr/local/etc/raddb/db.daily"
 counter: key = "User-Name"
 counter: reset = "daily"
 counter: count-attribute = "Acct-Session-Time"
 counter: counter-name = "Daily-Session-Time"
 counter: check-name = "Max-Daily-Session"
 counter: allowed-servicetype = "Framed-User"
 counter: cache-size = 5000
rlm_counter: Counter attribute Daily-Session-Time is number 1671
rlm_counter: Current Time: 1119932000 [2005-06-28 14:13:20], Next reset
1119967200 [2005-06-29 00:00:00]
Module: Instantiated counter (daily)

So the Counter loads just fine and Radius works fine.

Looking at that debug, where exactly is it resetting the counter ?  In
the db.daily ?  Because it's not resetting in SQL.
Also what is the db.daily ?  How do you read that file ?

What I'm trying to achieve here is to get mysql to reset the counters
its doing, and then start a new row if possible.
Within the table radacct.  AcctInputOctets AcctOutputOctets.
Is it possible to do this ?
Does the counter actually do that ?

Thanks in advance.

Ross.

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

Re: sql counter clarification and issues

Alan DeKok
"Ross Tsolakidis" <[hidden email]> wrote:
> Looking at that debug, where exactly is it resetting the counter ?

  The "counter" module is resetting the counter.

> In the db.daily ?  Because it's not resetting in SQL.

  Yes, the counter module doesn't say it's using SQL.

> Also what is the db.daily ?  How do you read that file ?

  You don't.  Its used by the counter module to keep it's count.

> What I'm trying to achieve here is to get mysql to reset the counters
> its doing, and then start a new row if possible.
> Within the table radacct.  AcctInputOctets AcctOutputOctets.
> Is it possible to do this ?
> Does the counter actually do that ?

  You want sqlcounter.

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

RE: sql counter clarification and issues

Ross Tsolakidis
In reply to this post by Ross Tsolakidis
Thanks for responding.

Can you give me an example of how to set the counter to reset in SQL ?
This is my config for the counter.

    counter daily {
                filename = ${raddbdir}/db.daily
                key = User-Name
                count-attribute = Acct-Session-Time
                reset = daily
                counter-name = Daily-Session-Time
                check-name = Max-Daily-Session
                allowed-servicetype = Framed-User
                cache-size = 5000
        }

Thanks again.


Regards,
--
Ross

-----Original Message-----
From: [hidden email]
[mailto:[hidden email]] On Behalf Of Alan
DeKok
Sent: Wednesday, 29 June 2005 12:58 AM
To: FreeRadius users mailing list
Subject: Re: sql counter clarification and issues

"Ross Tsolakidis" <[hidden email]> wrote:
> Looking at that debug, where exactly is it resetting the counter ?

  The "counter" module is resetting the counter.

> In the db.daily ?  Because it's not resetting in SQL.

  Yes, the counter module doesn't say it's using SQL.

> Also what is the db.daily ?  How do you read that file ?

  You don't.  Its used by the counter module to keep it's count.

> What I'm trying to achieve here is to get mysql to reset the counters
> its doing, and then start a new row if possible.
> Within the table radacct.  AcctInputOctets AcctOutputOctets.
> Is it possible to do this ?
> Does the counter actually do that ?

  You want sqlcounter.

  Alan DeKok.
-
List info/subscribe/unsubscribe? See
http://www.freeradius.org/list/users.html

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

Re: sql counter clarification and issues

"Carlos Martínez-Troncoso C."
In the source code see in doc/rlm_sqlcounter
Reggards,
Carlos Martínez-Troncoso Cera
Coordinador de Servicios Internet/Intranet
Universidad del Norte
Barranquilla, Colombia
Tel: 57 5 3509367


Ross Tsolakidis wrote:
Thanks for responding.

Can you give me an example of how to set the counter to reset in SQL ?
This is my config for the counter.

    counter daily {
                filename = ${raddbdir}/db.daily
                key = User-Name
                count-attribute = Acct-Session-Time
                reset = daily
                counter-name = Daily-Session-Time
                check-name = Max-Daily-Session
                allowed-servicetype = Framed-User
                cache-size = 5000
        }

Thanks again.


Regards,
--
Ross

-----Original Message-----
From: [hidden email]
[[hidden email]] On Behalf Of Alan
DeKok
Sent: Wednesday, 29 June 2005 12:58 AM
To: FreeRadius users mailing list
Subject: Re: sql counter clarification and issues 

"Ross Tsolakidis" [hidden email] wrote:
  
Looking at that debug, where exactly is it resetting the counter ? 
    

  The "counter" module is resetting the counter.

  
In the db.daily ?  Because it's not resetting in SQL.
    

  Yes, the counter module doesn't say it's using SQL.

  
Also what is the db.daily ?  How do you read that file ?
    

  You don't.  Its used by the counter module to keep it's count.

  
What I'm trying to achieve here is to get mysql to reset the counters 
its doing, and then start a new row if possible.
Within the table radacct.  AcctInputOctets AcctOutputOctets.
Is it possible to do this ? 
Does the counter actually do that ?
    

  You want sqlcounter.

  Alan DeKok.
-
List info/subscribe/unsubscribe? See
http://www.freeradius.org/list/users.html

- 
List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html

  

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

Re: sql counter clarification and issues

Alan DeKok
In reply to this post by Ross Tsolakidis
"Ross Tsolakidis" <[hidden email]> wrote:
> Can you give me an example of how to set the counter to reset in SQL ?

  No, I said "sqlcounter".  It's a separate module.  See
"experimental.conf"

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

RE: sql counter clarification and issues

Ross Tsolakidis
In reply to this post by Ross Tsolakidis
Hi all,

Ok I've set it all up, it looks like it's working, when I look in the
debug I see:

Module: Instantiated sql (sql)
Module: Loaded SQL Counter
 sqlcounter: counter-name = "Daily-Session-Time"
 sqlcounter: check-name = "Max-Daily-Session"
 sqlcounter: key = "User-Name"
 sqlcounter: sqlmod-inst = "sqlcca3"
 sqlcounter: query = "SELECT SUM(AcctSessionTime - GREATEST((%b -
UNIX_TIMESTAMP(AcctStartTime)), 0)) FROM radacct WHERE UserName='%{%k}'
AND UNIX_TIMESTAMP(AcctStartTime) + AcctSessionTime > '%b'"
 sqlcounter: reset = "daily"
rlm_sqlcounter: Counter attribute Daily-Session-Time is number 1671
rlm_sqlcounter: Check attribute Max-Daily-Session is number 1672
rlm_sqlcounter: Current Time: 1120445182 [2005-07-04 12:46:22], Next
reset 1120485600 [2005-07-05 00:00:00]
rlm_sqlcounter: Current Time: 1120445182 [2005-07-04 12:46:22], Prev
reset 1120399200 [2005-07-04 00:00:00]
Module: Instantiated sqlcounter (dailycounter)
Module: Loaded Acct-Unique-Session-Id
 acct_unique: key = "User-Name, Acct-Session-Id, NAS-IP-Address,
Client-IP-Address, NAS-Port"
Module: Instantiated acct_unique (acct_unique)

So I understand what it's meant to do:
At midnight every night will it begin a new row in SQL ?
So let's say that someone has maintained a connection all day, SQL will
create a new row ?
Am I understanding this correctly ?
Because at the moment there are no new rows being created, no counters
being reset.

Cheers, thanks again.



Regards,
--
Ross

-----Original Message-----
From: [hidden email]
[mailto:[hidden email]] On Behalf Of Alan
DeKok
Sent: Thursday, 30 June 2005 1:41 AM
To: FreeRadius users mailing list
Subject: Re: sql counter clarification and issues

"Ross Tsolakidis" <[hidden email]> wrote:
> Can you give me an example of how to set the counter to reset in SQL ?

  No, I said "sqlcounter".  It's a separate module.  See
"experimental.conf"

  Alan DeKok.
-
List info/subscribe/unsubscribe? See
http://www.freeradius.org/list/users.html

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

Re: sql counter clarification and issues

Alan DeKok
"Ross Tsolakidis" <[hidden email]> wrote:
> At midnight every night will it begin a new row in SQL ?

  I don't know, I've never used the module.

> Because at the moment there are no new rows being created, no counters
> being reset.

  Is the server receiving accounting packets?

  Run it in debug mode with "reset=hour", and spend some time looking
at what it's doing.

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

RE: sql counter clarification and issues

Ross Tsolakidis
In reply to this post by Ross Tsolakidis
Yes the server is receiving accounting packets.
The SQL rows are updating every 5 mins, (I have the NAS set to send
accounting packets every 5 mins).
 
I run it in debug mode, I see:
Module: Loaded SQL Counter
 sqlcounter: counter-name = "Daily-Session-Time"
 sqlcounter: check-name = "Max-Daily-Session"
 sqlcounter: key = "User-Name"
 sqlcounter: sqlmod-inst = "sqlcca3"
 sqlcounter: query = "SELECT SUM(AcctSessionTime - GREATEST((%b -
UNIX_TIMESTAMP(AcctStartTime)), 0)) FROM radacct WHERE UserName='%{%k}'
AND UNIX_TIMESTAMP(AcctStartTime) + AcctSessionTime > '%b'"
 sqlcounter: reset = "1h"
rlm_sqlcounter: Counter attribute Daily-Session-Time is number 1671
rlm_sqlcounter: Check attribute Max-Daily-Session is number 1672
rlm_sqlcounter: num=1, last=h
rlm_sqlcounter: Current Time: 1120532709 [2005-07-05 13:05:09], Next
reset 1120536000 [2005-07-05 14:00:00]
rlm_sqlcounter: num=1, last=h
rlm_sqlcounter: Current Time: 1120532709 [2005-07-05 13:05:09], Prev
reset 1120532400 [2005-07-05 13:00:00]
Module: Instantiated sqlcounter (dailycounter)

But no reset.

Does anyone have this working, if so, what am I looking for, what am I
supposed to see ?

Thanks  :)

Regards,
--
Ross

-----Original Message-----
From: [hidden email]
[mailto:[hidden email]] On Behalf Of Alan
DeKok
Sent: Tuesday, 5 July 2005 2:05 AM
To: FreeRadius users mailing list
Subject: Re: sql counter clarification and issues

"Ross Tsolakidis" <[hidden email]> wrote:
> At midnight every night will it begin a new row in SQL ?

  I don't know, I've never used the module.

> Because at the moment there are no new rows being created, no counters

> being reset.

  Is the server receiving accounting packets?

  Run it in debug mode with "reset=hour", and spend some time looking at
what it's doing.

  Alan DeKok.
-
List info/subscribe/unsubscribe? See
http://www.freeradius.org/list/users.html

-
List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html