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