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