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

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

On Mon, Feb 07, 2011 at 08:48:27AM +0200, Tyller D wrote:

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"; }

That logic returns RLM_MODULE_REJECT in both branches of the if statement, so I imagine you haven't copy-pasted it correctly. But I think I see what you're getting at.

exctract from radcheck: +------+----------+--------------------+----+----------+ | id | username | attribute | op | value | +------+----------+--------------------+----+----------+ | 3069 | Joe | databank | := | 52428800 | | 3068 | Joe | Cleartext-Password | := | Joe123 | | 3070 | Joe | Auth-Type | := | Perl | +------+----------+--------------------+----+----------+

OK, so you're using radcheck as a convenient place to store the user's remaining quota. I guess that should work.

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 | +------------+

Well, those are two different things. The databank value will be whatever original value you put in databank (which you haven't told us), minus the bytes in and bytes out. So that would be correct if the initial value was 1537923477 I have to say this looks like a pretty fragile way of doing accounting, because you are relying 100% on Stop packets. This means: (1) A lost stop packet will not update databank (2) If a user stays online solidly for months, they won't get their quota updated in the database Doing accounting based on interim-update packets is more robust. However, they are cumulative (each interim-update packet shows the *total* used so far for that session, just as if it were a Stop packet), so you can't just subtract them from an accumulator. You need to add together the last values seen for each session.

On Mon, Feb 07, 2011 at 12:44:01PM +0200, Tyller D wrote:

+------------------------------------------+ | sum(acctinputoctets + acctoutputoctets ) | +------------------------------------------+ | 1840263628 | +------------------------------------------+ 1 row in set (0.00 sec) mysql> select * from radcheck where username='scotty'; +------+----------+--------------------+----+------------+ | id | username | attribute | op | value | +------+----------+--------------------+----+------------+ | 5192 | Scotty | Auth-Type | := | Perl | | 5191 | Scotty | databank | := | -302340151 | | 5190 | Scotty | Cleartext-Password | := | DALNIC | +------+----------+--------------------+----+------------+ 3 rows in set (0.00 sec) original databank value = 262141750 as you can see the amount of traffic used, far exceeds what was "assigned" originally.

Well, I think you're going about this the wrong way, using an extremely fragile way of doing accounting. If your customer queries their bill, or claims to have been cut off too early, they will probably want to see day-by-day usage and proof that the total usage exceeds their limit. So you should be tracking individual sessions, adding the session values together, to give the total usage.

When I ran the queries manually I noticed that without quoting the radcheck.value in the accounting_stop_query_alt querie, it would not update the radcheck.value for databank. but once it is quoted seems, to work.

Quoting will give you a string, which will be treated as zero, won't it? mysql> select 'wibble.bibble' - '1234' from dual; +--------------------------+ | 'wibble.bibble' - '1234' | +--------------------------+ | -1234 | +--------------------------+ 1 row in set, 1 warning (0.00 sec) Indeed, you're using string columns to hold integer values. This seems to work, with mysql anyway, but you'll need to be very careful what you're doing. mysql> select '1234' - '5' from dual; +--------------+ | '1234' - '5' | +--------------+ | 1229 | +--------------+ 1 row in set (0.00 sec)

Im not too clued up on freeradius, so just to clear my mind to only way for a session to end is on a stop request(correct?) and even if interim updates are send, on the stop request it will show all data used in the session (not just just since the last update)

The Stop request will show the total data used in the session, not since the last update, yes. Accounting packets are carried over UDP and delivery is not guaranteed. Your NAS may try resending the packet a few times if it doesn't get a response, but will give up. Furthermore, you may receive duplicate Stop packets, for the same reason (lost UDP acknowledgements). If you are subtracting the session usage from an accumulator each time you receive a Stop, you may end up subtracting it multiple times. So, like I say, this is a really bad way to do accounting. You have been given details of better ways to do it (rlm_sqlcounter). If you ignore this advice, you take the consequences - sorry. Regards, Brian.

Sorry, maybe i didn't explain correctly. Im not updating the database. I am using auth-type perl and when the user authenticates my perl script looks in the db to see what nas is bieng used on that IP and then checks the value for "databank" for the user and then send the correct attribute with the correct value like so $RAD_REPLY{'Nomadix-MaxBytesDown'} = "$DATABANK"; (in this example its a nomadix gateway) That part works perfectly. The issue im having is when the stop request comes in, it doesn't update the value in the radcheck table, well it does but only sometimes.. On Sun, Feb 6, 2011 at 8:32 PM, Brian Candler <B.Candler@pobox.com> wrote:

On Sun, Feb 06, 2011 at 04:55:34PM +0200, Tyller D wrote:

...

freeradius is used to authenticate users at our hotspots, as we are using different nas devices and we must cater for them all i did this. in dictionary file I added an attribute called databank, then I created a perl script to check which type of nas the user is coming from and renaming databank to the correct attribute, now that works fine.

Not sure what you mean by "renaming databank to the correct attribute" - are you actually changing the authentication database? If so that's scary.

If you simply want to send a different attribute based on what type of NAS they are connecting from, then you can change the response attribute sent, without modifying the underlying database.

For identifying the NAS: the simplest way is to use the preprocess module. It reads the "huntgroups" file which maps NAS-IP-Address to control attribute Huntgroup-Name, and then you can use a regular users file to add an appropriate attribute, or do it in unlang:

sql

if ( "%{control:Huntgroup-Name}" == "Foo" && "%{reply:Databank}" ) { update reply { OtherAttr = "%{reply:Databank}" Databank !* "" } }

Or you can do anything in perl of course.

The point is, I don't think you should be updating the database simply to change which reply attribute is sent based on which NAS they're connecting from at this instant.

...

Then on the stop request I need to update the value associated to databank to be (databank - (acctinputoctets+acctoutputoctets)). that way limiting users to only as much "data" as we stipulated in the begining.

I'm guessing your "databank" is some sort of data limit attribute, a bit like a Session-Timeout but for bytes transferred?

...

my problem is that it doesn't always update the databank value, I can see how much traffic a users used in his session (i.e. in the radacct table) so the accounting_stop_query & accounting_stop_query_alt are not failing, its just not updating the databank value in radcheck. ... here are the two queries 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}'"

That's a scary update: updating two independent tables with the same query.

What database are you using? Calling a stored procedure would be a much cleaner way of doing this, if your database supports it. (I use mysql which does)

accounting_stop_query = "CALL process_stop_packet(...)"

Then you can do two separate updates, which I think is what you really want.

...

does anyone know how this can happen?

Watch radiusd -X until you see it happen. Look at exactly what SQL updates are being done.

...

could it be something to do with interim-updates (grasping at straws here).

Sounds highly unlikely to me.

- List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html