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