Re: Mysql, Accounting and DialupAdmin
On Thu, Nov 8, 2012 at 2:43 PM, Erich Titl erich.t...@think.ch wrote: Hi Fajar on 08.11.2012 08:16, Fajar A. Nugraha wrote: ... IIRC only one of them will be used. I suggest you dop MD5 (since it's useless for your purpose) and Cleartext (you don't want that, right?) and verify you use the correct NT-Password (use smbencrypt if you haven't already done so) Yes, it appears that authentication using NT-Password hash works fine for M$. What would be the least common setting in a multi vendor environment. I guess, OSX, for example, is using a different protocol. Most other supplicants can use EAP-MSCHAPv2 just fine, so you shouldn't have any problems with other OS. NT-Password should work with PAP as well, so PAP and TTLS-PAP should also work, if you need to choose that for some reason. Also note that storing NT-Passwords should be considered as insecure as storing cleartext password (since cracking MD4 hash is easy-enough), but at least you won't see the cleartext password in the database. -- Fajar - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Re: Mysql, Accounting and DialupAdmin
On Thu, Nov 8, 2012 at 2:08 PM, Erich Titl erich.t...@think.ch wrote: 2) I could see login and logout information, but no data usage, e.g. dowload and upload sizes appear to be zeroes. Some NAS (e.g. AP's flashed with dd-wrt) simply doesn't send accounting packets. Blame your NAS :P :-( Do you have a recommendation for AP's that pass this information? ... or to be more acccurate, look at your NAS documentation (or ask the vendor) how to get it to send accounting packets. It is a ZyXEL, so basically a black box, even to the local vendor. Just to be sure, you HAVE enabled sql in accounting section, right? If you want to be extra sure, run FR in debug mode, and do a login-logout using a client (e.g. notebook) to the NAS (i.e. AP). FR should print out what packets it received. If it DOESN'T show any accounting packets, then your NAS doesn't send them, or hasn't been configured to do so. -- Fajar - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Re: Mysql, Accounting and DialupAdmin
on 08.11.2012 09:01, Fajar A. Nugraha wrote:
...
It is a ZyXEL, so basically a black box, even to the local vendor.
Just to be sure, you HAVE enabled sql in accounting section, right?
I guess the fact that I have entries in the radacct table which
correspond to actual connection attempts should prove that.
mysql select username,acctstarttime,acctstoptime,acctinputoctets from
radacct;
+--+-+-+-+
| username | acctstarttime | acctstoptime| acctinputoctets |
+--+-+-+-+
| test | 2012-11-07 15:09:47 | 2012-11-07 15:15:48 | 0 |
| test | 2012-11-07 15:15:48 | 2012-11-07 15:25:02 | 0 |
| test | 2012-11-07 15:25:32 | 2012-11-07 15:41:52 | 0 |
| test | 2012-11-07 21:20:53 | 2012-11-07 21:24:13 | 0 |
| test | 2012-11-07 21:41:50 | 2012-11-07 21:42:13 | 0 |
| test | 2012-11-07 21:42:43 | 2012-11-07 21:47:14 | 0 |
| test | 2012-11-08 07:52:42 | 2012-11-08 07:55:45 | 0 |
| test | 2012-11-08 08:35:15 | 2012-11-08 08:50:22 | 0 |
| test | 2012-11-08 09:56:24 | 2012-11-08 10:02:28 | 0 |
| test | 2012-11-08 10:06:58 | 2012-11-08 10:07:23 | 0 |
| test | 2012-11-08 10:11:31 | 2012-11-08 10:12:06 | 0 |
| test | 2012-11-08 10:12:20 | 2012-11-08 10:12:35 | 0 |
| test | 2012-11-08 10:12:42 | 2012-11-08 10:13:11 | 0 |
| test | 2012-11-08 10:13:27 | 2012-11-08 10:14:38 | 0 |
| test | 2012-11-08 10:14:51 | NULL| 0 |
+--+-+-+-+
If you want to be extra sure, run FR in debug mode, and do a
login-logout using a client (e.g. notebook) to the NAS (i.e. AP). FR
should print out what packets it received. If it DOESN'T show any
accounting packets, then your NAS doesn't send them, or hasn't been
configured to do so.
I _guess_ it shows some accounting
rad_recv: Accounting-Request packet from host 194.124.158.62 port 47037,
id=165, length=135
Acct-Session-Id = 509ACAB9-000F
Acct-Status-Type = Start
Acct-Authentic = RADIUS
User-Name = test
NAS-Port = 0
Called-Station-Id = 50-67-F0-38-A9-E5:ZyXEL
Calling-Station-Id = 74-F0-6D-07-9B-91
NAS-Port-Type = Wireless-802.11
Connect-Info = CONNECT 0Mbps 802.11
# Executing section preacct from file
/usr/local/etc/raddb/sites-enabled/default
+- entering group preacct {...}
++[preprocess] returns ok
[acct_unique] WARNING: Attribute NAS-Identifier was not found in
request, unique ID MAY be inconsistent
[acct_unique] Hashing 'NAS-Port = 0,,NAS-IP-Address =
194.124.158.62,Acct-Session-Id = 509ACAB9-000F,User-Name = test'
[acct_unique] Acct-Unique-Session-ID = de12b16f3f8a6cf8.
++[acct_unique] returns ok
++[files] returns noop
# Executing section accounting from file
/usr/local/etc/raddb/sites-enabled/default
+- entering group accounting {...}
[detail]expand: %{Packet-Src-IP-Address} - 194.124.158.62
[detail]expand:
/usr/local/var/log/radius/radacct/%{%{Packet-Src-IP-Address}:-%{Packet-Src-IPv6-Address}}/detail-%Y%m%d
- /usr/local/var/log/radius/radacct/194.124.158.62/detail-20121108
[detail]
/usr/local/var/log/radius/radacct/%{%{Packet-Src-IP-Address}:-%{Packet-Src-IPv6-Address}}/detail-%Y%m%d
expands to /usr/local/var/log/radius/radacct/194.124.158.62/detail-20121108
[detail]expand: %t - Thu Nov 8 10:22:38 2012
++[detail] returns ok
[sql] expand: %{User-Name} - test
[sql] sql_set_user escaped user -- 'test'
[sql] expand: %{Acct-Delay-Time} -
[sql] ... expanding second conditional
[sql] expand:INSERT INTO radacct
(acctsessionid,acctuniqueid, username, realm,
nasipaddress, nasportid, nasporttype,
acctstarttime,acctstoptime, acctsessiontime,
acctauthentic,connectinfo_start, connectinfo_stop,
acctinputoctets, acctoutputoctets, calledstationid,
callingstationid, acctterminatecause, servicetype,
framedprotocol, framedipaddress, acctstartdelay,
acctstopdelay,xascendsessionsvrkey) VALUES
('%{Acct-Session-Id}', '%{Acct-Unique-Session-Id}',
'%{SQL-User-Name}', '%{Realm}', '%{NAS-IP-Address}',
'%{NAS-Port}', '%{NAS-Port-Type}', '%S', NULL,
'0', '%{Acct-Authentic}', '%{Connect-Info}', '', '0', '0',
'%{Called-Station-Id}', '%{Calling-Station-Id}', '',
'%{Service-Type}', '%{Framed-Protocol}', '%{Framed-IP-Address}',
rlm_sql (sql): Reserving sql socket id: 1
rlm_sql (sql): Released sql socket id: 1
++[sql] returns ok
Erich
smime.p7s
Description: S/MIME Kryptografische Unterschrift
-
List
Re: Mysql, Accounting and DialupAdmin
On Thu, Nov 8, 2012 at 4:27 PM, Erich Titl erich.t...@think.ch wrote: I _guess_ it shows some accounting rad_recv: Accounting-Request packet from host 194.124.158.62 port 47037, id=165, length=135 Acct-Session-Id = 509ACAB9-000F Acct-Status-Type = Start Do some stuff first with the client (e.g. browsing), then disconnect. Look for accounting stop packet. If it doesn't show Acct-In-Octets and friends, then your AP is seriously broken. -- Fajar - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Re: Mysql, Accounting and DialupAdmin
On Wed, Nov 7, 2012 at 10:16 PM, Erich Titl erich.t...@think.ch wrote: Hi Folks I succeeded to get my set up running with FR 2.2.0 and Mysql, e.g. I can connect through a ZyXEL NWA 3160 using credentials in the MySQL database using a M$ Windows 7 client. Everything is still quite raw and blurry to me. Could someone point me to the right dos for the following? 1) I had to enter cleartext password into the mysql database, apparently other formats were not accepted Because you use Windows client, which defaults to EAP-MSCHAPv2. See http://deployingradius.com/documents/protocols/compatibility.html If your main concern is I don't want to store cleartext password in db, you should be able to use NT-Password. Search the list archive, there's a recent thread about this. 2) I could see login and logout information, but no data usage, e.g. dowload and upload sizes appear to be zeroes. Some NAS (e.g. AP's flashed with dd-wrt) simply doesn't send accounting packets. Blame your NAS :P ... or to be more acccurate, look at your NAS documentation (or ask the vendor) how to get it to send accounting packets. -- Fajar - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Re: Mysql, Accounting and DialupAdmin
Hi Fajar on 08.11.2012 03:35, Fajar A. Nugraha wrote: On Wed, Nov 7, 2012 at 10:16 PM, Erich Titl erich.t...@think.ch wrote: Hi Folks I succeeded to get my set up running with FR 2.2.0 and Mysql, e.g. I can connect through a ZyXEL NWA 3160 using credentials in the MySQL database using a M$ Windows 7 client. Everything is still quite raw and blurry to me. Could someone point me to the right dos for the following? 1) I had to enter cleartext password into the mysql database, apparently other formats were not accepted Because you use Windows client, which defaults to EAP-MSCHAPv2. See http://deployingradius.com/documents/protocols/compatibility.html If your main concern is I don't want to store cleartext password in db, you should be able to use NT-Password. Search the list archive, there's a recent thread about this. Thanks, I read that URL, actually that one guided me to enter a Cleartext Password at all. mysql select * from radcheck; ++--+++--+ | id | username | attribute | op | value | ++--+++--+ | 1 | test | MD5-Password | := | 81dc9bdb52d04dc20036dbd8313ed055 | | 2 | test | NT-Password| := | 7CE21F17C0AEE7FB9CEBA532D0546AD6 | | 3 | test | Cleartext-Password | := | 1234 | ++--+++--+ 2) I could see login and logout information, but no data usage, e.g. dowload and upload sizes appear to be zeroes. Some NAS (e.g. AP's flashed with dd-wrt) simply doesn't send accounting packets. Blame your NAS :P :-( Do you have a recommendation for AP's that pass this information? ... or to be more acccurate, look at your NAS documentation (or ask the vendor) how to get it to send accounting packets. It is a ZyXEL, so basically a black box, even to the local vendor. Thanks Erich smime.p7s Description: S/MIME Kryptografische Unterschrift - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Re: Mysql, Accounting and DialupAdmin
On Thu, Nov 8, 2012 at 2:08 PM, Erich Titl erich.t...@think.ch wrote: Thanks, I read that URL, actually that one guided me to enter a Cleartext Password at all. See the column labeled NT hash? mysql select * from radcheck; ++--+++--+ | id | username | attribute | op | value | ++--+++--+ | 1 | test | MD5-Password | := | 81dc9bdb52d04dc20036dbd8313ed055 | | 2 | test | NT-Password| := | 7CE21F17C0AEE7FB9CEBA532D0546AD6 | | 3 | test | Cleartext-Password | := | 1234 | ++--+++--+ IIRC only one of them will be used. I suggest you dop MD5 (since it's useless for your purpose) and Cleartext (you don't want that, right?) and verify you use the correct NT-Password (use smbencrypt if you haven't already done so) 2) I could see login and logout information, but no data usage, e.g. dowload and upload sizes appear to be zeroes. Some NAS (e.g. AP's flashed with dd-wrt) simply doesn't send accounting packets. Blame your NAS :P :-( Do you have a recommendation for AP's that pass this information? Nope. Sorry. Try looking at the archives, I think Cisco boxes sends them. As an alternative, if you're fine with captive-portal setup, chillispot sends accounting packets just fine. ... or to be more acccurate, look at your NAS documentation (or ask the vendor) how to get it to send accounting packets. It is a ZyXEL, so basically a black box, even to the local vendor. Then blame the vendor. Seriously. Why would you want to use something that even the local vendor can't support? -- Fajar - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Re: Mysql, Accounting and DialupAdmin
Hi Fajar on 08.11.2012 08:16, Fajar A. Nugraha wrote: ... IIRC only one of them will be used. I suggest you dop MD5 (since it's useless for your purpose) and Cleartext (you don't want that, right?) and verify you use the correct NT-Password (use smbencrypt if you haven't already done so) Yes, it appears that authentication using NT-Password hash works fine for M$. What would be the least common setting in a multi vendor environment. I guess, OSX, for example, is using a different protocol. 2) I could see login and logout information, but no data usage, e.g. dowload and upload sizes appear to be zeroes. ... It is a ZyXEL, so basically a black box, even to the local vendor. Then blame the vendor. Seriously. Why would you want to use something that even the local vendor can't support? I am in an evaluation phase and this is a vendor with widespread acceptance here. Finding such a weakness is important as we will probably drop the product then. Unfortunately not everyone is really comfortable with open source products. This is just the kind of reality the vendors try to lock us in. Thanks Erich smime.p7s Description: S/MIME Kryptografische Unterschrift - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Re: MySQL accounting issue
Andy Smith wrote: I have attempted to configure freeradius to write accounting data to MySQL, however currently its not working. No data is being written to MySQL and the Radius client is complaining its not recieving a reply for accounting requests. I subsituted the detail setting in the accounting config section of radiusd.conf for sql and modifyied the sql.conf as seemed necessary. As soon as I switch this back to detail my Radius client starts recieving replies from freeradius once again :S So the sql module isn't logging anything. But after that there is no activity in the MySQL log :S And I have no errors logged from MySQL or radiusd, nor can I see any errors when running radiusd -Xf anyone any ideas what more I can do to identify the issue?? Post the output of radiusd -X here, as suggested in the FAQ, README, INSTALL, and (almost) daily. Alan DeKok. - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Re: MySQL accounting issue
Hi,
ok this is an example of when its meant to do some accounting, in this
example I have both detail and sql set for accounting in radiusd.conf.
Nothing to do. Sleeping until we see a request.
rad_recv: Accounting-Request packet from host 89.107.16.10:35377, id=82,
length=145
Acct-Status-Type = Failed
Service-Type = IAPP-Register
Attr-102 = 0x0194
Error-Cause = 32
Event-Timestamp = Jan 21 2008 13:05:11 GMT
Attr-105 = 0x61733738333035366464
Acct-Session-Id = [EMAIL PROTECTED]
Attr-108 = 0x38392e3130372e31362e39
Attr-109 = 0x35303630
NAS-Port = 5060
Acct-Delay-Time = 0
NAS-IP-Address = 89.107.16.10
Processing the preacct section of radiusd.conf
modcall: entering group preacct for request 85
modcall[preacct]: module preprocess returns noop for request 85
rlm_realm: Proxy reply, or no User-Name. Ignoring.
modcall[preacct]: module suffix returns noop for request 85
modcall[preacct]: module files returns noop for request 85
modcall: leaving group preacct (returns noop) for request 85
Processing the accounting section of radiusd.conf
modcall: entering group accounting for request 85
radius_xlat:
'/usr/local/var/log/radius/radacct/89.107.16.10/detail-20080121'
rlm_detail:
/usr/local/var/log/radius/radacct/%{Client-IP-Address}/detail-%Y%m%d expands
to /usr/local/var/log/radius/radacct/89.107.16.10/detail-20080121
modcall[accounting]: module detail returns ok for request 85
rlm_sql (sql): Unsupported Acct-Status-Type = 15
modcall[accounting]: module sql returns noop for request 85
modcall: leaving group accounting (returns ok) for request 85
Sending Accounting-Response of id 82 to 89.107.16.10 port 35377
Finished request 85
Going to the next request
At startup I see this from radiusd
Starting - reading configuration files ...
reread_config: reading radiusd.conf
Config: including file: /usr/local/etc/raddb/proxy.conf
Config: including file: /usr/local/etc/raddb/clients.conf
Config: including file: /usr/local/etc/raddb/snmp.conf
Config: including file: /usr/local/etc/raddb/sql.conf
main: prefix = /usr/local
main: localstatedir = /usr/local/var
main: logdir = /usr/local/var/log/radius
main: libdir = /usr/local/lib
main: radacctdir = /usr/local/var/log/radius/radacct
main: hostname_lookups = no
main: max_request_time = 30
main: cleanup_delay = 5
main: max_requests = 1024
main: delete_blocked_requests = 0
main: port = 0
main: allow_core_dumps = no
main: log_stripped_names = no
main: log_file = /usr/local/var/log/radius/radius.log
main: log_auth = no
main: log_auth_badpass = no
main: log_auth_goodpass = no
main: pidfile = /usr/local/var/run/radiusd/radiusd.pid
main: user = (null)
main: group = (null)
main: usercollide = no
main: lower_user = no
main: lower_pass = no
main: nospace_user = no
main: nospace_pass = no
main: checkrad = /usr/local/sbin/checkrad
main: proxy_requests = yes
proxy: retry_delay = 5
proxy: retry_count = 3
proxy: synchronous = no
proxy: default_fallback = yes
proxy: dead_time = 120
proxy: post_proxy_authorize = no
proxy: wake_all_if_all_dead = no
security: max_attributes = 200
security: reject_delay = 1
security: status_server = no
main: debug_level = 0
read_config_files: reading dictionary
read_config_files: reading naslist
Using deprecated naslist file. Support for this will go away soon.
read_config_files: reading clients
read_config_files: reading realms
radiusd: entering modules setup
Module: Library search path is /usr/local/lib
Module: Loaded expr
Module: Instantiated expr (expr)
Module: Loaded PAP
pap: encryption_scheme = crypt
pap: auto_header = no
Module: Instantiated pap (pap)
Module: Loaded CHAP
Module: Instantiated chap (chap)
Module: Loaded MS-CHAP
mschap: use_mppe = yes
mschap: require_encryption = no
mschap: require_strong = no
mschap: with_ntdomain_hack = no
mschap: passwd = (null)
mschap: ntlm_auth = (null)
Module: Instantiated mschap (mschap)
Module: Loaded System
unix: cache = no
unix: passwd = /etc/passwd
unix: shadow = /etc/shadow
unix: group = /etc/group
unix: radwtmp = /usr/local/var/log/radius/radwtmp
unix: usegroup = no
unix: cache_reload = 600
Module: Instantiated unix (unix)
Module: Loaded preprocess
preprocess: huntgroups = /usr/local/etc/raddb/huntgroups
preprocess: hints = /usr/local/etc/raddb/hints
preprocess: with_ascend_hack = no
preprocess: ascend_channels_per_line = 23
preprocess: with_ntdomain_hack = no
preprocess: with_specialix_jetstream_hack = no
preprocess: with_cisco_vsa_hack = no
preprocess: with_alvarion_vsa_hack = no
Module: Instantiated preprocess (preprocess)
Module: Loaded SQL
sql: driver = rlm_sql_mysql
sql: server = localhost
sql: port =
sql: login = root
sql: password = password
sql: radius_db = radius
sql: nas_table = nas
sql: sqltrace = yes
sql: sqltracefile = /usr/local/var/log/radius/sqltrace.sql
sql: readclients = yes
sql:
Re: MySQL accounting issue
Ah, ok, now I see the other post regarding this. The problem is OpenSER and this status type: rlm_sql (sql): Unsupported Acct-Status-Type = 15 Whats the latest on this? Has the incompatibility been addressed in freeradius 2.0? thanks Andy. Message sent using UK Grid Webmail 2.7.9 - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Re: MySQL accounting issue
Again, send a debug with the Start and Stop packets. Ivan Kalik Kalik Informatika ISP Dana 21/1/2008, A.smith [EMAIL PROTECTED] piše: Also, regarding radius 1.x I now have a patch which allows this type of record. However next issue is that with accounting set to sql in radiusd.conf the SQL statements are being written just to /usr/local/var/log/radius/sqltrace.sql and nothing is actually put in the database... What have I done wrong? :P cheers Andy. Message sent using UK Grid Webmail 2.7.9 - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Re: MySQL accounting issue
Anyone any ideas? I repost this as the posts are in a messed up order on the forum page... As per my previous mail, my current issue is that with accounting set to sql in radiusd.conf the SQL statements are being written just to /usr/local/var/log/radius/sqltrace.sql and nothing is actually put in the database... What have I done wrong? thank u! - Original Message - From: Andy Smith [EMAIL PROTECTED] To: FreeRadius users mailing list freeradius-users@lists.freeradius.org Sent: Monday, January 21, 2008 4:40 PM Subject: Re: MySQL accounting issue Ok, Im seeing this from radiusd: rad_recv: Accounting-Request packet from host 89.107.16.10:35563, id=252, length=145 Acct-Status-Type = Failed Service-Type = IAPP-Register Attr-102 = 0x0194 Error-Cause = 32 Event-Timestamp = Jan 21 2008 15:53:44 GMT Attr-105 = 0x61733264636462613764 Acct-Session-Id = [EMAIL PROTECTED] Attr-108 = 0x38392e3130372e31362e39 Attr-109 = 0x35303630 NAS-Port = 5060 Acct-Delay-Time = 0 NAS-IP-Address = 89.107.16.10 Processing the preacct section of radiusd.conf modcall: entering group preacct for request 0 modcall[preacct]: module preprocess returns noop for request 0 rlm_acct_unique: WARNING: Attribute User-Name was not found in request, unique ID MAY be inconsistent rlm_acct_unique: Hashing 'NAS-Port = 5060,Client-IP-Address = 89.107.16.10,NAS-IP-Address = 89.107.16.10,Acct-Session-Id = [EMAIL PROTECTED],' rlm_acct_unique: Acct-Unique-Session-ID = df36632bb92d5086. modcall[preacct]: module acct_unique returns ok for request 0 rlm_realm: Proxy reply, or no User-Name. Ignoring. modcall[preacct]: module suffix returns noop for request 0 modcall[preacct]: module files returns noop for request 0 modcall: leaving group preacct (returns ok) for request 0 Processing the accounting section of radiusd.conf modcall: entering group accounting for request 0 modcall[accounting]: module unix returns noop for request 0 radius_xlat: '/usr/local/var/log/radius/radutmp' rlm_radutmp: NAS OpenSER port 5060 unknown packet type 15) modcall[accounting]: module radutmp returns noop for request 0 radius_xlat: '' radius_xlat: '' radius_xlat: '/usr/local/var/log/radius/sqltrace.sql' rlm_sql (sql): Reserving sql socket id: 3 rlm_sql (sql): Released sql socket id: 3 modcall[accounting]: module sql returns ok for request 0 modcall: leaving group accounting (returns ok) for request 0 Sending Accounting-Response of id 252 to 89.107.16.10 port 35563 Finished request 0 Going to the next request --- Walking the entire request list --- Waking up in 6 seconds... --- Walking the entire request list --- Cleaning up request 0 ID 252 with timestamp 4794be17 Nothing to do. Sleeping until we see a request. - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Re: MySQL accounting issue
Dana 21/1/2008, A.smith [EMAIL PROTECTED] piše: Also, regarding radius 1.x I now have a patch which allows this type of record. However next issue is that with accounting set to sql in radiusd.conf the SQL statements are being written just to /usr/local/var/log/radius/sqltrace.sql and nothing is actually put in the database... What have I done wrong? :P cheers Andy. Message sent using UK Grid Webmail 2.7.9 - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Re: mysql accounting connect speeds
And the errors are?
Ivan Kalik
Kalik Informatika ISP
Dana 16/7/2007, Jeff [EMAIL PROTECTED] piše:
I need to log connect speeds from users
At any rate things working fine from our own carrier globalpops to capture
these on the start packet
but Yournetplus for some reason it doesn't work.
I see this info in the update accounting packet so i thought I would modify
the update query but It gives errors
anyone know why this is wrong.. it stops right at the AscendDataRate ='26400'
for example then nothing after
Trying to gather the Ascend-Data-Rate and USR-Connect-Speed
accounting_update_query = UPDATE ${acct_table1} \
SET FramedIPAddress = '%{Framed-IP-Address}', \
AcctSessionTime = '%{Acct-Session-Time}', \
AcctInputOctets = '%{Acct-Input-Octets}', \
AcctOutputOctets = '%{Acct-Output-Octets}' \
AscenDataRate = '%{Ascend-Xmit-Rate}' \
USRConnectSpeed = '%{USR-Connect-Speed}' \
WHERE AcctSessionId = '%{Acct-Session-Id}' \
AND UserName = '%{SQL-User-Name}' \
AND NASIPAddress= '%{NAS-IP-Address}'
-
List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Re: mysql accounting connect speeds
Mon Jul 16 11:23:22 2007 : Error: rlm_sql (sql): Couldn't update SQL accounting
ALIVE record - You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near
'AscendDataRate = '24000' USRConnectSpeed = '' WHERE
AcctSess' at line 1
Mon Jul 16 11:23:24 2007 : Error: rlm_sql (sql): Couldn't update SQL accounting
ALIVE record - You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near
'AscendDataRate = '19200' USRConnectSpeed = '' WHERE
AcctSess' at line 1
Mon Jul 16 11:23:37 2007 : Error: rlm_sql (sql): Couldn't update SQL accounting
ALIVE record - You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near
'AscendDataRate = '19200' USRConnectSpeed = '' WHERE
AcctSess' at line 1
Mon Jul 16 11:23:42 2007 : Error: rlm_sql (sql): Couldn't update SQL accounting
ALIVE record - You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near
'AscendDataRate = '19200' USRConnectSpeed = '' WHERE
AcctSess' at line 1
_
From: [EMAIL PROTECTED]
To: FreeRadius users mailing list [mailto:[EMAIL PROTECTED]
Sent: Mon, 16 Jul 2007 11:06:28 -0400
Subject: Re: mysql accounting connect speeds
And the errors are?
Ivan Kalik
Kalik Informatika ISP
Dana 16/7/2007, Jeff [EMAIL PROTECTED] piše:
I need to log connect speeds from users
At any rate things working fine from our own carrier globalpops to capture
these on the start packet
but Yournetplus for some reason it doesn't work.
I see this info in the update accounting packet so i thought I would modify
the update query but It gives errors
anyone know why this is wrong.. it stops right at the AscendDataRate ='26400'
for example then nothing after
Trying to gather the Ascend-Data-Rate and USR-Connect-Speed
accounting_update_query = UPDATE ${acct_table1} \
SET FramedIPAddress = '%{Framed-IP-Address}', \
AcctSessionTime = '%{Acct-Session-Time}', \
AcctInputOctets = '%{Acct-Input-Octets}', \
AcctOutputOctets = '%{Acct-Output-Octets}' \
AscenDataRate = '%{Ascend-Xmit-Rate}' \
USRConnectSpeed = '%{USR-Connect-Speed}' \
WHERE AcctSessionId = '%{Acct-Session-Id}' \
AND UserName = '%{SQL-User-Name}' \
AND NASIPAddress= '%{NAS-IP-Address}'
-
List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
-
List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
RE: mysql accounting connect speeds
Yes, and the AscendDataRate too.
I get the inserts fine on the start packet and the data goes right in as
suppose too.
all works fine this way for our GlobalPOPS and all data shows up and into sql
using this line in the start
---
accounting_start_query = INSERT into ${acct_table1} (AcctSessionId,
AcctUniqueId, UserName, Realm, NASIPAddress, NASPortId, NASPortType,
AcctStartTime, AcctStopTime, AcctSessionTime, AcctAuthentic, ConnectInfo_start,
ConnectInfo_stop, AcctInputOctets, AcctOutputOctets, CalledStationId,
CallingStationId, AcctTerminateCause, ServiceType, FramedProtocol,
FramedIPAddress, AcctStartDelay, AscendDataRate, USRConnectSpeed,
AcctStopDelay) values('%{Acct-Session-Id}', '%{Acct-Unique-Session-Id}',
'%{SQL-User-Name}', '%{Realm}', '%{NAS-IP-Address}', '%{NAS-Port}',
'%{NAS-Port-Type}', '%S', '0', '0', '%{Acct-Authentic}', '%{Connect-Info}', '',
'0', '0', '%{Called-Station-Id}', '%{Calling-Station-Id}', '',
'%{Service-Type}', '%{Framed-Protocol}', '%{Framed-IP-Address}',
'%{Acct-Delay-Time}', '%{Ascend-Xmit-Rate}', '%{USR-Connect-Speed}', '0')
---
data goes right into mysql tables
But YNP for some reason most miss the start, so I thought maybe I could grab
them on the update query cause i see one or the other on in update packet, for
ynp so then i would have what i need, but as i stated this errors out with the
error i mentioned below trying to do this
Jeff
_
From: Hugh Messenger [mailto:[EMAIL PROTECTED]
To: 'FreeRadius users mailing list' [mailto:[EMAIL PROTECTED]
Sent: Mon, 16 Jul 2007 11:40:53 -0400
Subject: RE: mysql accounting connect speeds
Jeff said:
USRConnectSpeed = '%{USR-Connect-Speed}' \
Did you actually add a USRConnectSpeed column to the radacct table? There
isn't one by default.
-- hugh
-
List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
-
List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Re: mysql accounting connect speeds
Jeff wrote: Mon Jul 16 11:23:22 2007 : Error: rlm_sql (sql): Couldn't update SQL accounting ALIVE record - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'AscendDataRate = '24000' USRConnectSpeed = '' WHERE AcctSess' at line 1 You need a comma between data items: 'AscendDataRate = '24000', USRConnectSpeed ='' WHERE AcctSess' ^^^ -- Dennis Skinner Systems Administrator BlueFrog Internet http://www.bluefrog.com - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Re: mysql accounting connect speeds
ok heres what i have now
accounting_update_query = UPDATE ${acct_table1} \
SET FramedIPAddress = '%{Framed-IP-Address}', \
AcctSessionTime = '%{Acct-Session-Time}', \
AcctInputOctets = '%{Acct-Input-Octets}', \
AcctOutputOctets = '%{Acct-Output-Octets}' \
AscendDataRate = '%{Ascend-Data-Rate}', \
USRConnectSpeed = '%{USR-Connect-Speed}' \
WHERE AcctSessionId = '%{Acct-Session-Id}' \
AND UserName = '%{SQL-User-Name}' \
AND NASIPAddress= '%{NAS-IP-Address}'
an heres the new error
Mon Jul 16 12:49:19 2007 : Error: rlm_sql (sql): Couldn't update SQL accounting
ALIVE record - You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near
'AscendDataRate = '19200', USRConnectSpeed = '' WHERE
AcctSes' at line 1
Mon Jul 16 12:49:35 2007 : Error: rlm_sql (sql): Couldn't update SQL accounting
ALIVE record - You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near
'AscendDataRate = '19200', USRConnectSpeed = '' WHERE
AcctSes' at line 1
Mon Jul 16 12:49:40 2007 : Error: rlm_sql (sql): Couldn't update SQL accounting
ALIVE record - You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near
'AscendDataRate = '19200', USRConnectSpeed = '' WHERE
AcctSes' at line 1
Mon Jul 16 12:49:59 2007 : Error: rlm_sql (sql): Couldn't update SQL accounting
ALIVE record - You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near
'AscendDataRate = '19200', USRConnectSpeed = '' WHERE
AcctSes' at line
_
From: Dennis Skinner [mailto:[EMAIL PROTECTED]
To: FreeRadius users mailing list [mailto:[EMAIL PROTECTED]
Sent: Mon, 16 Jul 2007 11:59:34 -0400
Subject: Re: mysql accounting connect speeds
Jeff wrote:
Mon Jul 16 11:23:22 2007 : Error: rlm_sql (sql): Couldn't update SQL
accounting ALIVE record - You have an error in your SQL syntax; check
the manual that corresponds to your MySQL server version for the right
syntax to use near 'AscendDataRate = '24000' USRConnectSpeed =
'' WHERE AcctSess' at line 1
You need a comma between data items:
'AscendDataRate = '24000', USRConnectSpeed ='' WHERE AcctSess'
^^^
--
Dennis Skinner
Systems Administrator
BlueFrog Internet
http://www.bluefrog.com
-
List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
-
List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Re: mysql accounting connect speeds
Yes. You are missing commas before AscendDataRate and USRConnectSpeed expressions that you have added to the update query. Ivan Kalik Kalik Informatika ISP Dana 16/7/2007, Dennis Skinner [EMAIL PROTECTED] piše: Jeff wrote: Mon Jul 16 11:23:22 2007 : Error: rlm_sql (sql): Couldn't update SQL accounting ALIVE record - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'AscendDataRate = '24000' USRConnectSpeed = '' WHERE AcctSess' at line 1 You need a comma between data items: 'AscendDataRate = '24000', USRConnectSpeed ='' WHERE AcctSess' ^^^ -- Dennis Skinner Systems Administrator BlueFrog Internet http://www.bluefrog.com - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Re: mysql accounting connect speeds
Jeff wrote:
AcctOutputOctets = '%{Acct-Output-Octets}' \
Need comma on live above. This is a MySQL issue, not a FR issue.
Please read the MySQL docs if you don't understand how to create a valid
query.
--
Dennis Skinner
Systems Administrator
BlueFrog Internet
http://www.bluefrog.com
-
List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Re: mysql accounting connect speeds
its not i do not understand its just these stupid bi-focals i have a hard time
seeing.
I overlooked that, sorry for being a blind idiot
_
From: Dennis Skinner [mailto:[EMAIL PROTECTED]
To: FreeRadius users mailing list [mailto:[EMAIL PROTECTED]
Sent: Mon, 16 Jul 2007 13:54:02 -0400
Subject: Re: mysql accounting connect speeds
Jeff wrote:
AcctOutputOctets = '%{Acct-Output-Octets}' \
Need comma on live above. This is a MySQL issue, not a FR issue.
Please read the MySQL docs if you don't understand how to create a valid
query.
--
Dennis Skinner
Systems Administrator
BlueFrog Internet
http://www.bluefrog.com
-
List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
-
List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Re: mysql accounting
Seferovic Edvin wrote: I do NOT want to be rude, but sometimes searching the archives helps A LOT ! BELIEVE ME ! But for the lazy developers among you people - here is the part that describes the needed feature. Thanks to Jamal ( of course ). This is copy paste - so do NOT blame me ;) You're right, but unfortunately you didn't pick up the easiest method. This question has been asked so many many times on the mailing list that now it's in the FAQ. (but it appears people are too lazy to read the FAQ) http://wiki.freeradius.org/index.php/FreeRADIUS_Wiki:FAQ Nicolas Baradakis -- A: Yes. Q: Are you sure? A: Because it reverses the logical flow of conversation. Q: Why is top posting annoying in email? - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Re: mysql accounting
Thanks for that, I cant believe I didnt find it. I scoured the wiki. I seriously don't like to ask for help unless I just cant find it anywhere. Anyways, I'm working on a pretty neat php5 driven interface for this whole thing that I would be happy to share after it's finished if there would be an interest in that sort of thing. On Wed, 2006-06-14 at 12:06 +0200, Nicolas Baradakis wrote: Seferovic Edvin wrote: I do NOT want to be rude, but sometimes searching the archives helps A LOT ! BELIEVE ME ! But for the lazy developers among you people - here is the part that describes the needed feature. Thanks to Jamal ( of course ). This is copy paste - so do NOT blame me ;) You're right, but unfortunately you didn't pick up the easiest method. This question has been asked so many many times on the mailing list that now it's in the FAQ. (but it appears people are too lazy to read the FAQ) http://wiki.freeradius.org/index.php/FreeRADIUS_Wiki:FAQ Nicolas Baradakis - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
RE: mysql accounting
Hello,
I do NOT want to be rude, but sometimes searching the archives helps A LOT !
BELIEVE ME ! But for the lazy developers among you people - here is the part
that describes the needed feature. Thanks to Jamal ( of course ). This is
copy paste - so do NOT blame me ;)
START
Create a table in the radius schema (called fails_log) to include three
columns: trial_date, username, password.
Create a function in the database (called fails). The main statements
which you should write are
fails ( username1 in out char, password1 in char) return char is
v_user char:='';
v_password:='';
begin
select username , value into v_user from radcheck where
attribute='password' and username= username1 and password=password1;
if v_user = '' then insert into fails_log values
(sysdate,username1,password1);
else return v_user;
end if;
end;
Update authorize_ceck_query module in sql.conf file to be as follows:
authorize_check_query = SELECT id,Username,Attribute,Value,op FROM
${authcheck_table} WHERE Username =(select
fails('%{SQL-User-Name}','%{User-Password}') from dual) ORDER BY id
That is all. Then you can find all failed logs inside the new created table
fails_log.
END
Regards,
Edvin Seferovic
-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED]
g] On Behalf Of Sean Taylor
Sent: Donnerstag, 15. Juni 2006 07:13
To: freeradius-users@lists.freeradius.org
Subject: mysql accounting
I have a quick question on the mysql accounting. I am working on my own
interface for managing the freeradius+mysql setup. Everything is
working great, I can view all my users, see who's connected, add new
users, manage static vs. dynamic IP's, etc..The problem is it doesnt
seem to log authentication failures into the radacct table. It logs all
the successes just fine, but it would be very beneficial to have it log
the failures too. I have the sql module turned on in the accounting
section of the config and have uncommented all of the accounting
queries. Any help appreciated.
Thanks
Sean Taylor
Systems Administrator
Valutel Communications
-
List info/subscribe/unsubscribe? See
http://www.freeradius.org/list/users.html
-
List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Re: mysql accounting
I actually did search the archives quite a bit, just not for the right
thing apparently. I usually won't post to a list until I've exhausted
all other options. Anyways, has someone actually turned this giberish
into working SQL code they are willing to share?
Thanks
Seferovic Edvin wrote:
Hello,
I do NOT want to be rude, but sometimes searching the archives helps A LOT !
BELIEVE ME ! But for the lazy developers among you people - here is the part
that describes the needed feature. Thanks to Jamal ( of course ). This is
copy paste - so do NOT blame me ;)
START
Create a table in the radius schema (called fails_log) to include three
columns: trial_date, username, password.
Create a function in the database (called fails). The main statements
which you should write are
fails ( username1 in out char, password1 in char) return char is
v_user char:='';
v_password:='';
begin
select username , value into v_user from radcheck where
attribute='password' and username= username1 and password=password1;
if v_user = '' then insert into fails_log values
(sysdate,username1,password1);
else return v_user;
end if;
end;
Update authorize_ceck_query module in sql.conf file to be as follows:
authorize_check_query = SELECT id,Username,Attribute,Value,op FROM
${authcheck_table} WHERE Username =(select
fails('%{SQL-User-Name}','%{User-Password}') from dual) ORDER BY id
That is all. Then you can find all failed logs inside the new created table
fails_log.
END
Regards,
Edvin Seferovic
-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED]
g] On Behalf Of Sean Taylor
Sent: Donnerstag, 15. Juni 2006 07:13
To: freeradius-users@lists.freeradius.org
Subject: mysql accounting
I have a quick question on the mysql accounting. I am working on my own
interface for managing the freeradius+mysql setup. Everything is
working great, I can view all my users, see who's connected, add new
users, manage static vs. dynamic IP's, etc..The problem is it doesnt
seem to log authentication failures into the radacct table. It logs all
the successes just fine, but it would be very beneficial to have it log
the failures too. I have the sql module turned on in the accounting
section of the config and have uncommented all of the accounting
queries. Any help appreciated.
Thanks
Sean Taylor
Systems Administrator
Valutel Communications
-
List info/subscribe/unsubscribe? See
http://www.freeradius.org/list/users.html
-
List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
-
List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Re: mysql Accounting not working
on the sql.conf add sqltrace = yes
start up with radiusd -X and see what happen.
You test the mysql conneciotn from the SAME host that freeradius?
On 7/14/05, Leonardo Valente [EMAIL PROTECTED] wrote:
Hello,
I use Debian Linux Sarge, kernel 2.6.8-2(368),
freeradius 1.0.2, and I'm trying to configure
freeradius + mysql Accounting.
I created database from script db_mysql.sql, and
created a user to access database with full
privileges. I tested to access database from another
host and it's fine.
I can get authentication from localhost and another
host. BUT freeradius is not insert accounting
information in database.
I used freeradius -X to get some debug information
and I can't see it doing INSERT. But I know that
freeradius connect into database when I start the
daemon, I could see that in mysql.log.
I looked into mysql.log and freeradius is not doing
INSERT.
Database name, database username, password and host
are set in sql.conf. And accounting tag from
radiusd.conf is:
---
accounting {
sql
}
Someone can tell me if I forgot some configuration? I
just wanna do Mysql Accounting...
Thanks in advance
-
Dize-me tuas comunidades e te direi quem és...
Leonardo Valente
MSN: [EMAIL PROTECTED]
__
Converse com seus amigos em tempo real com o Yahoo! Messenger
http://br.download.yahoo.com/messenger/
-
List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
-
List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Re: mysql Accounting not working
in my sql.conf:
-
sqltrace = yes
sqltracefile = ${logdir}/sqltrace.sql
-
freeradius user has privileges to write in ${logdir},
but this file not even is created when I do
freeradius -X or freeradius -x.
Yes, I tested mysql connection from SAME host that
free radius. Like I said, I can see in mysql.log
freeradius connected 5 times, when I start up daemon,
but I can't see INSERT TO...
Can debian freeradius package not be compiled with
mysql account support?
I don't have ideas anymore... I think I'll try compile
from source code... and test...
Thanks
--- Mario Alberto Cruz Gartner [EMAIL PROTECTED]
escreveu:
on the sql.conf add sqltrace = yes
start up with radiusd -X and see what happen.
You test the mysql conneciotn from the SAME host
that freeradius?
On 7/14/05, Leonardo Valente
[EMAIL PROTECTED] wrote:
Hello,
I use Debian Linux Sarge, kernel 2.6.8-2(368),
freeradius 1.0.2, and I'm trying to configure
freeradius + mysql Accounting.
I created database from script db_mysql.sql, and
created a user to access database with full
privileges. I tested to access database from
another
host and it's fine.
I can get authentication from localhost and
another
host. BUT freeradius is not insert accounting
information in database.
I used freeradius -X to get some debug
information
and I can't see it doing INSERT. But I know that
freeradius connect into database when I start the
daemon, I could see that in mysql.log.
I looked into mysql.log and freeradius is not
doing
INSERT.
Database name, database username, password and
host
are set in sql.conf. And accounting tag from
radiusd.conf is:
---
accounting {
sql
}
Someone can tell me if I forgot some
configuration? I
just wanna do Mysql Accounting...
Thanks in advance
-
Dize-me tuas comunidades e te direi quem és...
Leonardo Valente
MSN: [EMAIL PROTECTED]
__
Converse com seus amigos em tempo real com o
Yahoo! Messenger
http://br.download.yahoo.com/messenger/
-
List info/subscribe/unsubscribe? See
http://www.freeradius.org/list/users.html
-
List info/subscribe/unsubscribe? See
http://www.freeradius.org/list/users.html
-
Dize-me tuas comunidades e te direi quem és...
Leonardo Valente
MSN: [EMAIL PROTECTED]
___
Yahoo! Acesso Grátis - Internet rápida e grátis.
Instale o discador agora! http://br.acesso.yahoo.com/
-
List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Re: MySQL Accounting in Freeradius
Andrey [EMAIL PROTECTED] wrote: I have a question about the uniqueness of the AcctUniqueId field. Everytime the user connects, the id is always the same. AcctSessionId is always the same as well. Then there isn't much you can do. Some NAS vendors re-use Acct-Session-Id's, even though the RFC's say the Id's should be unique. In order to work around this, we wrote the acct_unique module, which takes *additional* data, and tries to create a more unique Id. If the AcctUniqueId field is always the same, then the accounting request contain all the same information, or are missing some information. See the acct_unique configuration for a list of attributes it's using. For some reason I doubt it, but thought I'd ask just to make sure. If that's a bug, how would I go about fixing it? Get your NAS to send real accounting data. Barring that, there's nothing you can do. As a related question: Pretend you're the RADIUS server, looking at two or more accounting requests. How can you tell different sessions apart of the Acct-Session-Id is the same, and all other information is the same? If you can't tell the difference, neither can FreeRADIUS. Alan DeKok. - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Re: MYSQL Accounting Table Size?
cris boisvert escreveu: My Mysql database is about 50 megs right now.. because of the accounting table. How large does most people let it get before rolling it? My radacct table is over 500 MB / 1.3 million records right now. For now I'm just letting it grow. Make sure you have plenty of RAM though. If not, the database will become slow to query / insert and radius will stop dropping packets. Keith Yoder - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
RE: MYSQL Accounting Table Size?
I got 4 gigs of ram.. I hope its enough.. -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Keith Yoder Sent: Monday, October 04, 2004 3:52 PM To: [EMAIL PROTECTED] Subject: Re: MYSQL Accounting Table Size? cris boisvert escreveu: My Mysql database is about 50 megs right now.. because of the accounting table. How large does most people let it get before rolling it? My radacct table is over 500 MB / 1.3 million records right now. For now I'm just letting it grow. Make sure you have plenty of RAM though. If not, the database will become slow to query / insert and radius will stop dropping packets. Keith Yoder - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html --- Incoming mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.768 / Virus Database: 515 - Release Date: 9/22/2004 --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.768 / Virus Database: 515 - Release Date: 9/22/2004 - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Re: MYSQL Accounting Table Size?
cris boisvert escreveu: I got 4 gigs of ram.. I hope its enough.. I've only got 1 so you should be fine. - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Re: MYSQL Accounting Table Size?
On Mon, 4 Oct 2004, cris boisvert wrote: My Mysql database is about 50 megs right now.. because of the accounting table. How large does most people let it get before rolling it? I Guess other people will just roll it on a Time/Date basis in cron.. But what if I want to keep it for a year so I can pull stats out of it? Can it get to a couple of gig without problems? What is everyone else doing? I 've got a 2.4GB innodb file with no problems. Though i would strongly recommend against maintaining full accounting for a year on your main radacct table. You could move old accounting (a few months old) to a different radacct table if you want, or you could just keep aggregated accounting on a different table for statistical purposes. The reason is that the larger your table, the more your indexes wont work correctly (a lot of rows for the same username, sessionid etc). -- Kostas Kalevras Network Operations Center [EMAIL PROTECTED] National Technical University of Athens, Greece Work Phone: +30 210 7721861 'Go back to the shadow' Gandalf - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Re: mysql accounting
On Wed, 28 Jul 2004, Ken A wrote: Edgars wrote: i am writing my own program to get them in human-readable form:) Edgars Yep. I made some changes that make it easier for me to start from scratch with a language I'm more familiar with (perl) than to modify dialupadmin to do what I want, especially since I'm not very good with php, and there are many things in dialupadmin I would want to change. What do you mean by that? I added a couple of columns to the radacct table, so my records include several Ascend attributes not in the standard table: (Ascend-Disconnect-Cause, Ascend-XmitRate, Ascend-DataRate). And, I was getting duplicate STOP records in the radacct table, so I also put a unique index on (sessionid,username,nasipaddress) and changed the INSERT STOP record in sql.conf to a REPLACE INTO instead of INSERT INTO and that seems to have resolved the problem. Ken A Ken A wrote: Those of you that use mysql with freeradius, can anyone recommend some software for linux to process mysql radacct table logs? Do you just roll your own scripts to query the logs and make reports? Seems simple enough, but what are others doing? is always a good question :-) Thanks, Ken A - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html -- Kostas Kalevras Network Operations Center [EMAIL PROTECTED] National Technical University of Athens, Greece Work Phone: +30 210 7721861 'Go back to the shadow' Gandalf - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Re: mysql accounting
On Wed, 28 Jul 2004, Edgars wrote: i am writing my own program to get them in human-readable form:) Edgars Ken A wrote: Those of you that use mysql with freeradius, can anyone recommend some software for linux to process mysql radacct table logs? dialupadmin has a user statistics and a statistics page. It also has tot_stats and monthly_tot_stats to aggregate user accounting to per day or per month tables for easier statistics creation. Try starting from there. Do you just roll your own scripts to query the logs and make reports? Seems simple enough, but what are others doing? is always a good question :-) Thanks, Ken A - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html -- Kostas Kalevras Network Operations Center [EMAIL PROTECTED] National Technical University of Athens, Greece Work Phone: +30 210 7721861 'Go back to the shadow' Gandalf - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Re: mysql accounting
Kostas Kalevras wrote: On Wed, 28 Jul 2004, Ken A wrote: Edgars wrote: i am writing my own program to get them in human-readable form:) Edgars Yep. I made some changes that make it easier for me to start from scratch with a language I'm more familiar with (perl) than to modify dialupadmin to do what I want, especially since I'm not very good with php, and there are many things in dialupadmin I would want to change. What do you mean by that? Sorry, that wasn't meant to suggest that there's anything wrong with dialupadmin. It's just overkill here. I don't do php, and my application is for support people who don't need much of the functionality of dialupadmin. I just need to lookup radacct records by UserName or IP, and display the accounting records for that user or ip, and be able to sort on any column quickly. ~150 lines of perl did it. Ken A I added a couple of columns to the radacct table, so my records include several Ascend attributes not in the standard table: (Ascend-Disconnect-Cause, Ascend-XmitRate, Ascend-DataRate). And, I was getting duplicate STOP records in the radacct table, so I also put a unique index on (sessionid,username,nasipaddress) and changed the INSERT STOP record in sql.conf to a REPLACE INTO instead of INSERT INTO and that seems to have resolved the problem. Ken A Ken A wrote: Those of you that use mysql with freeradius, can anyone recommend some software for linux to process mysql radacct table logs? Do you just roll your own scripts to query the logs and make reports? Seems simple enough, but what are others doing? is always a good question :-) Thanks, Ken A - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html -- Kostas Kalevras Network Operations Center [EMAIL PROTECTED] National Technical University of Athens, Greece Work Phone: +30 210 7721861 'Go back to the shadow' Gandalf - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Re: mysql accounting
i am writing my own program to get them in human-readable form:) Edgars Ken A wrote: Those of you that use mysql with freeradius, can anyone recommend some software for linux to process mysql radacct table logs? Do you just roll your own scripts to query the logs and make reports? Seems simple enough, but what are others doing? is always a good question :-) Thanks, Ken A - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Re: mysql accounting
Edgars wrote: i am writing my own program to get them in human-readable form:) Edgars Yep. I made some changes that make it easier for me to start from scratch with a language I'm more familiar with (perl) than to modify dialupadmin to do what I want, especially since I'm not very good with php, and there are many things in dialupadmin I would want to change. I added a couple of columns to the radacct table, so my records include several Ascend attributes not in the standard table: (Ascend-Disconnect-Cause, Ascend-XmitRate, Ascend-DataRate). And, I was getting duplicate STOP records in the radacct table, so I also put a unique index on (sessionid,username,nasipaddress) and changed the INSERT STOP record in sql.conf to a REPLACE INTO instead of INSERT INTO and that seems to have resolved the problem. Ken A Ken A wrote: Those of you that use mysql with freeradius, can anyone recommend some software for linux to process mysql radacct table logs? Do you just roll your own scripts to query the logs and make reports? Seems simple enough, but what are others doing? is always a good question :-) Thanks, Ken A - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Re: mysql accounting
Those of you that use mysql with freeradius, can anyone recommend some software for linux to process mysql radacct table logs? Do you just roll your own scripts to query the logs and make reports? Seems simple enough, but what are others doing? is always a good question :-) if dialup_admin is too technical for you, you can decide to create your own customized PHP/Perl scripts to do whatever output you like. //milver - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Re: mysql accounting
duh! I didn't know it was there. Thanks. Ken A Milver S. Nisay wrote: Those of you that use mysql with freeradius, can anyone recommend some software for linux to process mysql radacct table logs? Do you just roll your own scripts to query the logs and make reports? Seems simple enough, but what are others doing? is always a good question :-) if dialup_admin is too technical for you, you can decide to create your own customized PHP/Perl scripts to do whatever output you like. //milver - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Re: MySQL accounting strangeness
Normally this could depend on your NAS configuration. Check this is sending the attributes you need. Untitled DocumentErnesto Freyre Ramírez Área de Operaciones Red Privada Virtual S.A. Av. Paseo de la República 4675 - Lima 34 Telf.: (511) 241-4122 Anexo 2245 Fax: (511) 446-8135 Visítenos en: www.qnet.com.pe - Original Message - From: Andre Fortin [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Monday, July 05, 2004 12:26 PM Subject: MySQL accounting strangeness Hello, Forgive me if this has been covered. I'm using FreeRADIUS 0.8.1 and am using MySQL for accounting (and LDAP for authorization, but that's probably not important). This works well for getting totals of time used for each user. However, I'm now trying to write a tool to search which username was logged on at X time, and noticed some missing information in the radacct table; It seems that when a session is started, its not entering the FramedIPAddress. Strangely, if the user was logged in when accounting wasn't happening, and the session ends, it records a start time of all zeros, and the AcctStopTime, with the IP address. If the system has both the AcctStartTime and StopTime, there is no IP address.. Here's a snippet of results from my database: ++-+-+-- ---+ | UserName | FramedIPAddress | AcctStartTime | AcctStopTime| ++-+-+-- ---+ | Xuser | 66.206.230.5| -00-00 00:00:00 | 2004-04-29 11:57:27 | | Xuser | | 2004-05-03 23:33:25 | 2004-05-03 23:44:09 | All accounts are exhibiting this behavior; very few actually have a recorded IP address, only the ones without a valid start time.. Any ideas? If you need any more information, let me know.. I haven't yet tried upgrading, as I'm not sure if it will fix it, and I don't want to accidently cause any other problems by changing the version. Thanks in advance, Andre - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
RE: MySQL accounting strangeness
The NAS is apparently sending all the information (according to the network guys here). It is getting the FramedIPAddress for sessions without a start time, so it's apparently sending it.. It just doesn't put it into mysql when theres a start time.. Andre -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Behalf Of Ernesto Freyre Sent: Monday, July 05, 2004 5:27 PM To: [EMAIL PROTECTED] Subject: Re: MySQL accounting strangeness Normally this could depend on your NAS configuration. Check this is sending the attributes you need. Untitled DocumentErnesto Freyre Ramírez Área de Operaciones Red Privada Virtual S.A. Av. Paseo de la República 4675 - Lima 34 Telf.: (511) 241-4122 Anexo 2245 Fax: (511) 446-8135 Visítenos en: www.qnet.com.pe - Original Message - From: Andre Fortin [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Monday, July 05, 2004 12:26 PM Subject: MySQL accounting strangeness Hello, Forgive me if this has been covered. I'm using FreeRADIUS 0.8.1 and am using MySQL for accounting (and LDAP for authorization, but that's probably not important). This works well for getting totals of time used for each user. However, I'm now trying to write a tool to search which username was logged on at X time, and noticed some missing information in the radacct table; It seems that when a session is started, its not entering the FramedIPAddress. Strangely, if the user was logged in when accounting wasn't happening, and the session ends, it records a start time of all zeros, and the AcctStopTime, with the IP address. If the system has both the AcctStartTime and StopTime, there is no IP address.. Here's a snippet of results from my database: ++-+-- ---+-- ---+ | UserName | FramedIPAddress | AcctStartTime | AcctStopTime| ++-+-- ---+-- ---+ | Xuser | 66.206.230.5| -00-00 00:00:00 | 2004-04-29 11:57:27 | | Xuser | | 2004-05-03 23:33:25 | 2004-05-03 23:44:09 | All accounts are exhibiting this behavior; very few actually have a recorded IP address, only the ones without a valid start time.. Any ideas? If you need any more information, let me know.. I haven't yet tried upgrading, as I'm not sure if it will fix it, and I don't want to accidently cause any other problems by changing the version. Thanks in advance, Andre - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Re: MySQL accounting and Cisco-AVPair
Le lun 22/03/2004 à 11:47, Pugnaloni Federico a écrit :
Hi,
i'm using FreeRADIUS Version 0.9.3on FreeBSD 4.9
i'm using with a Cisco PIX to AAA internet access
it works fine, but i need to store the Cisco-AVPair info in radacct SQL
table.
As i can see in the detail accounting freeradius store Cisco-AVPair info
-snip-
Cisco-AVPair = ip:source-ip=192.168.0.127
Cisco-AVPair = ip:source-port=4051
Cisco-AVPair = ip:destination-ip=10.10.10.1
Cisco-AVPair = ip:destination-port=23
-snip
but i cannot store this info on sql
I've tried to modify sql.conf as is:
accounting_stop_query_alt = INSERT into ${acct_table2} (RadAcctId,
AcctSessionId... AcctStopDelay) values('', '%{Acct-Session-Id}',
'%{Acct-Unique-Session-Id}', '%{SQL-User-Name}', '%{Realm}',
'%{NAS-IP-Address}', '%{NAS-Port}'... '%{Cisco-AVPair}',
'%{Cisco-AVPair}'..}')
but it returns only the first instance of Cisco-AVPair
(ip:source-ip=192.168.0.127)
how can i store all the values?
Does the following help you?
http://www.freeradius.org/cgi-bin/cvsweb.cgi/~checkout~/radiusd/src/billing/README?rev=1.5content-type=text/plain
--
Federico Pugnaloni
--
Jérôme Warnier
Consultant
BeezNest
http://beeznest.net
-
List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
RE: mysql accounting question
hi This error indicates of a slow MYSQL machine Do you have a big radacct table? Cheers ~~ Mustafa N. Deeb Technical Director Palnet Communications Ltd. Tel: +970-2-2403434 Fax: +970-2-2403430 www.palsms.com www.paltime.net www.palnet.com -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Fogarasi Andras Sent: Sunday, January 11, 2004 2:08 PM To: [EMAIL PROTECTED] Subject: mysql accounting question Hi! I see messages like this in radius.log: Sun Jan 11 13:00:06 2004 : Info: rlm_sql (sql): There are no DB handles to use! skipped 0, tried to connect 0 When it happens, the accounting will be continous or this message indicates lost data? Thanks, Andras -- - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
