Hi The main reason I am doing it this way to send the correct attributes per location. Hopefully this explains whats happening. perl.pl ..... if ( $device =~ /^nomadix/i ) { if ($DATABANK != '') { if ( $DATABANK le 0 ) { $RAD_REPLY{'Reply-Message'} = "You have no more Data Left"; return RLM_MODULE_REJECT; }else { return RLM_MODULE_REJECT; $RAD_REPLY{'Nomadix-MaxBytesDown'} = "$DATABANK"; } ..... if ( $device =~ /^mikrotik/i ) { if ($DATABANK != '') { if ( $DATABANK <= 0 ) { $RAD_REPLY{'Reply-Message'} = "You have no more Data Left"; return RLM_MODULE_REJECT; }else { $RAD_REPLY{'Mikrotik-Xmit-Limit'} = "$DATABANK"; } exctract from radcheck: +------+----------+--------------------+----+----------+ | id | username | attribute | op | value | +------+----------+--------------------+----+----------+ | 3069 | Joe | databank | := | 52428800 | | 3068 | Joe | Cleartext-Password | := | Joe123 | | 3070 | Joe | Auth-Type | := | Perl | +------+----------+--------------------+----+----------+ stop query accounting_stop_query = " \ UPDATE radacct,radcheck SET \ radacct.acctstoptime = '%S', \ radacct.acctsessiontime = '%{Acct-Session-Time}', \ radacct.acctinputoctets = '%{%{Acct-Input-Gigawords}:-0}' << 32 | \ '%{%{Acct-Input-Octets}:-0}', \ radacct.acctoutputoctets = '%{%{Acct-Output-Gigawords}:-0}' << 32 | \ '%{%{Acct-Output-Octets}:-0}', \ radacct.acctterminatecause = '%{Acct-Terminate-Cause}', \ radcheck.value = radcheck.value - '%{Acct-Output-Octets}' - '%{Acct-Input-Octets}', \ radacct.acctstopdelay = '%{%{Acct-Delay-Time}:-0}', \ radacct.connectinfo_stop = '%{Connect-Info}' \ WHERE radacct.acctsessionid = '%{Acct-Session-Id}' \ AND radcheck.username = '%{SQL-User-Name}' \ AND radcheck.attribute = 'databank' \ AND radacct.username = '%{SQL-User-Name}' \ AND radacct.nasipaddress = '%{NAS-IP-Address}'" accounting_stop_query_alt = " \ UPDATE radacct,radcheck SET \ radacct.acctstoptime = '%S', \ radacct.acctsessiontime = '%{Acct-Session-Time}', \ radacct.acctinputoctets = '%{%{Acct-Input-Gigawords}:-0}' << 32 | \ '%{%{Acct-Input-Octets}:-0}', \ radacct.acctoutputoctets = '%{%{Acct-Output-Gigawords}:-0}' << 32 | \ '%{%{Acct-Output-Octets}:-0}', \ radacct.acctterminatecause = '%{Acct-Terminate-Cause}', \ radacct.acctstopdelay = '%{%{Acct-Delay-Time}:-0}', \ radacct.connectinfo_stop = '%{Connect-Info}', \ radcheck.value = radcheck.value - '%{Acct-Output-Octets}' - '%{Acct-Input-Octets}' \ WHERE radacct.acctsessionid = '%{Acct-Session-Id}' \ AND radacct.username = '%{SQL-User-Name}' \ AND radacct.nasipaddress = '%{NAS-IP-Address}' \ AND radcheck.username = '%{SQL-User-Name}' \ AND radcheck.attribute = 'databank'" So the thoery is this, user tries to login, we check the NAS device , check the databank and get the values and send the reply-attribute that suits the gateway (else fail). when the user sends the stop query we re-update the the databank value in the radcheck table. so the value for databank in the radcheck table should always be equal to databank - sum(acctoutputoctest + acctinputoctets) but it not always exectuting stop request correctly because mysql> select sum(acctinputoctets + acctoutputoctets) from radacct where username='scotty'; +-----------------------------------------+ | sum(acctinputoctets + acctoutputoctets) | +-----------------------------------------+ | 1840263628 | +-----------------------------------------+ mysql> select value from radcheck where username='scotty' and attribute='databank'; +------------+ | value | +------------+ | -302340151 | +------------+ Do you guys see where I have made an error? On Mon, Feb 7, 2011 at 12:56 AM, Fajar A. Nugraha <list@fajar.net> wrote:
On Mon, Feb 7, 2011 at 1:08 AM, Tyller D <tyllerd@gmail.com> wrote:
Hi
I will try run the queries manually and see what happens.
rlm_sqlcounter is cool, but this should work right?
The idea looks good.
However, since you're trying to reimplement what's already available, you might have a hard time trying to get everything right. For example, what happens when a user exceeds their quota? Your setup would accept the user while sending a negative quota (which might or might not work).
On my implmentation, I use rlm_sqlcounter, but I changed the way it gets the numbers. I didn't like the way it does a sum() on radacct every time a user logs in (one of the reasons were I want to delete old entries from my radacct table), so I created an additional table to record total usage, and update it using sql trigger. So instead of having to examine thousands of rows when a user logs in, now the db simply has to examine one row, plus update that row when a user logs out. This way I can still make use of rlm_sqlcounter without having to reinvent the whole logic behind it.
-- Fajar - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html