sql counter clarification and issues

Ross Tsolakidis Ross.Tsolakidis at day3.com.au
Thu Jul 14 01:38:30 CEST 2005


Thanks for clearing that up Marcin  :)

Does anyone else have any other suggestions on how to extract daily
totals ? 


Regards,
--
Ross

-----Original Message-----
From: Marcin Jessa [mailto:lists at yazzy.org] 
Sent: Wednesday, 13 July 2005 4:38 PM
To: Ross Tsolakidis
Cc: freeradius-users at lists.freeradius.org
Subject: Re: sql counter clarification and issues

On Wed, 13 Jul 2005 12:15:45 +1000
"Ross Tsolakidis" <Ross.Tsolakidis at day3.com.au> wrote:

>  
> Thanks for responding Marcin, just to double check:
> 
> 
> I added those details into radgroupcheck:
> mysql>  select * from radgroupcheck;
> +----+-----------+---------------------+----+-------+
> | id | GroupName | Attribute           | op | Value |
> +----+-----------+---------------------+----+-------+
> |  1 | 24Hours   | Max-Daily-Session   | := | 86400 |
> |  2 | 24Hours   | Max-Weekly-Session  | := | 86400 |
> |  3 | 24Hours   | Max-Monthly-Session | := | 86400 |
> |  4 | 24Hours   | Max-All-Session     | := | 86400 |
> |  5 | 10Mins    | Max-Daily-Session   | := | 600   |
> |  6 | 10Mins    | Max-Weekly-Session  | := | 600   |
> |  7 | 10Mins    | Max-Monthly-Session | := | 600   |
> |  8 | 10Mins    | Max-All-Session     | := | 600   |
> +----+-----------+---------------------+----+-------+
> 8 rows in set (0.00 sec)
> 
> mysql>
> 
> ==
> 
> My sqlcounter.conf is exactly the same.
> 
> I have added this:
> $INCLUDE  ${confdir}/sqlcounter.conf
> Into the radiusd.conf
> 
> ==
> 
> When I fire up radius in debug mode I get these messages for
sqlcounter:
> 
> Module: Loaded SQL Counter
>  sqlcounter: counter-name = "Daily-Session-Time"
>  sqlcounter: check-name = "Max-Daily-Session"
>  sqlcounter: key = "User-Name"
>  sqlcounter: sqlmod-inst = "sql"
>  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: 1121220718 [2005-07-13 12:11:58], Next 
> reset 1121263200 [2005-07-14 00:00:00]
> rlm_sqlcounter: Current Time: 1121220718 [2005-07-13 12:11:58], Prev 
> reset 1121176800 [2005-07-13 00:00:00]
> Module: Instantiated sqlcounter (dailycounter)
> 
> ==
> 
> I read the experimental.conf but I can't seem to understand where the 
> counters are reset, which table ?
> Could you give me an example of the table so I can see what it should 
> look like ?  (daily totals) I don't really have any need to disconnect

> people after a certain period of time, just need daily and monthly 
> totals.

Then sqlcounter is not for you. 
If you need stats, those are stored in radacct table and can be sorted
with SQL queries + maybe some code.
Take a look at dialupadmin, how it's done there.


> Thanks again, greatly appreciated !  :)
> 
> 
> Regards,
> --
> Ross
> 
> -----Original Message-----
> From: Marcin Jessa [mailto:lists at yazzy.org]
> Sent: Tuesday, 12 July 2005 6:03 PM
> To: FreeRadius users mailing list
> Cc: Ross Tsolakidis
> Subject: Re: sql counter clarification and issues
> 
> On Tue, 12 Jul 2005 15:21:52 +1000
> "Ross Tsolakidis" <Ross.Tsolakidis at day3.com.au> wrote:
> 
> >  Ok, now I'm really confused  :(
> > 
> > Can someone please clarify exactly what does the sqlcounter module 
> > do
> ?
> > Basically I need to calculate the data on a daily basis for each
user.
> 
> sqlcounter works fine. 
> Your radgroupcheck or radcheck needs to have the same attributes you 
> have in your sqlcounter config.
> Check out mine:
> http://www.yazzy.org/configs/linux/freeradius/sqlcounter.conf
> 
> mysql> select * from radgroupcheck;
> +----+-----------+---------------------+----+-------+
> | id | GroupName | Attribute           | op | Value |
> +----+-----------+---------------------+----+-------+
> |  1 | 24hours   | Max-Daily-Session   | := | 86400 |
> |  2 | 24hours   | Max-Weekly-Session  | := | 86400 |
> |  3 | 24hours   | Max-Monthly-Session | := | 86400 |
> |  4 | 24hours   | Max-All-Session     | := | 86400 |
> |  5 | 10mins    | Max-Daily-Session   | := | 600   |
> |  6 | 10mins    | Max-Weekly-Session  | := | 600   |
> |  7 | 10mins    | Max-Monthly-Session | := | 600   |
> |  8 | 10mins    | Max-All-Session     | := | 600   |
> +----+-----------+---------------------+----+-------+
> 8 rows in set (0.00 sec)
> 
> Read experimental.conf, it describes the module.
> It count's user's online time stored in the raddacct table and based 
> on that info allows user to log in if her allowed session time is not 
> exceeded.
> This can be done on daily, weekly, monthly or total basis.
> 
> > Thanks for your patience  :)
> 
> Read former postings to the list describing the behaviour and 
> functionality of the module.
> People tend to be more patient if they know you did some work on your 
> own.
> 
> Cheers,
> Marcin Jessa
> 
> 
> > 
> > -----Original Message-----
> > From: freeradius-users-bounces at lists.freeradius.org
> > [mailto:freeradius-users-bounces at lists.freeradius.org] On Behalf Of 
> > Ross Tsolakidis
> > Sent: Tuesday, 5 July 2005 2:14 PM
> > To: FreeRadius users mailing list
> > Subject: RE: sql counter clarification and issues
> > 
> > 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: freeradius-users-bounces at lists.freeradius.org
> > [mailto:freeradius-users-bounces at lists.freeradius.org] 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" <Ross.Tsolakidis at day3.com.au> 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
> > 
> > -
> > List info/subscribe/unsubscribe? See 
> > http://www.freeradius.org/list/users.html




More information about the Freeradius-Users mailing list