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@yazzy.org] Sent: Wednesday, 13 July 2005 4:38 PM To: Ross Tsolakidis Cc: freeradius-users@lists.freeradius.org Subject: Re: sql counter clarification and issues On Wed, 13 Jul 2005 12:15:45 +1000 "Ross Tsolakidis" <Ross.Tsolakidis@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@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@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@lists.freeradius.org [mailto:freeradius-users-bounces@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@lists.freeradius.org [mailto:freeradius-users-bounces@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@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