I think I found the mistake radcheck.value = radcheck.value - '%{Acct-Output-Octets}' - '%{Acct-Input-Octets}' \ should be radcheck.value = 'radcheck.value' - '%{Acct-Output-Octets}' - '%{Acct-Input-Octets}' \ that query wouldn't run before but does after i quote 'radcheck.value'. Hopefully that fixes the issue. Thanks On Mon, Feb 7, 2011 at 8:48 AM, Tyller D <tyllerd@gmail.com> wrote:
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