Re: log loading of configuration files [was Re: Accounting to MySQL not working]
Josip Rodin wrote: Then again, there is no clear indication to users which part of the large debug output is important and which part is ignorable, so even if they don't ignore it, it may still actually be too complicated for them to handle. The alternative is to run a system that you don't understand. This isn't recommended. For example, add a debug level which would make all references to module instances also include the config filename they were read from. The solution for too much debug output is even *more* debug output? I see your point, but I *still* think there will be issues reading the debugging output, even with having it print filenames. It would be a long output, but doesn't seem very hard to implement (just keep a mapping of module-to-configfilename sets in memory) and would be a good option to have in these cases. Ditto for other setting blocks - virtual hosts, home servers, home server pools. The filename is already cached internally. I think it's safer just to *occasionally* print out the filename, and then ask people to *read* the debug output. Alan DeKok. - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Re: log loading of configuration files [was Re: Accounting to MySQL not working]
On Mon, Jun 07, 2010 at 10:49:01AM +0200, Alan DeKok wrote: Josip Rodin wrote: Then again, there is no clear indication to users which part of the large debug output is important and which part is ignorable, so even if they don't ignore it, it may still actually be too complicated for them to handle. The alternative is to run a system that you don't understand. This isn't recommended. For example, add a debug level which would make all references to module instances also include the config filename they were read from. The solution for too much debug output is even *more* debug output? I see your point, but I *still* think there will be issues reading the debugging output, even with having it print filenames. If the debugging output isn't particularly straightforward - and with regard to filenames it doesn't really have to be, because you can put practically any kind of setting into what is nominally a virtual host file, it can be clients or home servers or realms or whatever - then people will have issues reading it. Add to that this inherent ability to *silently* override one setting with another from a different file, and then even more people will have issues reading the debug output. In other words, there was too much debug output of the wrong kind, and too little debug output of the right kind, to debug that problem. So the solution is to expand it in the areas where it needs expansion, and reduce it in the areas where it's too verbose already. IOW, don't just blurt out the list of files in the beginning, but put them down there next to the settings that come from those files. Though I think probably the best way forward in the matter of restructuring debug output is to generally straighten out like with those new patches you mentioned a few months back. What happened to that? -- 2. That which causes joy or happiness. - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Re: log loading of configuration files [was Re: Accounting to MySQL not working]
On Wed, Jun 02, 2010 at 01:46:02PM +0200, Alan DeKok wrote: When they run it with -X, they'll see the packets as they come in and that's good for the debugging of the per-request logic, but a lot of this initial text will scroll down the screen as if everything in it is all right, and they might miss important information in it. Well... it shouldn't be too much to ask people to read the debug output. It prints out filenames for a *reason*. Anyone ignoring that because it's too complicated is responsible for their own choice. Then again, there is no clear indication to users which part of the large debug output is important and which part is ignorable, so even if they don't ignore it, it may still actually be too complicated for them to handle. The issues seen recently here with unusual behavior in the config are partly because the server is flexible, in that it allows multiple overlapping configuration sections. It's also partly the responsibility of people who don't examine the configuration files the server is using. On this specific issue - if, perhaps, there was a message about which setting was read from which file, rather than two asynchronous sets of messages, it would be easier to understand and see any unwanted duplication. For example, add a debug level which would make all references to module instances also include the config filename they were read from. It would be a long output, but doesn't seem very hard to implement (just keep a mapping of module-to-configfilename sets in memory) and would be a good option to have in these cases. Ditto for other setting blocks - virtual hosts, home servers, home server pools. -- 2. That which causes joy or happiness. - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
log loading of configuration files [was Re: Accounting to MySQL not working]
On Tue, Jun 01, 2010 at 02:19:31PM +0200, joy wrote: On Sat, May 29, 2010 at 10:42:06AM +0200, Alan DeKok wrote: Even Apache reads the entire directory: ... # Include the virtual host configurations: Include /etc/apache2/sites-enabled/ ... So what's the solution? Why isn't this a problem for Apache, and is a problem here? It stands to reason that they have their own share of people making these mistakes, we just don't hear about it here. :) It occurs to me that it would be a good idea to add an option that would allow users to log the entire initial configuration parsing on startup to the main log file, IOW log that part even if the server did not run with -X. When they run it with -X, they'll see the packets as they come in and that's good for the debugging of the per-request logic, but a lot of this initial text will scroll down the screen as if everything in it is all right, and they might miss important information in it. This option could default to on in the default config files, for the sake of newbies. Since server (re)starts aren't common, let alone a per-packet event (hopefully! :), this would not be a deviation from the practice of keeping the logs quiet by default. And the advanced users would simply keep their old configs and/or turn the new option off. On related note, OpenLDAP provides loglevel 'config' for a very similar functionality, it logs the parsing of slapd.conf on start. -- 2. That which causes joy or happiness. - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Re: log loading of configuration files [was Re: Accounting to MySQL not working]
Josip Rodin wrote: It occurs to me that it would be a good idea to add an option that would allow users to log the entire initial configuration parsing on startup to the main log file, IOW log that part even if the server did not run with -X. Hmm... OK. When they run it with -X, they'll see the packets as they come in and that's good for the debugging of the per-request logic, but a lot of this initial text will scroll down the screen as if everything in it is all right, and they might miss important information in it. Well... it shouldn't be too much to ask people to read the debug output. It prints out filenames for a *reason*. Anyone ignoring that because it's too complicated is responsible for their own choice. The issues seen recently here with unusual behavior in the config are partly because the server is flexible, in that it allows multiple overlapping configuration sections. It's also partly the responsibility of people who don't examine the configuration files the server is using. On related note, OpenLDAP provides loglevel 'config' for a very similar functionality, it logs the parsing of slapd.conf on start. That's what debug mode does... Alan DeKok. - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Re: Accounting to MySQL not working
On Sat, May 29, 2010 at 10:42:06AM +0200, Alan DeKok wrote: Even Apache reads the entire directory: ... # Include the virtual host configurations: Include /etc/apache2/sites-enabled/ ... So what's the solution? Why isn't this a problem for Apache, and is a problem here? It stands to reason that they have their own share of people making these mistakes, we just don't hear about it here. :) -- 2. That which causes joy or happiness. - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Re: Accounting to MySQL not working
John Dennis wrote: Hmm... we just had a discussion about how loading every configuration file in a directory trips folks up. Yet another example :-) We need a solution that is (a) easy, and (b) doesn't involve a massive re-name of files in the middle of a stable release? Even Apache reads the entire directory: ... # Include the virtual host configurations: Include /etc/apache2/sites-enabled/ ... So what's the solution? Why isn't this a problem for Apache, and is a problem here? Alan DeKok. - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Re: Accounting to MySQL not working
Hi Alan, thank you for your response. according to the debug: +- entering group accounting {...} [detail]expand: /var/log/freeradius/radacct/%{Client-IP-Address}/detail-%Y%m%d - +/var/log/freeradius/radacct/192.168.1.10/detail-20100527 [detail] /var/log/freeradius/radacct/%{Client-IP-Address}/detail-%Y%m%d expands to +/var/log/freeradius/radacct/192.168.1.10/detail-20100527 [detail]expand: %t - Thu May 27 23:32:23 2010 ++[detail] returns ok ++[unix] returns ok [radutmp] expand: /var/log/freeradius/radutmp - /var/log/freeradius/radutmp [radutmp] expand: %{User-Name} - chrissql ++[radutmp] returns ok [attr_filter.accounting_response] expand: %{User-Name} - chrissql attr_filter: Matched entry DEFAULT at line 12 ++[attr_filter.accounting_response] returns updated Sending Accounting-Response of id 77 to 192.168.1.10 port 1646 Finished request 19. so, its drops into accouting section...it does detail unix radutmp attr_filter.accounting_response but where oh were was the SQL being called? hmm, from here is doesnt look like you are calling it. check the sites-enabled/* files (I dont know what virtual servers you have running or what you've called them) and please uncomment the 'sql' It comes after the lines that say: # # Log traffic to an SQL database. # # See Accounting queries in sql.conf this part is (and was) enabled please find below the configuration of the files (which is the same configuration as for 2.0.4 - this configuration is working for 2.0.4) however, it seems the sql line is ignored by the radius server file: default authorize { preprocess chap mschap suffix eap { ok = return } unix files sql expiration logintime pap } authenticate { Auth-Type PAP { pap } Auth-Type CHAP { chap } Auth-Type MS-CHAP { mschap } unix eap } preacct { preprocess acct_unique suffix files } accounting { detail unix radutmp sql attr_filter.accounting_response } session { radutmp } post-auth { exec Post-Auth-Type REJECT { attr_filter.access_reject } } pre-proxy { } post-proxy { eap } -- fiel: inner-tunnel server inner-tunnel { authorize { chap mschap unix suffix update control { Proxy-To-Realm := LOCAL } eap { ok = return } files sql expiration logintime pap } authenticate { Auth-Type PAP { pap } Auth-Type CHAP { chap } Auth-Type MS-CHAP { mschap } unix eap } session { radutmp } post-auth { Post-Auth-Type REJECT { attr_filter.access_reject } } pre-proxy { } post-proxy { eap } BTW: If I uncomment all 'sql' entries, I get data into the radpostauth table, but I never get data into the radacct table. Has somebody a working configuration for logging into the radacct mysql table with freeradius v2.1.8 (under Debian Lenny)? br Christoph -- GMX.at - Österreichs FreeMail-Dienst mit über 2 Mio Mitgliedern E-Mail, SMS mehr! Kostenlos: http://portal.gmx.net/de/go/atfreemail - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Re: Accounting to MySQL not working
Christoph Schwabl wrote: this part is (and was) enabled please find below the configuration of the files (which is the same configuration as for 2.0.4 - this configuration is working for 2.0.4) however, it seems the sql line is ignored by the radius server This happens in one of two cases: 1) the server is reading a *different* file than the one you're editing 2) there are *two* accounting sections, and the server is using the first rather than the second. BTW: If I uncomment all 'sql' entries, I get data into the radpostauth table, but I never get data into the radacct table. Then either it isn't receiving accounting packets, *or* it's not using the sql configuration you think it's using. Has somebody a working configuration for logging into the radacct mysql table with freeradius v2.1.8 (under Debian Lenny)? $ cd /etc/raddb/sites-enabled $ grep sql * And un-comment the lines that look like: default:# sql This is the default config, and it does work. Alan DeKok. - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Re: Accounting to MySQL not working
however, it seems the sql line is ignored by the radius server This happens in one of two cases: 1) the server is reading a *different* file than the one you're editing this exactly was the problem I had 2 files in /etc/raddb/sites-enabled default.original default It seems the server was reading default.original Since I deleted default.original its working fine. thx br Christoph -- GMX.at - Österreichs FreeMail-Dienst mit über 2 Mio Mitgliedern E-Mail, SMS mehr! Kostenlos: http://portal.gmx.net/de/go/atfreemail - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Re: Accounting to MySQL not working
On 05/28/2010 04:33 PM, Christoph Schwabl wrote: however, it seems the sql line is ignored by the radius server This happens in one of two cases: 1) the server is reading a *different* file than the one you're editing this exactly was the problem I had 2 files in /etc/raddb/sites-enabled default.original default It seems the server was reading default.original Since I deleted default.original its working fine. Hmm... we just had a discussion about how loading every configuration file in a directory trips folks up. Yet another example :-) -- John Dennis jden...@redhat.com Looking to carve out IT costs? www.redhat.com/carveoutcosts/ - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Re: Accounting to MySQL not working
:) Sure, its good idea to create backups but do not store them in the sites-enabled dir :) John Dennis wrote: On 05/28/2010 04:33 PM, Christoph Schwabl wrote: however, it seems the sql line is ignored by the radius server This happens in one of two cases: 1) the server is reading a *different* file than the one you're editing this exactly was the problem I had 2 files in /etc/raddb/sites-enabled default.original default It seems the server was reading default.original Since I deleted default.original its working fine. Hmm... we just had a discussion about how loading every configuration file in a directory trips folks up. Yet another example :-) - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Re: Accounting to MySQL not working
Making a backup of the file in sites-available would make more sense since the files in sites-enabled are only links to the files in sites-enabled. It is documented. ;) Marinko Tarlac mangi...@gmail.com wrote: :) Sure, its good idea to create backups but do not store them in the sites-enabled dir :) John Dennis wrote: On 05/28/2010 04:33 PM, Christoph Schwabl wrote: however, it seems the sql line is ignored by the radius server This happens in one of two cases: 1) the server is reading a *different* file than the one you're editing this exactly was the problem I had 2 files in /etc/raddb/sites-enabled default.original default It seems the server was reading default.original Since I deleted default.original its working fine. Hmm... we just had a discussion about how loading every configuration file in a directory trips folks up. Yet another example :-) - 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: Accounting to MySQL not working
hi, according to the debug: +- entering group accounting {...} [detail]expand: /var/log/freeradius/radacct/%{Client-IP-Address}/detail-%Y%m%d - +/var/log/freeradius/radacct/192.168.1.10/detail-20100527 [detail] /var/log/freeradius/radacct/%{Client-IP-Address}/detail-%Y%m%d expands to +/var/log/freeradius/radacct/192.168.1.10/detail-20100527 [detail]expand: %t - Thu May 27 23:32:23 2010 ++[detail] returns ok ++[unix] returns ok [radutmp] expand: /var/log/freeradius/radutmp - /var/log/freeradius/radutmp [radutmp] expand: %{User-Name} - chrissql ++[radutmp] returns ok [attr_filter.accounting_response] expand: %{User-Name} - chrissql attr_filter: Matched entry DEFAULT at line 12 ++[attr_filter.accounting_response] returns updated Sending Accounting-Response of id 77 to 192.168.1.10 port 1646 Finished request 19. so, its drops into accouting section...it does detail unix radutmp attr_filter.accounting_response but where oh were was the SQL being called? hmm, from here is doesnt look like you are calling it. check the sites-enabled/* files (I dont know what virtual servers you have running or what you've called them) and please uncomment the 'sql' It comes after the lines that say: # # Log traffic to an SQL database. # # See Accounting queries in sql.conf alan - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Accounting to MySQL not working
Hello, I have a small problem with accounting to mysql. freeradius 2.1.8 mysql Ver 14.12 Distrib 5.0.51a, for debian-linux-gnu (i486) using readline 5.2 The NAS is sending accounting data, which I can see in: more /var/log/freeradius/radacct/192.168.1.10/detail-20100526 ... Wed May 26 23:43:57 2010 Acct-Session-Id = 0039 Called-Station-Id = 0011.2177.0da4 Calling-Station-Id = 001f.3b1c.f25b Cisco-AVPair = ssid=testradius Cisco-AVPair = vlan-id=0 Cisco-AVPair = nas-location=unspecified User-Name = testuser Cisco-AVPair = connect-progress=Call Up Acct-Session-Time = 237 Acct-Input-Octets = 35971 Acct-Output-Octets = 64059 Acct-Input-Packets = 473 Acct-Output-Packets = 98 Acct-Authentic = RADIUS Acct-Status-Type = Interim-Update NAS-Port-Type = Wireless-802.11 Cisco-NAS-Port = 296 NAS-Port = 296 Service-Type = Framed-User NAS-IP-Address = 192.168.1.10 Acct-Delay-Time = 0 Acct-Unique-Session-Id = 4d69927723cb56d8 Timestamp = 1274910237 Request-Authenticator = Verified But I cann't find accounting data in MySQL database. My configuration is: radiusd.conf == ... $INCLUDE sql.conf ... readclients = yes ... sites-enabled/inner-tunnel == ... authorize { chap mschap unix suffix update control { Proxy-To-Realm := LOCAL } eap { ok = return } files sql expiration logintime pap } ... sites-enabled/default == ... authorize { preprocess chap mschap suffix eap { ok = return } unix files sql expiration logintime pap } ... accounting { detail unix radutmp sql attr_filter.accounting_response } ... Using users (radcheck) and clients (nas) from MySQL are working well. However, accounting into MySQL is not working. Anybody an idea? thx br Christoph -- GMX.at - Österreichs FreeMail-Dienst mit über 2 Mio Mitgliedern E-Mail, SMS mehr! Kostenlos: http://portal.gmx.net/de/go/atfreemail - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Re: Accounting into MySQL
Yes, both. 2009/5/14 EasyHorpak.com i...@easyhorpak.com: is it freeradius and freeradius-mysql ? I'm doing some more testing using the suggestions from Edvin and Ivan (almost like the two ivan's), and will follow up. I think its just my misunderstanding of what accounting actually does. ;) Cheers, - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Accounting into MySQL
Hi folks, I have an Ubuntu server 8.04.2 LTS running (pretty bare bones). I installed freeradius and freeradius-sql from the repositories (MySQL 5, all dependiencies, etc). For configuration I followed the SQL HOWTO on the FreeRADIUS Wiki page. I am able to successfully use NTRadPing Test Utility against the radius server when configured to use SQL as the back end. I also followed the instructions on the Frontios website and a few other websites, and by searching this groups mail archive on how to configure accounting into SQL, but I'm not getting any accounting information. Running freeradius -X I see the queries going to the database for authentication, but I don't see any insert/select into statements indicating accounting information being written to the database. I have some basic questions to better understand accounting into SQL. 1. Using NTRadPING, should I get entries in my radacct table? 2. I have uncommented the sql_log, and all the detail log sections, and in the accounting section, and uncommented the sql in the accounting section. What other changed are required assuming that I am already successfully authenticating from the radcheck table? 3. Is there a more detailed how-to on the internet (I guess my google-foo is not as good as others) specifically on accounting in mysql? 3.b) ... sepecifically on MAC authentication? Thanks so much! Tim - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
RE: Accounting into MySQL
1. Using NTRadPING, should I get entries in my radacct table? Are you sending accounting packets? If not - why should freeradius pass any request to accounting parts. 2. I have uncommented the sql_log, and all the detail log sections, and in the accounting section, and uncommented the sql in the accounting section. What other changed are required assuming that I am already successfully authenticating from the radcheck table? Did you set the right user/password combination for access to mysql server? ( you probably did, else you would have seen errors while starting freeradius with -X switch ) Regards, E:S - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Re: Accounting into MySQL
I have some basic questions to better understand accounting into SQL. 1. Using NTRadPING, should I get entries in my radacct table? No. Those are authentication, not accounting requests. 2. I have uncommented the sql_log, and all the detail log sections, and in the accounting section, and uncommented the sql in the accounting section. What other changed are required assuming that I am already successfully authenticating from the radcheck table? You don't need all that. Just sql in accounting. Detail writes to files. 3. Is there a more detailed how-to on the internet (I guess my google-foo is not as good as others) specifically on accounting in mysql? Configure sql connection details in sql.conf. Uncomment sql in accounting section of the default virtual server. That's it. 3.b) ... sepecifically on MAC authentication? http://wiki.freeradius.org/Mac-Auth Ivan Kalik Kalik Informatika ISP - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Re: Accounting into MySQL
is it freeradius and freeradius-mysql ? - Original Message - From: Tim Dressel tjdres...@gmail.com To: freeradius-users@lists.freeradius.org Sent: Friday, May 15, 2009 3:53 AM Subject: Accounting into MySQL Hi folks, I have an Ubuntu server 8.04.2 LTS running (pretty bare bones). I installed freeradius and freeradius-sql from the repositories (MySQL 5, all dependiencies, etc). For configuration I followed the SQL HOWTO on the FreeRADIUS Wiki page. I am able to successfully use NTRadPing Test Utility against the radius server when configured to use SQL as the back end. I also followed the instructions on the Frontios website and a few other websites, and by searching this groups mail archive on how to configure accounting into SQL, but I'm not getting any accounting information. Running freeradius -X I see the queries going to the database for authentication, but I don't see any insert/select into statements indicating accounting information being written to the database. I have some basic questions to better understand accounting into SQL. 1. Using NTRadPING, should I get entries in my radacct table? 2. I have uncommented the sql_log, and all the detail log sections, and in the accounting section, and uncommented the sql in the accounting section. What other changed are required assuming that I am already successfully authenticating from the radcheck table? 3. Is there a more detailed how-to on the internet (I guess my google-foo is not as good as others) specifically on accounting in mysql? 3.b) ... sepecifically on MAC authentication? Thanks so much! Tim - 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: Accounting in MySQL
No, I don´t have connection problems, I have actually my FreeRADIUS users in the radcheck and radreply tables, and working fine, but accounting do not. Germán P. Santillán Administrador de Redes Jefe del Dpto. Técnico DESETech Argentina S.A. San Martín 133 - CP: B8000FIC Bahía Blanca - Argentina Tel/Fax: +54 (291) 456-5642 [EMAIL PROTECTED] http://www.desetech.com.ar From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] .org] On Behalf Of satish patel Sent: Tuesday, January 23, 2007 4:56 AM To: FreeRadius users mailing list Subject: Re: Accounting in MySQL Dear First check your radiusd -X debug log and find mysql connectivity debug if there any problem regarding connection ??? then check radius.conf file there is accounting option and put sql key word in it and u can also find some document on my website http://geocities.com/satish_patel_2000_2000/ Satish Patel DESEtech - German P. Santillan [EMAIL PROTECTED] wrote: I actually have my Users DB in MySQL Server and my FreeRADIUS use the radcheck and radreply tables to read (SELECT) records, in my radiusd.conf I have... authorize { sql } accounting { sql } But I don´t hace records in radacct Table. What is the problem? Thanks in advance and sorry for my English Germán P. Santillán Administrador de Redes Jefe del Dpto. Técnico DESETech Argentina S.A. San Martín 133 - CP: B8000FIC Bahía Blanca - Argentina Tel/Fax: +54 (291) 456-5642 [EMAIL PROTECTED] http://www.desetech.com.ar - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html _ Heres a new way to find what you're looking for - Yahoo! http://us.rd.yahoo.com/mail/in/yanswers/*http:/in.answers.yahoo.com/ Answers _ Heres a new way to find what you're looking for - Yahoo! http://us.rd.yahoo.com/mail/in/yanswers/*http:/in.answers.yahoo.com/ Answers - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
RE: Accounting in MySQL
Dear What NAS device are u using ? when user authenticate from radius thn nas send acct-start packet to radius if your NAS not sending start accounting packet to freeradius then radius not start accounting I have cisco Router for NAS aaa accounting start-stop radius --- commnad i m useing plz see my document there is some more help regrading NAS Satish Patel DESEtech - German P. Santillan [EMAIL PROTECTED] wrote: v\:* {behavior:url(#default#VML);} o\:* {behavior:url(#default#VML);} w\:* {behavior:url(#default#VML);} .shape {behavior:url(#default#VML);} No, I don´t have connection problems, I have actually my FreeRADIUS users in the radcheck and radreply tables, and working fine, but accounting do not. Germán P. Santillán Administrador de Redes Jefe del Dpto. Técnico DESETech Argentina S.A. San Martín 133 - CP: B8000FIC Bahía Blanca - Argentina Tel/Fax: +54 (291) 456-5642 [EMAIL PROTECTED] http://www.desetech.com.ar From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of satish patel Sent: Tuesday, January 23, 2007 4:56 AM To: FreeRadius users mailing list Subject: Re: Accounting in MySQL Dear First check your radiusd -X debug log and find mysql connectivity debug if there any problem regarding connection ??? then check radius.conf file there is accounting option and put sql key word in it and u can also find some document on my website http://geocities.com/satish_patel_2000_2000/ Satish Patel DESEtech - German P. Santillan [EMAIL PROTECTED] wrote: I actually have my Users DB in MySQL Server and my FreeRADIUS use the radcheck and radreply tables to read (SELECT) records, in my radiusd.conf I have... authorize { sql } accounting { sql } But I don´t hace records in radacct Table. What is the problem? Thanks in advance and sorry for my English Germán P. Santillán Administrador de Redes Jefe del Dpto. Técnico DESETech Argentina S.A. San Martín 133 - CP: B8000FIC Bahía Blanca - Argentina Tel/Fax: +54 (291) 456-5642 [EMAIL PROTECTED] http://www.desetech.com.ar - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html - Heres a new way to find what you're looking for - Yahoo! Answers - Heres a new way to find what you're looking for - Yahoo! Answers - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html - Heres a new way to find what you're looking for - Yahoo! Answers - Heres a new way to find what you're looking for - Yahoo! Answers - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Re: Accounting in MySQL
Dear Find freeradius + Mysql document on my website http://geocities.com/satish_patel_2000_2000/ Satish Patel Alan DeKok [EMAIL PROTECTED] wrote: DESEtech - German P. Santillan wrote: But I don´t hace records in radacct Table. What is the problem? See the FAQ. Is the server receiving accounting packets? Alan DeKok. -- http://deployingradius.com - The web site of the book http://deployingradius.com/blog/ - The blog - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html - Heres a new way to find what you're looking for - Yahoo! Answers - Heres a new way to find what you're looking for - Yahoo! Answers - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Re: Accounting in MySQL
Dear First check your radiusd -X debug log and find mysql connectivity debug if there any problem regarding connection ??? then check radius.conf file there is accounting option and put sql key word in it and u can also find some document on my website http://geocities.com/satish_patel_2000_2000/ Satish Patel DESEtech - German P. Santillan [EMAIL PROTECTED] wrote: I actually have my Users DB in MySQL Server and my FreeRADIUS use the radcheck and radreply tables to read (SELECT) records, in my radiusd.conf I have... authorize { sql } accounting { sql } But I don´t hace records in radacct Table. What is the problem? Thanks in advance and sorry for my English Germán P. Santillán Administrador de Redes Jefe del Dpto. Técnico DESETech Argentina S.A. San Martín 133 - CP: B8000FIC Bahía Blanca - Argentina Tel/Fax: +54 (291) 456-5642 [EMAIL PROTECTED] http://www.desetech.com.ar - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html - Heres a new way to find what you're looking for - Yahoo! Answers - Heres a new way to find what you're looking for - Yahoo! Answers - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Accounting in MySQL
I actually have my Users DB in MySQL Server and my FreeRADIUS use the radcheck and radreply tables to read (SELECT) records, in my radiusd.conf I have... authorize { sql } accounting { sql } But I don´t hace records in radacct Table. What is the problem? Thanks in advance and sorry for my English Germán P. Santillán Administrador de Redes Jefe del Dpto. Técnico DESETech Argentina S.A. San Martín 133 - CP: B8000FIC Bahía Blanca - Argentina Tel/Fax: +54 (291) 456-5642 [EMAIL PROTECTED] http://www.desetech.com.ar - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Re: Accounting in MySQL
DESEtech - German P. Santillan wrote: But I don´t hace records in radacct Table. What is the problem? See the FAQ. Is the server receiving accounting packets? Alan DeKok. -- http://deployingradius.com - The web site of the book http://deployingradius.com/blog/ - The blog - 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: how to do accounting with mysql?
I've set up sqlcounter and I'm on testing right now, the next step is prepaid cards and still dont know how to do them Rogelio Alvarado Anchisi Ing. de Sistemas Galaxy Communications Corp. Tel. +507-2000128 Fax.+507-2000132 Cel. +507-6744093 -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Nick Berry Sent: Martes, 27 de Abril de 2004 11:14 To: [EMAIL PROTECTED] Subject: RE: how to do accounting with mysql? The NAS has the fake response off, so it is sending the packages. How do I check if it is sending them? I want to have another reference. Other thing. I put the sqlcounter stuff but I don't have any sqlcounter.so or something like that on my lib. What do I have to do to download it and put it on my lib? I am doing accounting with MySQL without sqlcounter (since I'm not limiting my user's connection time). Have you been able to get this to work? Do you still need any help? - 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: how to do accounting with mysql?
I've set up sqlcounter and I'm on testing right now, the next step is prepaid cards and still dont know how to do them read the subscription list - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
how to do accounting with mysql using the radacct table?
Ok, I set up sqlcounter and made my tests. And it keeps doing failures because the sqltrace never shows the sql sentences for accounting. I don't know if it's the table or if it could be something else that is jamming my stuff. Questions: How do I have to fill the radcheck and userinfo in order to make accounting? As far I've done I just put password and pool-name attributes, what am I missing? All the documentation I find doesnt explains it. How do I fill the radacct? Rogelio Alvarado Anchisi Ing. de Sistemas Galaxy Communications Corp. Tel. +507-2000128 Fax.+507-2000132 Cel. +507-6744093 -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Milver S. Nisay Sent: Miércoles, 28 de Abril de 2004 14:34 To: [EMAIL PROTECTED] Subject: Re: how to do accounting with mysql? I've set up sqlcounter and I'm on testing right now, the next step is prepaid cards and still dont know how to do them read the subscription list - 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: setting up accounting in mysql
Hello milver, i already test what you said. example for user X has a session time of 10 hrs (43200 secs). After 1 month he/she update his/her account then what i did add another 10 hrs, which will 43200 + 43200 = session timeout. but as i said we have a client with constant username, adding 10 hrs in session timeout everytime the user updating his/her account is too difficult to maintain. Im concern in noresetcounter sql_query which compute the total sum of user accounting. How to construct sql_query which only compute the total sum of user accounting from this date until this date. thanks you very much for your response. - Original Message - From: Milver S. Nisay [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Monday, April 26, 2004 9:08 PM Subject: Re: setting up accounting in mysql Hello milver, what i mean is in noresetcounter configuration in rlm_sqlcounter. Where resetcounter add all accounting of the user and compare to session timeout. My problem is i have a client with constant username. example: user X have a connection of 10 hrs starting from date, April 26 which should expire on May 27. After May 26, 2004, user X update his/here account. Add another 10 hrs, take note user X has contant username. What im doing is delete user X accounting, so that when he/she login again he/she have new accounting record which will be compared against his/her session timeout. A new start of his/her 10 hrs. Any comment or suggestion? I want to keep this accounting of all users i have. one solution is edit your attributes from your database. if the old attributes is 10 hours, add another 10 hours. in this case, the accounting session are still intact with your database. there is no need to delete and re create his account, you just need to prolong his limit in hours. you can do it directly with your database, or using perl script or using PHP script to manipulate your database. prolong session-timout or max-all-session timeout attributes (if you are using database). - 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: setting up accounting in mysql
Actually im using dialup admin for user account management, thanks very much for reply, i really appreciate it. I have another idea what else if i have 2 database which update user accounting realtime, it is possible? i would create 2 sql.conf in radius.conf. but i dont know what will be the config of another sql.conf which only use to update start and stop of accounting. - Original Message - From: Milver S. Nisay [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, April 27, 2004 9:05 PM Subject: Re: setting up accounting in mysql Hello milver, i already test what you said. example for user X has a session time of 10 hrs (43200 secs). After 1 month he/she update his/her account then what i did add another 10 hrs, which will 43200 + 43200 = session timeout. but as i said we have a client with constant username, adding 10 hrs in session timeout everytime the user updating his/her account is too difficult to maintain. if you are doing it manually with your database, it will be a tedious job if you have to do it on a hundred accounts. have you heard of manipulating your database using PHP or perl script to lessen you tiring typing jobs, nice web interface will make it easy for you i guess. - 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: setting up accounting in mysql
Actually im using dialup admin for user account management, thanks very much for reply, i really appreciate it. I have another idea what else if i have 2 database which update user accounting realtime, it is possible? i would create 2 sql.conf in radius.conf. but i dont know what will be the config of another sql.conf which only use to update start and stop of accounting. doing it on dialup admin,takes longer that a customized PHP and perl script. updating in real time is possible, depends on your NAS config.it can be every 30 seconds or every 1 hour. - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
RE: how to do accounting with mysql?
The NAS has the fake response off, so it is sending the packages. How do I check if it is sending them? I want to have another reference. Other thing. I put the sqlcounter stuff but I don't have any sqlcounter.so or something like that on my lib. What do I have to do to download it and put it on my lib? I am doing accounting with MySQL without sqlcounter (since I'm not limiting my user's connection time). Have you been able to get this to work? Do you still need any help? - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Re: setting up accounting in mysql
Hello milver, what i mean is in noresetcounter configuration in rlm_sqlcounter. Where resetcounter add all accounting of the user and compare to session timeout. My problem is i have a client with constant username. example: user X have a connection of 10 hrs starting from date, April 26 which should expire on May 27. After May 26, 2004, user X update his/here account. Add another 10 hrs, take note user X has contant username. What im doing is delete user X accounting, so that when he/she login again he/she have new accounting record which will be compared against his/her session timeout. A new start of his/her 10 hrs. Any comment or suggestion? I want to keep this accounting of all users i have. - Original Message - From: Milver S. Nisay [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Saturday, April 24, 2004 4:03 PM Subject: Re: setting up accounting in mysql Do u have any idea how to update the user account? example im setup prepaid internet access using freeradius. im include rlm_sqlcounter in the config and it works perfectly. Now my problem is because im configured rlm_sqlcounter for noresetcounter it works when the user consume all his example 10 hrs of prepaid internet. Then as expected he/she cant login anymore. what about if the user update his/her account? Same username with the same password. Although were using prepaid internet but we have a constant username of the user. thanks very much ... if you are using MySQL, you have many options: 1. you can edit the number of hours or the session attribute directly via MySQL console. 2. you can create a nice PHP script to do it for you 3. Perl script is another option //milver - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Re: setting up accounting in mysql
Hello milver, what i mean is in noresetcounter configuration in rlm_sqlcounter. Where resetcounter add all accounting of the user and compare to session timeout. My problem is i have a client with constant username. example: user X have a connection of 10 hrs starting from date, April 26 which should expire on May 27. After May 26, 2004, user X update his/here account. Add another 10 hrs, take note user X has contant username. What im doing is delete user X accounting, so that when he/she login again he/she have new accounting record which will be compared against his/her session timeout. A new start of his/her 10 hrs. Any comment or suggestion? I want to keep this accounting of all users i have. one solution is edit your attributes from your database. if the old attributes is 10 hours, add another 10 hours. in this case, the accounting session are still intact with your database. there is no need to delete and re create his account, you just need to prolong his limit in hours. you can do it directly with your database, or using perl script or using PHP script to manipulate your database. prolong session-timout or max-all-session timeout attributes (if you are using database). - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Re: setting up accounting in mysql
Do u have any idea how to update the user account? example im setup prepaid internet access using freeradius. im include rlm_sqlcounter in the config and it works perfectly. Now my problem is because im configured rlm_sqlcounter for noresetcounter it works when the user consume all his example 10 hrs of prepaid internet. Then as expected he/she cant login anymore. what about if the user update his/her account? Same username with the same password. Although were using prepaid internet but we have a constant username of the user. thanks very much ... if you are using MySQL, you have many options: 1. you can edit the number of hours or the session attribute directly via MySQL console. 2. you can create a nice PHP script to do it for you 3. Perl script is another option //milver - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
how to do accounting with mysql?
Hello Im using v.0.9.3 and Im trying to do accounting. When I see the debug I see that every sql sentence is run, except the accounting sentences. I dont know if it has something to do with radacct. Maybe it could happen because I have the radacct empty. Im sending you the radius.conf and if any one sees the problem please answer authorize { preprocess chap suffix sql mschap } authenticate { Auth-Type PAP { pap } Auth-Type CHAP { chap } Auth-Type MS-CHAP { mschap } eap } preacct { preprocess suffix } accounting { acct_unique detail daily unix sql radutmp main_pool } session { sql } post-auth { main_pool } pre-proxy { } post-proxy { eap } Rogelio Alvarado Anchisi Ing. de Sistemas Galaxy Communications Corp. Tel. +507-2000128 Fax.+507-2000132 Cel. +507-6744093
RE: how to do accounting with mysql?
All the info it says is about Ciscos. But Im using a Nortel. Is there any way other than sql to do accounting? I cant leave my MySql database because I have all users there and are several thousands. Can I do a file accounting using database users? Rogelio Alvarado Anchisi Ing. de Sistemas Galaxy Communications Corp. Tel. +507-2000128 Fax.+507-2000132 Cel. +507-6744093 -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Costin Manda Sent: Viernes, 23 de Abril de 2004 08:02 a.m. To: [EMAIL PROTECTED] Subject: Re: how to do accounting with mysql? doesn't work. you must use postgresql. look in the src/billing directory and all will be revealed! - Original Message - From: Rogelio Alvarado Anchisi To: [EMAIL PROTECTED] Sent: Friday, April 23, 2004 03:50 PM Subject: how to do accounting with mysql? Hello Im using v.0.9.3 and Im trying to do accounting. When I see the debug I see that every sql sentence is run, except the accounting sentences. I dont know if it has something to do with radacct. Maybe it could happen because I have the radacct empty. Im sending you the radius.conf and if any one sees the problem please answer authorize { preprocess chap suffix sql mschap } authenticate { Auth-Type PAP { pap } Auth-Type CHAP { chap } Auth-Type MS-CHAP { mschap } eap } preacct { preprocess suffix } accounting { acct_unique detail daily unix sql radutmp main_pool } session { sql } post-auth { main_pool } pre-proxy { } post-proxy { eap } Rogelio Alvarado Anchisi Ing. de Sistemas Galaxy Communications Corp. Tel. +507-2000128 Fax.+507-2000132 Cel. +507-6744093
Re: how to do accounting with mysql?
Because of this I have both Mysql and postgreSQL installed on my computer. In a way it is safer, if one fails, you have the other. The thing with the mysql-postgress issue is that mysql doesn't have stored procedure support yet and the Cisco timestamp is not supported by Mysql. However, if you have nortel maybe you could use mysql, but you have to edit the queries and maybe the source of freeradius itself. Best talk to one of the programmers. - Original Message - From: Rogelio Alvarado Anchisi To: [EMAIL PROTECTED] Sent: Friday, April 23, 2004 04:15 PM Subject: RE: how to do accounting with mysql? All the info it says is about Ciscos. But Im using a Nortel. Is there any way other than sql to do accounting? I cant leave my MySql database because I have all users there and are several thousands. Can I do a file accounting using database users?
setting up accounting in mysql
Hello, Has anyone set up accounting using the radacct table in MySql? I read the debug and the sql trace doesn't show that the sql is being queried for accounting. Where in the radiusd.conf do I have to set it up? I got into the accounting { } but it seems that the sql trigger I put doesn't do a thing. Please any hint! Rogelio Alvarado Galaxy Communications, Panama - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Re: setting up accounting in mysql
Has anyone set up accounting using the radacct table in MySql? I read the debug and the sql trace doesn't show that the sql is being queried for accounting. Where in the radiusd.conf do I have to set it up? I got into the accounting { } but it seems that the sql trigger I put doesn't do a thing. uncomment sql from radiusd.conf, make sure you include sql.conf and sqlcounter.conf and both are working fine. //milver - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Re: setting up accounting in mysql
I can see that I have rlm_sqlcounter but I don't see the sqlcounter.conf Where or how do I get it? download the latest CVS pls. it should be something like this sqlcounter noresetcounter { driver = rlm_sqlcounter counter-name = Max-All-Session-Time check-name = Max-All-Session sqlmod-inst = sql key = User-Name reset = never query = SELECT SUM(AcctSessionTime) FROM radacct WHERE UserName='%{%k}' } sqlcounter dailycounter { driver = rlm_sqlcounter counter-name = Daily-Session-Time check-name = Max-Daily-Session sqlmod-inst = sqlcca3 key = User-Name reset = daily query = SELECT SUM(AcctSessionTime - GREATEST((%b - UNIX_TIMESTAMP(AcctStartTime)), 0)) FROM radacct WHERE UserName='%{%k}' AND UNIX_TIMESTAMP(AcctStartTime) + AcctSessionTime '%b' } sqlcounter monthlycounter { counter-name = Monthly-Session-Time check-name = Max-Monthly-Session sqlmod-inst = sqlcca3 key = User-Name reset = monthly query = SELECT SUM(AcctSessionTime - GREATEST((%b - UNIX_TIMESTAMP(AcctStartTime)), 0)) FROM radacct WHERE UserName='%{%k}' AND UNIX_TIMESTAMP(AcctStartTime) + AcctSessionTime '%b' } //milver - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Re: how to do accounting with mysql?
Rogelio Alvarado Anchisi [EMAIL PROTECTED] wrote: I'm using v.0.9.3 and I'm trying to do accounting. When I see the debug I see that every sql sentence is run, except the accounting sentences. I don't know if it has something to do with radacct. Maybe it could happen because I have the radacct empty. Or the NAS isn't sending accounting packets? See the FAQ. Alan DeKok. - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
RE: how to do accounting with mysql?
The NAS has the fake response off, so it is sending the packages. How do I check if it is sending them? I want to have another reference. Other thing. I put the sqlcounter stuff but I don't have any sqlcounter.so or something like that on my lib. What do I have to do to download it and put it on my lib? Rogelio Alvarado Anchisi Ing. de Sistemas Galaxy Communications Corp. Tel. +507-2000128 Fax.+507-2000132 Cel. +507-6744093 -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Alan DeKok Sent: Viernes, 23 de Abril de 2004 11:10 a.m. To: [EMAIL PROTECTED] Subject: Re: how to do accounting with mysql? Rogelio Alvarado Anchisi [EMAIL PROTECTED] wrote: I'm using v.0.9.3 and I'm trying to do accounting. When I see the debug I see that every sql sentence is run, except the accounting sentences. I don't know if it has something to do with radacct. Maybe it could happen because I have the radacct empty. Or the NAS isn't sending accounting packets? See the FAQ. Alan DeKok. - 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: how to do accounting with mysql?
Rogelio Alvarado Anchisi [EMAIL PROTECTED] wrote: The NAS has the fake response off, so it is sending the packages. How do I check if it is sending them? I want to have another reference. tcpdump Other thing. I put the sqlcounter stuff but I don't have any sqlcounter.so or something like that on my lib. What do I have to do to download it and put it on my lib? $ cd src/modules/rlm_sqlcounter $ ./configure $ make install Alan Dekok. - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Re: setting up accounting in mysql
Hello Milver, Do u have any idea how to update the user account? example im setup prepaid internet access using freeradius. im include rlm_sqlcounter in the config and it works perfectly. Now my problem is because im configured rlm_sqlcounter for noresetcounter it works when the user consume all his example 10 hrs of prepaid internet. Then as expected he/she cant login anymore. what about if the user update his/her account? Same username with the same password. Although were using prepaid internet but we have a constant username of the user. thanks very much ... - Original Message - From: Milver S. Nisay [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Friday, April 23, 2004 11:57 PM Subject: Re: setting up accounting in mysql I can see that I have rlm_sqlcounter but I don't see the sqlcounter.conf Where or how do I get it? download the latest CVS pls. it should be something like this sqlcounter noresetcounter { driver = rlm_sqlcounter counter-name = Max-All-Session-Time check-name = Max-All-Session sqlmod-inst = sql key = User-Name reset = never query = SELECT SUM(AcctSessionTime) FROM radacct WHERE UserName='%{%k}' } sqlcounter dailycounter { driver = rlm_sqlcounter counter-name = Daily-Session-Time check-name = Max-Daily-Session sqlmod-inst = sqlcca3 key = User-Name reset = daily query = SELECT SUM(AcctSessionTime - GREATEST((%b - UNIX_TIMESTAMP(AcctStartTime)), 0)) FROM radacct WHERE UserName='%{%k}' AND UNIX_TIMESTAMP(AcctStartTime) + AcctSessionTime '%b' } sqlcounter monthlycounter { counter-name = Monthly-Session-Time check-name = Max-Monthly-Session sqlmod-inst = sqlcca3 key = User-Name reset = monthly query = SELECT SUM(AcctSessionTime - GREATEST((%b - UNIX_TIMESTAMP(AcctStartTime)), 0)) FROM radacct WHERE UserName='%{%k}' AND UNIX_TIMESTAMP(AcctStartTime) + AcctSessionTime '%b' } //milver - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
RE: accounting to mysql database
I have no idea how this posting ending up on freeRADIUS!! Kirt -Original Message- From: Kirti S. Bajwa [mailto:[EMAIL PROTECTED] Sent: Wednesday, March 31, 2004 10:46 AM To: '[EMAIL PROTECTED]' Subject: RE: accounting to mysql database Hello List: I have installed and tested freeRADIUS 0.9.3 MySQL 5.?.? (latest dev version). Now I want to install test freeRADIUS MySQL with InnoDB tables. I have researched GOOGLE MySQL web site for information on installing MySQL with InnoDB tables without success. I would appreciate if somebody direct me to: What version of MySQL do I need to install (with freeRADIUS) so I can use InnoDB tables? I understand that installing MySQL with freeRADIUS requires a specific MySQL version? Is it correct? I would be more than happy to read the procedure if someone point me to the chapter in MySQL Doc! I have not been able to find out myself. Is it possible that there is no special separate procedure and that is why I am missing it? Thanks in advance. Kirt - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
accounting to mysql database
hello does anyone have example config of what Mr. Allan Dekok Suggestion? Have two accounting databases: One used by your RADIUS server fordaily/weekly/monthly accounting for "live" sessions, and another formulti-year backups. Alan DeKok. Thanks very much
Re: accounting to mysql database
This is a good suggestion. The MySQL schema should be changed, so that raddb is by default InnoDB. This is exactly the reason I have been using PostgreSQL for the last 6 or so years. Rather than using a different database for archived records just put the archived data into different tables. The SQL accounting system I built for Cistron Radius creates its tables as required automaticaly every month. I use a version of xlat that I wrote myself to define table and log names based on the timestamp of the event. I archive ancient detail tables, but keep summary tables for up to three years, because our customers want to check how their usage patterns change {I have no idea why, but when I changed it to a year I got lots of complaints}. This method works very well for my current system because no table holds more than one months data. When I move to FreeRadius in the near future I will have to come up with an inovative way to handle this problem. I have been helping the development team with the PostgreSQL driver while ensuring none of my entries break the MySQL driver. The main issue I come across with tables that span no more than one month, is that if a user is online past the end of the month, their time is accounted in the next month. I need to come up with a way to split the records that overlap the end of a month {or year} that does not damage the integrity of the record. I have been considering a termination code of Split or somthing like it indicating the record Continues into the next table, but I havn't yet decided how to indicate that a record is a Continuation from a previous table. It might be a good idea to have this type of functionality included with FreeRadius in some way. I am interested in other peoples opinions on this matter and would like to hear your ideas. Guy Jeff Warnica wrote: Not really a FreeRADIUS question, but anyway: For complex queries and/or queries with large result sets MySQL, using MyISAM tables, will produce a temporary table to fulfill the request. To ensure that it gets a consistent (temporary) table, MySQL locks the entire (real) table during this process, preventing writes. Reads can happen, but (I think) any reads that are complex enough to cause a temporary table will also queue up. InnoDB table types do not suffer from this limitation. Even if you can change table types it may be worth while to EXPLAIN some of your SELECT queries, and read the MySQL docs on general query optimizations. With about 5 minutes of effort, I managed to optimize someone else's queries from ~20s to ~5s. Still locks the table, but for significantly less time. On Thu, 2004-03-11 at 16:31, Mike Sturdee wrote: What might be causing this, or what could I do to resolve this? - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Re: accounting to mysql database
How about this scenario: (say it is March 1, SQL and col names from memory...) The 'live' table keeps up to two months (and a few hours) of data (January, February, March 1) Some time on March 1 you rotate out January CREATE TABLE jan_04 LIKE. INSERT INTO jan_04 SELECT * FROM radacct WHERE acctStartTime = 2004-02-01 UPDATE jan_04 SET acctStopTime = 2004-01-31 23:59:58, acctSessionTime = acctSessionTime - 1 WHERE acctStopTime = 2004-01-31 23:59:59 UPDATE jan_04 SET acctStopTime = 2004-01-31 23:59:59 WHERE acctStopTime 2004-01-31 23:59:59 UPDATE jan_04 SET acctSessionTime = UNIX_TIMESTAMP(TIMEDIFF(acctStopTime, acctStartTime) WHERE acctStopTime = 2004-01-31 23:59:59 DELETE FROM radacct WHERE acctStartTime = 2004-02-01 UPDATE radacct SET acctStarTime = 2004-02-01 00:00:00 WHERE acctStartTime 2004-02-01 00:00:00 UPDATE radacct SET acctSessionTime = UNIX_TIMESTAMP(TIMEDIFF(acctStopTime, acctStartTime) WHERE acctStarTime = 2004-02-01 00:00:00 You don't have to wait a month to do this, but that would pretty much guarantee that all of the to-be-rotated sessions have been closed. Any sessions that you see that end exactly at 23:59:59 on the last day of the month are ones that are have continuation into the next month. And yes, I recognize that this is the same as some y2k issues: 9/9/99, 12/31/99 meaning 'never' or 'infinite'.. But your only out a second. On Fri, 2004-03-12 at 13:37, Guy Fraser wrote: The main issue I come across with tables that span no more than one month, is that if a user is online past the end of the month, their time is accounted in the next month. I need to come up with a way to split the records that overlap the end of a month {or year} that does not damage the integrity of the record. I have been considering a termination code of Split or somthing like it indicating the record Continues into the next table, but I havn't yet decided how to indicate that a record is a Continuation from a previous table. - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
accounting to mysql database
I have radius set up to log accounting to a MySQL database. It currently holds a couple years worth of logging from several thousand users, so it's quite large.. Problem I'm having is if I do a select that will return a couple hundred entries, or anything other than the simplest of queries, radius will freeze until the query is sql complete, resulting in any auth requests essentially to be rejected. FreeRadius 0.9.3 MySQL Server 4.0.17 (MyISAM tables) What might be causing this, or what could I do to resolve this? Thanks, -Mike - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Re: accounting to mysql database
One solution may be to change your insert statements in sql.conf from Insert to Insert Delayed. you can read some here: http://www.mysql.com/doc/en/INSERT_DELAYED.html Post back to the list if it works. I'm no guru, so back up your sql.conf before making changes :) -Mike - Original Message - From: Mike Sturdee [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Thursday, March 11, 2004 3:31 PM Subject: accounting to mysql database I have radius set up to log accounting to a MySQL database. It currently holds a couple years worth of logging from several thousand users, so it's quite large.. Problem I'm having is if I do a select that will return a couple hundred entries, or anything other than the simplest of queries, radius will freeze until the query is sql complete, resulting in any auth requests essentially to be rejected. FreeRadius 0.9.3 MySQL Server 4.0.17 (MyISAM tables) What might be causing this, or what could I do to resolve this? Thanks, -Mike - 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: accounting to mysql database
Not really a FreeRADIUS question, but anyway: For complex queries and/or queries with large result sets MySQL, using MyISAM tables, will produce a temporary table to fulfill the request. To ensure that it gets a consistent (temporary) table, MySQL locks the entire (real) table during this process, preventing writes. Reads can happen, but (I think) any reads that are complex enough to cause a temporary table will also queue up. InnoDB table types do not suffer from this limitation. Even if you can change table types it may be worth while to EXPLAIN some of your SELECT queries, and read the MySQL docs on general query optimizations. With about 5 minutes of effort, I managed to optimize someone else's queries from ~20s to ~5s. Still locks the table, but for significantly less time. On Thu, 2004-03-11 at 16:31, Mike Sturdee wrote: What might be causing this, or what could I do to resolve this? - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Re: accounting to mysql database
Mike Sturdee [EMAIL PROTECTED] wrote: I have radius set up to log accounting to a MySQL database. It currently holds a couple years worth of logging from several thousand users, so it's quite large.. Problem I'm having is if I do a select that will return a couple hundred entries, or anything other than the simplest of queries, radius will freeze until the query is sql complete, resulting in any auth requests essentially to be rejected. Have two accounting databases: One used by your RADIUS server for daily/weekly/monthly accounting for live sessions, and another for multi-year backups. Alan DeKok. - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html