Kill sessions
Hey all, I got a bit of a problem. The setup we have here is a bit strange (I think). I dont have any access to any of the NAS's. Instead I got given a list of IP addresses of other radius servers , that was added to my clients.conf file. These servers then passes everything on to my server (Now that I think of it, this is probably a proxy setup ?) Anyways, my problem is as follows: Our internet connection went down for about 3 hours yesterday. I had quite a few customers connected during this time, which means that they disconnected while my freeradius box was inaccessible. As a result, I am sitting with quite a few entries that never received account stop packages (i think) and now those users cant get access again, because I have similtanious use set to 1. As a work around - I have just inccreased the similtanious use to 2. Now, finally my question: Is there a way to close all open connections ? Or at least fake the account stop packages ? Maybe a SQL query to run to close all open session in the database ? I dont know ? Anybody got any ideas how I would do this ? Thanks, Jacqueco Peenz
RE: SQL Query to get total bandwidth used per user per month.
-Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Graeme Lee Sent: 24 February 2005 02:52 AM To: freeradius-users@lists.freeradius.org Subject: Re: SQL Query to get total bandwidth used per user per month. Hyperlink Admin wrote: >Hi Guys, > >Ok, what I would like to do is the following: > >I would like to create a seperate radacct for each month. > >It would be nice to have them going radacct1, raddact2, and so on. and >then when it get to next year this time, just carry on with radacct13, >radacct14 and so on, otherwise Ill just restart with radacct1 next year. > >Does anybody know how I would go about setting this up ? Would it be >possible to do this automatically ? or would I have to change the >config file to write to a different radacct each month ? > > >>Maybe a second table for radacct_historical_data would be suitalbe? >>Move the data to the historical table and then delete it from your working radacct table. So for example: radacct and radacct_historical_data and then have a script just adding the data to the end of the radacct_historical_data table each month, and clearing the radacct table ? Would I do this with a SQL query ? If so, what would the query look like ? Then I can just create a script executing the query every month. > >Then, my second question: > >I need to get monthly total bandwidth transfer (both incomming and >outgoing >traffic) for each user. This I got figured out with the following SQL query: > >SELECT username, sum(acctinputoctets+acctoutputoctets) AS total FROM >radacct GROUP BY username; > >It works great, but to sit and filter through all the users will be a >pain each day. > >I need it to only print the users that is over a cetain amount. > >I have tried the following: > >SELECT username, sum(acctinputoctets+acctoutputoctets) AS total FROM >radacct WHERE sum(acctinputoctets+acctoutputoctets) > 3221225472 GROUP >BY username; > >and here is the error: > >ERROR (HY000): Invalid use of group function > > >>You need a sub-select >>SELECT * FROM (SELECT username, sum(acctinputoctets+acctoutputoctets) AS total FROM radacct GROUP BY username) AS foo >>radius-> WHERE total > 3221225472; Works great !!! Thank you ! > >Im sure there is a way to do it, but I have never really worked with MySQL >or any queries. What I got here, I patched together from reading around on >the net and on this list. > > >>I'm using postgresql. Let me rephrase -> Ive never used any sort of sql. Most Ive done with databases is a little access database. > >Last thing. And here I think I am actually pushing my luck. > >Will it be possible (when the previous query actaully works) to >automatically take all the users listed in the results of the previous query >and moved them from one group (profile) to another. > > >>Well I'd suggest triggers which get fired on updates on the radacct >>table, which update a separate table with username, month, and data >>(plus whatever else you need to keep on a monthly basis). Your trigger >>would have the benefit of being fired automatically rather than relying >>on human intervention. Or you could use cron, and have an external >>script which checks the db regularly, and massages the data according to >>your needs. Ok, im kinda lost. Triggers ? How would I add a trigger ? How does it work ? Sorry, Like I said, im kind new to this But thanks for the advise so far, I really appreciate it. > > > >The thing is, we are reselling ADSL accounts for the local Telco company >here, and they are very strict on capping accounts on 3GB. So in order for >us to be able to provide the service I need to check total usage for each >user on a daily basis and if he is over the cap, he needs to be moved to a >much slower service. > >I got the 2 different groups setup - capped and uncapped, and it is working >fine. > >I would really appreciate it if someone could help me. > >Thank you, > >Jacqueco Peenz > > > - 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: SQL Query to get total bandwidth used per user per month.
Hi Guys, Ok, what I would like to do is the following: I would like to create a seperate radacct for each month. It would be nice to have them going radacct1, raddact2, and so on. and then when it get to next year this time, just carry on with radacct13, radacct14 and so on, otherwise Ill just restart with radacct1 next year. Does anybody know how I would go about setting this up ? Would it be possible to do this automatically ? or would I have to change the config file to write to a different radacct each month ? Then, my second question: I need to get monthly total bandwidth transfer (both incomming and outgoing traffic) for each user. This I got figured out with the following SQL query: SELECT username, sum(acctinputoctets+acctoutputoctets) AS total FROM radacct GROUP BY username; It works great, but to sit and filter through all the users will be a pain each day. I need it to only print the users that is over a cetain amount. I have tried the following: SELECT username, sum(acctinputoctets+acctoutputoctets) AS total FROM radacct WHERE sum(acctinputoctets+acctoutputoctets) > 3221225472 GROUP BY username; and here is the error: ERROR (HY000): Invalid use of group function I have also tried the following variations: (with each error) SELECT username, sum(acctinputoctets+acctoutputoctets) AS total FROM radacct WHERE sum(acctinputoctets+acctoutputoctets) > 3*1024*1024*1024 GROUP BY username; ERROR (HY000): Invalid use of group function SELECT username,sum(acctinputoctets+acctoutputoctets) FROM radacct WHERE sum(acctinputoctets+acctoutputoctets) > 3*1024*1024*1024 GROUP BY username; ERROR (HY000): Invalid use of group function SELECT username,sum(acctinputoctets+acctoutputoctets) FROM radacct WHERE sum(acctinputoctets+acctoutputoctets) > 3221225472 GROUP BY username; ERROR (HY000): Invalid use of group function SELECT username,sum(acctinputoctets+acctoutputoctets) FROM radacct WHERE 'sum(acctinputoctets+acctoutputoctets)' > 3*1024*1024*1024 GROUP BY username; Empty set (0.00 sec) (not really error, but not what I need) SELECT username,sum(acctinputoctets+acctoutputoctets) FROM radacct WHERE 'sum(acctinputoctets+acctoutputoctets)' > 3221225472 GROUP BY username; Empty set (0.00 sec) (not really error, but not what I need) Im sure there is a way to do it, but I have never really worked with MySQL or any queries. What I got here, I patched together from reading around on the net and on this list. Last thing. And here I think I am actually pushing my luck. Will it be possible (when the previous query actaully works) to automatically take all the users listed in the results of the previous query and moved them from one group (profile) to another. The thing is, we are reselling ADSL accounts for the local Telco company here, and they are very strict on capping accounts on 3GB. So in order for us to be able to provide the service I need to check total usage for each user on a daily basis and if he is over the cap, he needs to be moved to a much slower service. I got the 2 different groups setup - capped and uncapped, and it is working fine. I would really appreciate it if someone could help me. Thank you, Jacqueco Peenz - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
dialup_admin - please help
Hi guys, Ok, I don't know what to do anymore. I have tried everyhting. I got it working yesterday, and most of today, then the problem came back. When I open one of the pages where I can enter the username or groupname I get admin,en filled in that field. When I turn on sql debug in admin.conf, all pages where where u can specify a username or password, something similar to this is on the screen: DEBUG(SQL,MYSQL DRIVER): Query: SELECT groupname FROM usergroup WHERE username = 'admin,en'; DEBUG(SQL,MYSQL DRIVER): Query It looks like it is getting the admin,en value from somewhere. I have tried working around it, but for example, when I want to edit a user, and I type the usename in the field and click edit user, they it comes up with a User [admin,en] could not be found. For some reason it is defaulting back to admin,en. I have even tried restarting my whole installation from scratch, fresh FreeBSD installation, re downloaded all src files, recompiling everyhting, and setting everything up from scratch. But I still get the same thing. Is it maybe the FreeBSD ? Or version of PHP or something like that ? What I did to fix it twice is to remove the whole /usr/local/dialup_admin directory and redo the whole installation from the freeradius tar file. Then it worked fine, and then all of the sudden it is back. I didnt change any config file, or made any other changes. I know someone mentioned trying to get the latest version via cvs, but when I followed the instructions in the HOWTO file, it downloaded a bunch of file, but it is mostly garbage. Commands I used: shell> cvs -d:pserver:[EMAIL PROTECTED]:/cvsroot/dialup-admin login * When prompted for a password simply press the Enter Key shell> cvs -z3 -d:pserver:[EMAIL PROTECTED]:/cvsroot/dialup-admin co dialup_admin I then replace the /usr/local/dialup_admin directory with the one that downloaded and then the whole page is just garbage. I have followed the HOWTO step-by-step, and still have no joy. If anybody got any idea what else I can try, please let me know. Thanks Jacqueco Peenz - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
RE: Subscription Analysis
Thanks a lot. LOL.I was actually looking at it yesterday, reminding myself to comment it out when I get this thing working. Is there a wait to use like a bandwidth counter in the same way as the time counter ? Thanks Jacqueco Peenz -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Mike-Olumide, Johnson Sent: 16 February 2005 07:55 PM To: freeradius-users@lists.freeradius.org Subject: Re: Subscription Analysis Counter defaults ..The last few lines in admin.conf to be precise. - Original Message - From: "Hyperlink Admin" <[EMAIL PROTECTED]> To: Sent: Wednesday, February 16, 2005 5:29 PM Subject: Subscription Analysis > Hey again, > > I got this sorted out - I downloaded freeradius from the website and > extracted the dialup_admin from there. And now its working. The problem must > have been with the version of freeradius in the FreeBSD ports. > > Just one more question, when I look at a user I created, it says that user > can only log in for 4 hours per day and 20 hours per week, or something like > that. > > Where would I edit (remove) this ? > > Would it be possible to change this from a time restriction to a bandwidth > usage restriction ? > > If so, could someone point me in the right direction ? > > Im not asking for a step-by-step, just point me, and ill try it myself. > > Thanks alot > > > Jacqueco Peenz > > -----Original Message- > From: [EMAIL PROTECTED] > [mailto:[EMAIL PROTECTED] On Behalf Of Hyperlink > Admin > Sent: 16 February 2005 02:20 PM > To: freeradius-users@lists.freeradius.org > Subject: RE: Freeradius + mysql + dial-up admin - strange error (admin,en) > > Hi there, > > Just an update on my previous question: > > I have tured on sql debugging, and now I get the following line when I click > on ad user: > > DEBUG(SQL,MYSQL DRIVER): Query: SELECT groupname FROM usergroup WHERE > username = 'admin,en'; DEBUG(SQL,MYSQL DRIVER): Query Result: > > > Maybe it means more to u guys than to me. > > Thanks again ! > > Cheerz > > > Jacqueco Peenz > > -Original Message- > From: [EMAIL PROTECTED] > [mailto:[EMAIL PROTECTED] On Behalf Of Hyperlink > Admin > Sent: 16 February 2005 11:49 AM > To: freeradius-users@lists.freeradius.org > Subject: Freeradius + mysql + dial-up admin - strange error (admin,en) > > Hi Guys, > > I am running freeradius 1.01, Freebsd 5.3, MySQL 4.15, PHP4 and apache 1.3 > > I have everything setup more or less correctly (I think), but I am > experiencing a weird problem. > > I have checked, and double checked my config files, and cannot see anything > in there, that would cause this error. > > When I open dialupadmin webpage and try to add a user or a group I always > get 'admin,en' in the username or group name field. > > I then replace it with the propper username or group name, and fill in all > the rest of the required fields. When I click on submit or add, the user is > created sucessfully, but with the wrong username or groupname. It defaults > back to admin,en as the username or group name. When I then try to add > another user or group, the same thing happens and I change the name again, > but then it tell me user (or group) admin,en already exsists. > > Could anybody please help me to try sort this problem out ? I really need to > get this up and runing soon. > > Thank you, > > Jacqueco Peenz > > > > > - > 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 > __ Do you Yahoo!? All your favorites on one personal page Try My Yahoo! http://my.yahoo.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
Subscription Analysis
Hey again, I got this sorted out - I downloaded freeradius from the website and extracted the dialup_admin from there. And now its working. The problem must have been with the version of freeradius in the FreeBSD ports. Just one more question, when I look at a user I created, it says that user can only log in for 4 hours per day and 20 hours per week, or something like that. Where would I edit (remove) this ? Would it be possible to change this from a time restriction to a bandwidth usage restriction ? If so, could someone point me in the right direction ? Im not asking for a step-by-step, just point me, and ill try it myself. Thanks alot Jacqueco Peenz -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Hyperlink Admin Sent: 16 February 2005 02:20 PM To: freeradius-users@lists.freeradius.org Subject: RE: Freeradius + mysql + dial-up admin - strange error (admin,en) Hi there, Just an update on my previous question: I have tured on sql debugging, and now I get the following line when I click on ad user: DEBUG(SQL,MYSQL DRIVER): Query: SELECT groupname FROM usergroup WHERE username = 'admin,en'; DEBUG(SQL,MYSQL DRIVER): Query Result: Maybe it means more to u guys than to me. Thanks again ! Cheerz Jacqueco Peenz -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Hyperlink Admin Sent: 16 February 2005 11:49 AM To: freeradius-users@lists.freeradius.org Subject: Freeradius + mysql + dial-up admin - strange error (admin,en) Hi Guys, I am running freeradius 1.01, Freebsd 5.3, MySQL 4.15, PHP4 and apache 1.3 I have everything setup more or less correctly (I think), but I am experiencing a weird problem. I have checked, and double checked my config files, and cannot see anything in there, that would cause this error. When I open dialupadmin webpage and try to add a user or a group I always get 'admin,en' in the username or group name field. I then replace it with the propper username or group name, and fill in all the rest of the required fields. When I click on submit or add, the user is created sucessfully, but with the wrong username or groupname. It defaults back to admin,en as the username or group name. When I then try to add another user or group, the same thing happens and I change the name again, but then it tell me user (or group) admin,en already exsists. Could anybody please help me to try sort this problem out ? I really need to get this up and runing soon. Thank you, Jacqueco Peenz - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
RE: Freeradius + mysql + dial-up admin - strange error (admin,en)
Hi There, Thanx for the reply. I am using the version that came with freeradius 1.0.1 I have not made any changes to any of the pages, except the admin.conf file I tried to get get the latest CVS version with the method in the HOWTO. I copied the files to /usr/local/dialup_admin, but then trying to access the webpage, I got a file not found (buttons.html) error where the buttons should be. Don't know if I did something wrong ? Thanks Jacqueco Peenz -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Kostas Kalevras Sent: 16 February 2005 03:03 PM To: freeradius-users@lists.freeradius.org Subject: Re: Freeradius + mysql + dial-up admin - strange error (admin,en) On Wed, 16 Feb 2005, Hyperlink Admin wrote: > Hi Guys, > > I am running freeradius 1.01, Freebsd 5.3, MySQL 4.15, PHP4 and apache > 1.3 > > I have everything setup more or less correctly (I think), but I am > experiencing a weird problem. > > I have checked, and double checked my config files, and cannot see > anything in there, that would cause this error. > > When I open dialupadmin webpage and try to add a user or a group I > always get 'admin,en' in the username or group name field. > > I then replace it with the propper username or group name, and fill in > all the rest of the required fields. When I click on submit or add, > the user is created sucessfully, but with the wrong username or > groupname. It defaults back to admin,en as the username or group name. > When I then try to add another user or group, the same thing happens > and I change the name again, but then it tell me user (or group) admin,en already exsists. > > Could anybody please help me to try sort this problem out ? I really > need to get this up and runing soon. What version of dialupadmin are you using? Try using the latest CVS version. Have you done any changes in any of the pages? > > Thank you, > > Jacqueco Peenz > > > > > - > 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: Freeradius + mysql + dial-up admin - strange error (admin,en)
Hi there, Just an update on my previous question: I have tured on sql debugging, and now I get the following line when I click on ad user: DEBUG(SQL,MYSQL DRIVER): Query: SELECT groupname FROM usergroup WHERE username = 'admin,en'; DEBUG(SQL,MYSQL DRIVER): Query Result: Maybe it means more to u guys than to me. Thanks again ! Cheerz Jacqueco Peenz -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Hyperlink Admin Sent: 16 February 2005 11:49 AM To: freeradius-users@lists.freeradius.org Subject: Freeradius + mysql + dial-up admin - strange error (admin,en) Hi Guys, I am running freeradius 1.01, Freebsd 5.3, MySQL 4.15, PHP4 and apache 1.3 I have everything setup more or less correctly (I think), but I am experiencing a weird problem. I have checked, and double checked my config files, and cannot see anything in there, that would cause this error. When I open dialupadmin webpage and try to add a user or a group I always get 'admin,en' in the username or group name field. I then replace it with the propper username or group name, and fill in all the rest of the required fields. When I click on submit or add, the user is created sucessfully, but with the wrong username or groupname. It defaults back to admin,en as the username or group name. When I then try to add another user or group, the same thing happens and I change the name again, but then it tell me user (or group) admin,en already exsists. Could anybody please help me to try sort this problem out ? I really need to get this up and runing soon. Thank you, Jacqueco Peenz - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Freeradius + mysql + dial-up admin - strange error (admin,en)
Hi Guys, I am running freeradius 1.01, Freebsd 5.3, MySQL 4.15, PHP4 and apache 1.3 I have everything setup more or less correctly (I think), but I am experiencing a weird problem. I have checked, and double checked my config files, and cannot see anything in there, that would cause this error. When I open dialupadmin webpage and try to add a user or a group I always get 'admin,en' in the username or group name field. I then replace it with the propper username or group name, and fill in all the rest of the required fields. When I click on submit or add, the user is created sucessfully, but with the wrong username or groupname. It defaults back to admin,en as the username or group name. When I then try to add another user or group, the same thing happens and I change the name again, but then it tell me user (or group) admin,en already exsists. Could anybody please help me to try sort this problem out ? I really need to get this up and runing soon. Thank you, Jacqueco Peenz - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
