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. - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
