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
Mysql, Accounting and DialupAdmin
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 2) I could see login and logout information, but no data usage, e.g. dowload and upload sizes appear to be zeroes. mysql select username,acctstarttime,acctstoptime,acctoutputoctets,acctoutputoctets from radacct; +--+-+-+--+--+ | username | acctstarttime | acctstoptime| acctoutputoctets | acctoutputoctets | +--+-+-+--+--+ | test | 2012-11-07 15:09:47 | 2012-11-07 15:15:48 | 0 |0 | | test | 2012-11-07 15:15:48 | 2012-11-07 15:25:02 | 0 |0 | | test | 2012-11-07 15:25:32 | 2012-11-07 15:41:52 | 0 |0 | +--+-+-+--+--+ Thanks for hints Erich Titl 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 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
MySQL accounting issue
Hi List, ok next issue I have :P 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 I can see from the MySQL log that radiusd is successfully establishing a connection to MySQL at startup: 080120 19:14:49 61 Connect[EMAIL PROTECTED] on radius 62 Connect [EMAIL PROTECTED] on radius 63 Connect [EMAIL PROTECTED] on radius 64 Connect [EMAIL PROTECTED] on radius 65 Connect [EMAIL PROTECTED] on radius 65 Query SELECT * FROM nas 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?? thanks Andy.- List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Re: issue with mysql accounting
Jan Satko wrote: So it looks like (for me) that AP is sending outer information for accounting. Maybe there is some option howto force AP to show inner username ? Send the inner user name back in the Access-Accept. Set use_tunneled_reply, and it should work. I noticed that TTLS has some options in eap.conf about tunneled-reply or variables. But i have dozen of users(usually students) which have only XP/Vista with PEAP plugin. Cannot force them to install TTLS (if TTLS will works). Peap also has use_tunneled_reply. Alan DeKok. - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Re: issue with mysql accounting
Peap also has use_tunneled_reply. Alan DeKok. Tyvm. It is working. I'm still using old eap.conf from 2 years ago and this option was before only in TTLS section ;-) S pozdravom -- Bc. Jan 'EIS' Satko Slovak University of Agriculture network system managerTr. A. Hlinku 2 Tel: +421 37 7412 616 949 76 Nitra Slovakia - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
mysql accounting connect speeds
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
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
problem with mysql accounting
hi i try to account with mysql here are the mysql start stop and update definition accounting_start_query = INSERT into radaact SET\ `User-Name` = '%{User-Name}',\ `Calling-Station-Id` = '%{Calling-Station-Id}',\ `Called-Station-Id` = '%{Called-Station-Id}',\ `NAS-IP-Address` = '%{NAS-IP-Address}',\ `NAS-Port` = %{NAS-Port},\ `Timestamp Start` = NOW(),\ `Acct-Unique-Session-Id` = '%{Acct-Unique-Session-Id}' accounting_update_query = UPDATE radaact SET\ `Acct-Session-Time` = '%{Acct-Session-Time}',\ `Acct-Input-Octets` = '%{Acct-Input-Octets}',\ `Acct-Output-Octets` = '%{Acct-Output-Octets}',\ `Acct-Input-Packets` = '%{Acct-Input-Packets}',\ `Acct-Output-Packets` = '%{Acct-Output-Packets}'\ WHERE `Acct-Unique-Session-Id` = '%{Acct-Unique-Session-Id}'\ LIMIT 1 accounting_stop_query = UPDATE radaact SET\ `Timestamp Stop` = NOW(),\ `Acct-Session-Time` = '%{Acct-Session-Time}',\ `Acct-Input-Octets` = '%{Acct-Input-Octets}',\ `Acct-Output-Octets` = '%{Acct-Output-Octets}',\ `Acct-Input-Packets` = '%{Acct-Input-Packets}',\ `Acct-Output-Packets` = '%{Acct-Output-Packets}',\ `Acct-Terminate-Cause` = '%{Acct-Terminate-Cause}'\ WHERE `Acct-Unique-Session-Id` = '%{Acct-Unique-Session-Id}'\ LIMIT 1 update works fine rlm_sql (sql): sql_set_user escaped user -- '' radius_xlat: 'UPDATE radaact SET??`Acct-Session-Time` = '292',??`Acct-Input-Octets` = '94237',??`Acct-Output-Octets` = '937628',??`Acct-Input-Packets` = '597',??`Acct-Output-Packets` = '816'?WHERE `Acct-Unique-Session-Id` = '814b38bc0e9c60f4'?LIMIT 1 ' rlm_sql (sql): Reserving sql socket id: 2 radius_xlat: '' rlm_sql (sql): Released sql socket id: 2 modcall[accounting]: module sql returns ok for request 61 modcall: leaving group accounting (returns ok) for request 61 but start and stop not rlm_sql (sql): sql_set_user escaped user -- '' radius_xlat: 'INSERT into radaact SET ' rlm_sql (sql): Reserving sql socket id: 4 rlm_sql_mysql: MYSQL check_error: 1064 received rlm_sql (sql): Couldn't insert SQL accounting START 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 '' at line 1 radius_xlat: '' rlm_sql (sql): Released sql socket id: 4 modcall[accounting]: module sql returns ok for request 59 modcall: leaving group accounting (returns ok) for request 59 rlm_sql (sql): sql_set_user escaped user -- '' radius_xlat: 'UPDATE radaact SET ' rlm_sql (sql): Reserving sql socket id: 3 rlm_sql_mysql: MYSQL check_error: 1064 received rlm_sql (sql): Couldn't update SQL accounting STOP 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 '' at line 1 rlm_sql (sql): Released sql socket id: 3 modcall[accounting]: module sql returns fail for request 60 modcall: leaving group accounting (returns fail) for request 60 i use mysql 4.1 , freeradius 1.1 thanks for help basile - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Re: problem with mysql accounting
On Thursday 21 December 2006 06:45, basile wrote: but start and stop not rlm_sql (sql): sql_set_user escaped user -- '' radius_xlat: 'INSERT into radaact SET ' rlm_sql (sql): Reserving sql socket id: 4 rlm_sql_mysql: MYSQL check_error: 1064 received When you run in debug mode, do you see the full query when the config options are printed? If not, there might be an escaping problem in your sql.conf file. freeradius 1.1 What freeradius version is that? 1.1.3 is the latest release. Kevin Bonner pgppSBvWYbfco.pgp Description: PGP signature - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Re: problem with mysql accounting
basile wrote: accounting_start_query = INSERT into radaact SET\ `User-Name` = '%{User-Name}',\ `Calling-Station-Id` = '%{Calling-Station-Id}',\ `Called-Station-Id` = '%{Called-Station-Id}',\ `NAS-IP-Address` = '%{NAS-IP-Address}',\ `NAS-Port` = %{NAS-Port},\ `Timestamp Start` = NOW(),\ `Acct-Unique-Session-Id` = '%{Acct-Unique-ession-Id}' Any particular reason you are using backticks (`) instead of single quotes (') around the column names? I'm not sure if they have special meanings in radius/mysql. You should also ensure that the Acct-Unique-Session-Id is truly unique when you UPDATE. Often NAS's will reuse supposedly unique id's. Esp if they are rebooted. Looking for a Timestamp Start within X hours of NOW() may help you out there. Otherwise you may update really old rows and have sessions that look like they lasted 30 days or something. We ran into this problem. -- Dennis Skinner Systems Administrator BlueFrog Internet http://www.bluefrog.com - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
FreeRADIUS + LDAP Authentication/Authorization + MySQL Accounting
Hello, I have been researching the use of FreeRADIUS on my network for the past few days. I'm not sure if FreeRADIUS can do what I want. Here is a list of my requirements: -Authentication through LDAP -Authorization through LDAP -Accounting through MySQL I have multiple Cisco and Foundry devices on my network. The RADIUS server will primarily be used for AAA for Telnet/SSH logins and eventually VPN dialin accounts. Is FreeRADIUS the software I should use? Thank you, Dan - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
RE: FreeRADIUS + LDAP Authentication/Authorization + MySQL Accounting
-Authentication through LDAP YES. Using it currently ! -Authorization through LDAP YES. See above :) -Accounting through MySQL YES. Doing traffic accounting. I have multiple Cisco and Foundry devices on my network. The RADIUS server will primarily be used for AAA for Telnet/SSH logins and eventually VPN dialin accounts. Is FreeRADIUS the software I should use? RADIUS provides AAA features and freeRADIUS is just one hell of a software :) I am using ProCurve with RADIUS support and I didnt have any troubles setting it up. ProCurve is based ( IMHO ) on Cisco software ( not 100% ) so you should be able to do whatever you need. Regards, Edvin Seferovic - 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
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
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
MySQL accounting stop query
I noticed that the stop query changed at some point between 0.9.3 and 1.0.1. Here are the queries: ## 0.9.3 ## accounting_stop_query = UPDATE ${acct_table2} SET AcctStopTime = '%S', AcctSessionTime = '%{Acct-Session-Time}', AcctInputOctets = '%{Acct-Input-Octets}', AcctOutputOctets = '%{Acct-Output-Octets}', AcctTerminateCause = '%{Acct-Terminate-Cause}', AcctStopDelay = '%{Acct-Delay-Time}', ConnectInfo_stop = '%{Connect-Info}' WHERE AcctSessionId = '%{Acct-Session-Id}' AND UserName = '%{SQL-User-Name}' AND NASIPAddress = '%{NAS-IP-Address}' AND AcctStopTime = 0 ## 1.0.1 (same as 1.0.5) ## accounting_stop_query = UPDATE ${acct_table2} SET AcctStopTime = '%S', AcctSessionTime = '%{Acct-Session-Time}', AcctInputOctets = '%{Acct-Input-Octets}', AcctOutputOctets = '%{Acct-Output-Octets}', AcctTerminateCause = '%{Acct-Terminate-Cause}', AcctStopDelay = '%{Acct-Delay-Time}', ConnectInfo_stop = '%{Connect-Info}' WHERE AcctSessionId = '%{Acct-Session-Id}' AND UserName = '%{SQL-User-Name}' AND NASIPAddress = '%{NAS-IP-Address}' Notice the last item in the WHERE clause is missing in the later version. After looking at the data in my own radacct table, it looks like the change would cut down on duplicates, but at the cost of modifying rows that are potentially days or weeks old with new stoptime data. If the NAS reuses SessionID for the same user 2 weeks after the initial use, then the user will end up with a 2 week long session and if sqlcounter is in use, will likely be shutoff. It seems the 0.9.3 version is safer. I don't see specific mention of the change in the ChangeLog. Was there a reason for the change? I am working out the details of a modification of my own. At this point the WHERE clause looks like this (untested): WHERE AcctUniqueId = '%{Acct-Unique-Session-Id}' AND ABS((UNIXTIMESTAMP(AcctStartTime) - UNIXTIMESTAMP(DATE_SUB('%S',INTERVAL (%{Acct-Session-Time:-0} + %{Acct-Delay-Time:-0}) SECOND))) 3600) Basically, it uses AcctUniqueId which is a hash of AcctSessionId (from the NAS), the NAS-IP, UserName, and Nas-Port and then makes sure the AcctStarttime already in the table is within an hour of the derrived time based on the stoptime and the session length. NOTE: Those using MySQL 5.0+ can use TIMESTAMPDIFF instead of converting and subtracting. I think this change should help reduce duplicate accounting entries. It will incurr some added load for the date calc, but I'm hoping not too much given the AcctUniqueId should be indexed and have a low number of duplicates. Any thoughts? -- Dennis Skinner Systems Administrator BlueFrog Internet http://www.bluefrog.com - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
mysql Accounting not working
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
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
MySQL Accounting in Freeradius
Hi, 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. All that would be fine, except that the times get messed up by being updated to the last AcctStopTime. The update query is as follows: UPDATE radacct SET AcctStopTime = '2005-05-05 16:08:00', AcctSessionTime = '', AcctInputOctets = '', AcctOutputOctets = '', AcctTerminateCause = '', AcctStopDelay = '', ConnectInfo_stop = '' WHERE AcctSessionId = '00-0f-3d-52-2b-13' AND UserName = 'dialup_username' AND NASIPAddress = 'x.x.x.x' which consequently updates all the records from before that have the same AcctSessionId (i.e. all the previous logins by that user). Is this the way it was intended to work? 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? Thanks for your time! -Andrey - 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
Mysql Accounting Data from freeradius.
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Hello All, I'm using the default sql.conf for the Acount start/stop information that will be inserted into the SQL database. however I found some of the info was inserted into the SQL server. E.g NASPortType (I've sure the request have this) ConnectInfo_start ConnectInfo_stop CalledStationId (should be the NAS MAC) FramedProtocol (the Ipaddress from ippool?) FramedIPAddress (same as above but is subnet) Anyone can tell me why and how to make them work? Regards, -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.4 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iD8DBQFCJYMLV0p9slMZLW4RAhxBAJwKrgwV0lWIoZifvmwKL2T9k04iJwCgjcYk FE8A/k7rAVypQUzdnqj3cOs= =SaJJ -END PGP SIGNATURE- - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
MYSQL accounting
I am running Freeradius 1.0.1 on Fedora Core 3 and authenticating wireless users with PEAP. I am trying to get accounting to work, but I don't understand the results that I am getting. The first entry has the MAC address as the UserName, no realm, and the AcctAtthentic is Local. 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) values('-0002', 'cb6600bfed39629d', '0080c813ae30', '', '127.0.0.1', '1', 'Wireless-802.11', '2005-02-04 17:28:36', '0', '0', 'Local', 'CONNECT 11Mbps 802.11b', '', '0', '0', '00-11-95-8C-D5-BE:OGWN', '00-80-C8-13-AE-30', '', '', '', '', '', '0'); Next comes authentication: INSERT into radpostauth (id, user, pass, reply, date) values ('', 'DI107079-3800=5C=5Ctom', 'Chap-Password', 'Access-Accept', NOW()); INSERT into radpostauth (id, user, pass, reply, date) values ('', 'DI107079-3800=5C=5Ctom', 'Chap-Password', 'Access-Accept', NOW()); Next comes the Accounting-Start, and that's where the questions start. Here we have the Stripped-UserName, the domain, and the AcctAtthentic is Radius. 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) values('-0002', '3ccf0ee5ef643776', 'tom', 'DI107079-3800', '127.0.0.1', '1', 'Wireless-802.11', '2005-02-04 17:28:36', '0', '0', 'RADIUS', 'CONNECT 11Mbps 802.11b', '', '0', '0', '00-11-95-8C-D5-BE:OGWN', '00-80-C8-13-AE-30', '', '', '', '', '', '0'); Is this supposed to be an update of the record above? The Accounting-Stop updates the first record by matching the MAC address. How do I get the second record closed if the Stripped-UserName is not passed? Can I take the UserName out of the update? UPDATE radacct SET AcctStopTime = '2005-02-04 17:39:46', AcctSessionTime = '669', AcctInputOctets = '', AcctOutputOctets = '', AcctTerminateCause = '', AcctStopDelay = '', ConnectInfo_stop = 'CONNECT 11Mbps 802.11b' WHERE AcctSessionId = '-0002' AND UserName = '0080c813ae30' AND NASIPAddress = '127.0.0.1'; Sorry for the long post. Thanks in advance for any help. - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
EAP TLS + Mysql + Accounting
Hi All I'm using a free radius with EAP TLS and Mysql and i work :) but the Accounting Query no working when the user authenticates ! My Config : authorize { # # The preprocess module takes care of sanitizing some bizarre # attributes in the request, and turning them into attributes # which are more standard. # # It takes care of processing the 'raddb/hints' and the # 'raddb/huntgroups' files. # # It also adds the %{Client-IP-Address} attribute to the request. preprocess # # If you want to have a log of authentication requests, # un-comment the following line, and the 'detail auth_log' # section, above. auth_log # attr_filter # # The chap module will set 'Auth-Type := CHAP' if we are # handling a CHAP request and Auth-Type has not already been set ## chap # # If the users are logging in with an MS-CHAP-Challenge # attribute for authentication, the mschap module will find # the MS-CHAP-Challenge attribute, and add 'Auth-Type := MS-CHAP' # to the request, which will cause the server to then use # the mschap module for authentication. ## mschap # # If you have a Cisco SIP server authenticating against # FreeRADIUS, uncomment the following line, and the 'digest' # line in the 'authenticate' section. # digest # # Look for IPASS style 'realm/', and if not found, look for # '@realm', and decide whether or not to proxy, based on # that. # IPASS # # If you are using multiple kinds of realms, you probably # want to set ignore_null = yes for all of them. # Otherwise, when the first style of realm doesn't match, # the other styles won't be checked. # ## suffix # ntdomain # # This module takes care of EAP-MD5, EAP-TLS, and EAP-LEAP # authentication. # # It also sets the EAP-Type attribute in the request # attribute list to the EAP type from the packet. ## eap # # Read the 'users' file ## files # # Look in an SQL database. The schema of the database # is meant to mirror the users file. # # See Authorization Queries in sql.conf sql # # If you are using /etc/smbpasswd, and are also doing # mschap authentication, the un-comment this line, and # configure the 'etc_smbpasswd' module, above. # etc_smbpasswd # # The ldap module will set Auth-Type to LDAP if it has not # already been set # ldap # # Enforce daily limits on time spent logged in. # daily # # Use the checkval module # checkval } # Authentication. # # # This section lists which modules are available for authentication. # Note that it does NOT mean 'try each module in order'. It means # that a module from the 'authorize' section adds a configuration # attribute 'Auth-Type := FOO'. That authentication type is then # used to pick the apropriate module from the list below. # # In general, you SHOULD NOT set the Auth-Type attribute. The server # will figure it out on its own, and will do the right thing. The # most common side effect of erroneously setting the Auth-Type # attribute is that one authentication method will work, but the # others will not. # # The common reasons to set the Auth-Type attribute by hand # is to either forcibly reject the user, or forcibly accept him. # authenticate { # # PAP authentication, when a back-end database listed # in the 'authorize' section supplies a password. The # password can be clear-text, or encrypted. Auth-Type PAP { pap } # # Most people want CHAP authentication # A back-end database listed in the 'authorize' section # MUST supply a CLEAR TEXT password. Encrypted passwords # won't work. Auth-Type CHAP { chap } # # MSCHAP authentication. Auth-Type MS-CHAP { mschap } # # If you have a Cisco SIP server authenticating against # FreeRADIUS, uncomment the following line, and the 'digest' # line in the 'authorize' section. # digest # # Pluggable Authentication Modules. # pam # # See 'man getpwent' for information on how the 'unix' # module checks the users password. Note that packets # containing CHAP-Password attributes CANNOT be authenticated # against /etc/passwd! See the FAQ for details. # unix # Uncomment it if you want to use ldap for
MYSQL Accounting Table Size?
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? Thanx Cris --- 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: 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
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 :-) Thanks, Ken A - 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
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
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
duplicate accounting with mysql-accounting and radrelay
Hi, there are several things I can imagine to prevent the below, but before re-inventing the wheel, I'm sure somebody of you has a simple solution for this or some good posts to point to ? Here it goes: using freeradius-1.0-pre2 on two servers, setup as follows: - server1 doing local mysql-accounting into table radacct - server2 is only accounting to detail and a detail-relay files for usage with radrelay to replay them to server1 the mysql-db is replicated from server1 (local-db) to server2 (local-db) but server2 does no accounting into sql while still doing auth/author but this shouldn't matter for this. Now, everything fine so far but while testing failover, I got duplicate accounting-records inserted into radacct-table. setup of database and queries is quite straightforward from the supplied sql.conf I were able to understand what happened: - server1 shutdown - session started 21:17:32, auth by server2, acct-start record on server2 saved in detail-relay for radrelay - radrelay on server2 has not yet sent the record from 21:17:32 to server1 - 21:22:02 server1 is up again - an acct-alive received for this session on server1 - server1 inserts a record with accounting_update_query_alt (as expected, no session in radacct yet present, so accounting_update_query fails and _alt kicks in) - 21:24:04 radrelay on server2 sends acct-start record to server1 using radrelay - server1 creates a new acct-session in radacct table (also as expected, accounting_start_query works fine) - from now on, the two sessions are updated in sync and closed correctly by server1 Now, banging my head to some walls, there are some more cases where things will go wrong: Scenario2: server1 down - acct-alive sent to server2 - server1 up - acct-stop to server1 - acct-alive from server2 sent by radrelay - again duplicate sessions in radacct) The easiest thing I could imagine is something with AcctUniqueId to prevent duplicates BUT: AcctUniqueId is different between server1 and server2 for the above session, after going through all logs, Client-IP-Address is server2 instead of the NAS in the packet radrelay sent from server2 to server1 (which is intentional what I've understood) Now one could remove Client-Ip from acct_unique and make it unique in the DB but this alone probably won't really solve the problem. I'd appreciate any hint on solving these duplicate accounting issues -or in general on how to get 100% reliable accounting into my db with two radius servers. Michael - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Re: R: MySQL accounting and Cisco-AVPair
Le ven 26/03/2004 à 11:24, Pugnaloni Federico a écrit : I've found an old patch to cisco_vsa_hack http://lists.cistron.nl/pipermail/freeradius-devel/2001-August/001181.html This patch (well, a modified version) has already been applied to the 0.9.2 version in Debian Sarge/Sid. I rebuild FreeRADIUS with latest PostgreSQL version anyway, and it seems at least to run. I will let you know, guys. i don't know C language so i've applied the patch as it was... it works!! cisco_vsa_hack change Cisco-AVPair = ip:source-ip=192.168.0.127 to ip:source-ip=192.168.0.127 so i've modified sql.conf to store this info on db radacct and now it's ok i don't know if the cisco_vsa_hack now is ok but it seems to works fine -Messaggio originale- Da: Jérôme Warnier [mailto:[EMAIL PROTECTED] Inviato: giovedì 25 marzo 2004 19:30 A: '[EMAIL PROTECTED]' Oggetto: 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~/radius d/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
R: MySQL accounting and Cisco-AVPair
I've found an old patch to cisco_vsa_hack http://lists.cistron.nl/pipermail/freeradius-devel/2001-August/001181.html i don't know C language so i've applied the patch as it was... it works!! cisco_vsa_hack change Cisco-AVPair = ip:source-ip=192.168.0.127 to ip:source-ip=192.168.0.127 so i've modified sql.conf to store this info on db radacct and now it's ok i don't know if the cisco_vsa_hack now is ok but it seems to works fine -Messaggio originale- Da: Jérôme Warnier [mailto:[EMAIL PROTECTED] Inviato: giovedì 25 marzo 2004 19:30 A: '[EMAIL PROTECTED]' Oggetto: 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~/radius d/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 - 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
MySQL accounting and Cisco-AVPair
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? -- Federico Pugnaloni - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
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
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