issue with dialup.conf

[Tyller D]

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 at fajar.net> wrote:

> On Mon, Feb 7, 2011 at 1:08 AM, Tyller D <tyllerd at 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
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.freeradius.org/pipermail/freeradius-users/attachments/20110207/de5fb2bf/attachment.html>