issue with dialup.conf

[Tyller D]

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 at 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 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/d76a28dc/attachment.html>