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
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
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
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
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
Those of you that use mysql with freeradius, can anyone recommend some software for linux to process mysql radacct table logs? Do you just roll your own scripts to query the logs and make reports? Seems simple enough, but "what are others doing?" is always a good question :-) if dialup_admin is too technical for you, you can decide to create your own customized PHP/Perl scripts to do whatever output you like. //milver - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Re: mysql accounting
duh! I didn't know it was there. Thanks. Ken A Milver S. Nisay wrote: Those of you that use mysql with freeradius, can anyone recommend some software for linux to process mysql radacct table logs? Do you just roll your own scripts to query the logs and make reports? Seems simple enough, but "what are others doing?" is always a good question :-) if dialup_admin is too technical for you, you can decide to create your own customized PHP/Perl scripts to do whatever output you like. //milver - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Re: mysql accounting
i am writing my own program to get them in human-readable form:) Edgars Ken A wrote: Those of you that use mysql with freeradius, can anyone recommend some software for linux to process mysql radacct table logs? Do you just roll your own scripts to query the logs and make reports? Seems simple enough, but "what are others doing?" is always a good question :-) Thanks, Ken A - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Re: mysql accounting
Edgars wrote: i am writing my own program to get them in human-readable form:) Edgars Yep. I made some changes that make it easier for me to start from scratch with a language I'm more familiar with (perl) than to modify dialupadmin to do what I want, especially since I'm not very good with php, and there are many things in dialupadmin I would want to change. I added a couple of columns to the radacct table, so my records include several Ascend attributes not in the standard table: (Ascend-Disconnect-Cause, Ascend-XmitRate, Ascend-DataRate). And, I was getting duplicate STOP records in the radacct table, so I also put a unique index on (sessionid,username,nasipaddress) and changed the INSERT STOP record in sql.conf to a "REPLACE INTO" instead of "INSERT INTO" and that seems to have resolved the problem. Ken A Ken A wrote: Those of you that use mysql with freeradius, can anyone recommend some software for linux to process mysql radacct table logs? Do you just roll your own scripts to query the logs and make reports? Seems simple enough, but "what are others doing?" is always a good question :-) Thanks, Ken A - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Re: mysql accounting
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
Hello,
I do NOT want to be rude, but sometimes searching the archives helps A LOT !
BELIEVE ME ! But for the lazy developers among you people - here is the part
that describes the needed feature. Thanks to Jamal ( of course ). This is
copy paste - so do NOT blame me ;)
START
Create a table in the radius schema (called "fails_log") to include three
columns: trial_date, username, password.
Create a function in the database (called "fails"). The main statements
which you should write are
"
fails ( username1 in out char, password1 in char) return char is
v_user char:='';
v_password:='';
begin
select username , value into v_user from radcheck where
attribute='password' and username= username1 and password=password1;
if v_user = '' then insert into fails_log values
(sysdate,username1,password1);
else return v_user;
end if;
end;
"
Update authorize_ceck_query module in sql.conf file to be as follows:
authorize_check_query = "SELECT id,Username,Attribute,Value,op FROM
${authcheck_table} WHERE Username =(select
fails('%{SQL-User-Name}','%{User-Password}') from dual) ORDER BY id"
That is all. Then you can find all failed logs inside the new created table
fails_log.
END
Regards,
Edvin Seferovic
-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED]
g] On Behalf Of Sean Taylor
Sent: Donnerstag, 15. Juni 2006 07:13
To: freeradius-users@lists.freeradius.org
Subject: mysql accounting
I have a quick question on the mysql accounting. I am working on my own
interface for managing the freeradius+mysql setup. Everything is
working great, I can view all my users, see who's connected, add new
users, manage static vs. dynamic IP's, etc..The problem is it doesnt
seem to log authentication failures into the radacct table. It logs all
the successes just fine, but it would be very beneficial to have it log
the failures too. I have the sql module turned on in the accounting
section of the config and have uncommented all of the accounting
queries. Any help appreciated.
Thanks
Sean Taylor
Systems Administrator
Valutel Communications
-
List info/subscribe/unsubscribe? See
http://www.freeradius.org/list/users.html
-
List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Re: mysql accounting
I actually did search the archives quite a bit, just not for the right
thing apparently. I usually won't post to a list until I've exhausted
all other options. Anyways, has someone actually turned this giberish
into working SQL code they are willing to share?
Thanks
Seferovic Edvin wrote:
Hello,
I do NOT want to be rude, but sometimes searching the archives helps A LOT !
BELIEVE ME ! But for the lazy developers among you people - here is the part
that describes the needed feature. Thanks to Jamal ( of course ). This is
copy paste - so do NOT blame me ;)
START
Create a table in the radius schema (called "fails_log") to include three
columns: trial_date, username, password.
Create a function in the database (called "fails"). The main statements
which you should write are
"
fails ( username1 in out char, password1 in char) return char is
v_user char:='';
v_password:='';
begin
select username , value into v_user from radcheck where
attribute='password' and username= username1 and password=password1;
if v_user = '' then insert into fails_log values
(sysdate,username1,password1);
else return v_user;
end if;
end;
"
Update authorize_ceck_query module in sql.conf file to be as follows:
authorize_check_query = "SELECT id,Username,Attribute,Value,op FROM
${authcheck_table} WHERE Username =(select
fails('%{SQL-User-Name}','%{User-Password}') from dual) ORDER BY id"
That is all. Then you can find all failed logs inside the new created table
fails_log.
END
Regards,
Edvin Seferovic
-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED]
g] On Behalf Of Sean Taylor
Sent: Donnerstag, 15. Juni 2006 07:13
To: freeradius-users@lists.freeradius.org
Subject: mysql accounting
I have a quick question on the mysql accounting. I am working on my own
interface for managing the freeradius+mysql setup. Everything is
working great, I can view all my users, see who's connected, add new
users, manage static vs. dynamic IP's, etc..The problem is it doesnt
seem to log authentication failures into the radacct table. It logs all
the successes just fine, but it would be very beneficial to have it log
the failures too. I have the sql module turned on in the accounting
section of the config and have uncommented all of the accounting
queries. Any help appreciated.
Thanks
Sean Taylor
Systems Administrator
Valutel Communications
-
List info/subscribe/unsubscribe? See
http://www.freeradius.org/list/users.html
-
List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
-
List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Re: mysql accounting
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 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
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
issue with mysql accounting
Hi. I have WIFI network based on Cisco Aironet 1130 with WPA/TKIP - EAP-PEAP. Radius server is freeradius (just upgraded to 1.1.7) with mysql backend (users,accounting). Everything worked fine for maybe 2 years. Just atm i have a new problem. Some APs got new IOS and i noticed that now is not sending User-Name like [EMAIL PROTECTED] but is sending MAC address as Username. This "MAC address" username i got also as system enviroment variables. On other APs with old IOS i got also problem. Some users got some new software for connecting to the network(suplicant) where they can set some "fake" outer username. Ofc users can authenticate against radius without problems. I think its because inner authentication variables (MS-CHAPv2 login name?) which freeradus use for authentication. 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 ? Back to MYSQL. Mysql atm is logging "fake usernames" or MAC addresses as UserName into radacc table. BUT mysql is logging correct username (inner) into radpostauth. Any chance howto solve this problem ? I want to log username like for radpostauth. Also want this username as system enviroment variable so i can make some start/stop scripts where i can use it. 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). Tyvm for help. 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
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/
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
And what accounting is it suposed to do with the failed packet???
That value (15) most likely isn't defined in the freeradius dictionary.
And even if it was no sql query is defined in sql.conf for the failed
packet. And most likely no logic in rlm_sql.
Ivan Kalik
Kalik Informatika ISP
Dana 21/1/2008, "A.smith" <[EMAIL PROTECTED]> piše:
>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_chann
Re: MySQL accounting issue
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
Re: MySQL accounting issue
A.smith wrote: > 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? No. OpenSER has chosen to ignore the RFC's, and to send garbage accounting packets to FreeRADIUS. If you want this to work, you will need to patch the SQL module. Alan DeKok. - 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
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
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" Sent: Monday, January 21, 2008 4:40 PM Subject: Re: MySQL accounting issue Ok, Im seeing this from radiusd: rad_recv: Accounting-Request packet from host 89.107.16.10:35563, id=252, length=145 Acct-Status-Type = Failed Service-Type = IAPP-Register Attr-102 = 0x0194 Error-Cause = 32 Event-Timestamp = "Jan 21 2008 15:53:44 GMT" Attr-105 = 0x61733264636462613764 Acct-Session-Id = "[EMAIL PROTECTED]" Attr-108 = 0x38392e3130372e31362e39 Attr-109 = 0x35303630 NAS-Port = 5060 Acct-Delay-Time = 0 NAS-IP-Address = 89.107.16.10 Processing the preacct section of radiusd.conf modcall: entering group preacct for request 0 modcall[preacct]: module "preprocess" returns noop for request 0 rlm_acct_unique: WARNING: Attribute User-Name was not found in request, unique ID MAY be inconsistent rlm_acct_unique: Hashing 'NAS-Port = 5060,Client-IP-Address = 89.107.16.10,NAS-IP-Address = 89.107.16.10,Acct-Session-Id = "[EMAIL PROTECTED]",' rlm_acct_unique: Acct-Unique-Session-ID = "df36632bb92d5086". modcall[preacct]: module "acct_unique" returns ok for request 0 rlm_realm: Proxy reply, or no User-Name. Ignoring. modcall[preacct]: module "suffix" returns noop for request 0 modcall[preacct]: module "files" returns noop for request 0 modcall: leaving group preacct (returns ok) for request 0 Processing the accounting section of radiusd.conf modcall: entering group accounting for request 0 modcall[accounting]: module "unix" returns noop for request 0 radius_xlat: '/usr/local/var/log/radius/radutmp' rlm_radutmp: NAS OpenSER port 5060 unknown packet type 15) modcall[accounting]: module "radutmp" returns noop for request 0 radius_xlat: '' radius_xlat: '' radius_xlat: '/usr/local/var/log/radius/sqltrace.sql' rlm_sql (sql): Reserving sql socket id: 3 rlm_sql (sql): Released sql socket id: 3 modcall[accounting]: module "sql" returns ok for request 0 modcall: leaving group accounting (returns ok) for request 0 Sending Accounting-Response of id 252 to 89.107.16.10 port 35563 Finished request 0 Going to the next request --- Walking the entire request list --- Waking up in 6 seconds... --- Walking the entire request list --- Cleaning up request 0 ID 252 with timestamp 4794be17 Nothing to do. Sleeping until we see a request. - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Re: MySQL accounting issue
Dana 21/1/2008, "A.smith" <[EMAIL PROTECTED]> piše: >Also, regarding radius 1.x I now have a patch which allows this type of >record. >However next issue is that with accounting set to sql in radiusd.conf the >SQL statements are being written just to >/usr/local/var/log/radius/sqltrace.sql >and nothing is actually put in the database... What have I done wrong? :P > >cheers Andy. > > >Message sent using UK Grid Webmail 2.7.9 > > >- >List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html > > - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Re: MySQL accounting 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
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
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 authent
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
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
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
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
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
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 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
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
RE: mysql accounting connect speeds
Yes, and the AscendDataRate too.
I get the inserts fine on the start packet and the data goes right in as
suppose too.
all works fine this way for our GlobalPOPS and all data shows up and into sql
using this line in the start
---
accounting_start_query = "INSERT into ${acct_table1} (AcctSessionId,
AcctUniqueId, UserName, Realm, NASIPAddress, NASPortId, NASPortType,
AcctStartTime, AcctStopTime, AcctSessionTime, AcctAuthentic, ConnectInfo_start,
ConnectInfo_stop, AcctInputOctets, AcctOutputOctets, CalledStationId,
CallingStationId, AcctTerminateCause, ServiceType, FramedProtocol,
FramedIPAddress, AcctStartDelay, AscendDataRate, USRConnectSpeed,
AcctStopDelay) values('%{Acct-Session-Id}', '%{Acct-Unique-Session-Id}',
'%{SQL-User-Name}', '%{Realm}', '%{NAS-IP-Address}', '%{NAS-Port}',
'%{NAS-Port-Type}', '%S', '0', '0', '%{Acct-Authentic}', '%{Connect-Info}', '',
'0', '0', '%{Called-Station-Id}', '%{Calling-Station-Id}', '',
'%{Service-Type}', '%{Framed-Protocol}', '%{Framed-IP-Address}',
'%{Acct-Delay-Time}', '%{Ascend-Xmit-Rate}', '%{USR-Connect-Speed}', '0')"
---
data goes right into mysql tables
But YNP for some reason most miss the start, so I thought maybe I could grab
them on the update query cause i see one or the other on in update packet, for
ynp so then i would have what i need, but as i stated this errors out with the
error i mentioned below trying to do this
Jeff
_
From: Hugh Messenger [mailto:[EMAIL PROTECTED]
To: 'FreeRadius users mailing list' [mailto:[EMAIL PROTECTED]
Sent: Mon, 16 Jul 2007 11:40:53 -0400
Subject: RE: mysql accounting connect speeds
Jeff said:
> USRConnectSpeed = '%{USR-Connect-Speed}' \
Did you actually add a USRConnectSpeed column to the radacct table? There
isn't one by default.
-- hugh
-
List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
-
List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Re: mysql accounting connect speeds
Jeff wrote: > Mon Jul 16 11:23:22 2007 : Error: rlm_sql (sql): Couldn't update SQL > accounting ALIVE record - You have an error in your SQL syntax; check > the manual that corresponds to your MySQL server version for the right > syntax to use near 'AscendDataRate = '24000' USRConnectSpeed = > '' WHERE AcctSess' at line 1 You need a comma between data items: 'AscendDataRate = '24000', USRConnectSpeed ='' WHERE AcctSess' ^^^ -- Dennis Skinner Systems Administrator BlueFrog Internet http://www.bluefrog.com - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Re: mysql accounting connect speeds
ok heres what i have now
accounting_update_query = "UPDATE ${acct_table1} \
SET FramedIPAddress = '%{Framed-IP-Address}', \
AcctSessionTime = '%{Acct-Session-Time}', \
AcctInputOctets = '%{Acct-Input-Octets}', \
AcctOutputOctets = '%{Acct-Output-Octets}' \
AscendDataRate = '%{Ascend-Data-Rate}', \
USRConnectSpeed = '%{USR-Connect-Speed}' \
WHERE AcctSessionId = '%{Acct-Session-Id}' \
AND UserName = '%{SQL-User-Name}' \
AND NASIPAddress= '%{NAS-IP-Address}'"
an heres the new error
Mon Jul 16 12:49:19 2007 : Error: rlm_sql (sql): Couldn't update SQL accounting
ALIVE record - You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near
'AscendDataRate = '19200', USRConnectSpeed = '' WHERE
AcctSes' at line 1
Mon Jul 16 12:49:35 2007 : Error: rlm_sql (sql): Couldn't update SQL accounting
ALIVE record - You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near
'AscendDataRate = '19200', USRConnectSpeed = '' WHERE
AcctSes' at line 1
Mon Jul 16 12:49:40 2007 : Error: rlm_sql (sql): Couldn't update SQL accounting
ALIVE record - You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near
'AscendDataRate = '19200', USRConnectSpeed = '' WHERE
AcctSes' at line 1
Mon Jul 16 12:49:59 2007 : Error: rlm_sql (sql): Couldn't update SQL accounting
ALIVE record - You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near
'AscendDataRate = '19200', USRConnectSpeed = '' WHERE
AcctSes' at line
_
From: Dennis Skinner [mailto:[EMAIL PROTECTED]
To: FreeRadius users mailing list [mailto:[EMAIL PROTECTED]
Sent: Mon, 16 Jul 2007 11:59:34 -0400
Subject: Re: mysql accounting connect speeds
Jeff wrote:
> Mon Jul 16 11:23:22 2007 : Error: rlm_sql (sql): Couldn't update SQL
> accounting ALIVE record - You have an error in your SQL syntax; check
> the manual that corresponds to your MySQL server version for the right
> syntax to use near 'AscendDataRate = '24000' USRConnectSpeed =
> '' WHERE AcctSess' at line 1
You need a comma between data items:
'AscendDataRate = '24000', USRConnectSpeed ='' WHERE AcctSess'
^^^
--
Dennis Skinner
Systems Administrator
BlueFrog Internet
http://www.bluefrog.com
-
List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
-
List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Re: mysql accounting connect speeds
Yes. You are missing commas before AscendDataRate and USRConnectSpeed expressions that you have added to the update query. Ivan Kalik Kalik Informatika ISP Dana 16/7/2007, "Dennis Skinner" <[EMAIL PROTECTED]> piše: >Jeff wrote: >> Mon Jul 16 11:23:22 2007 : Error: rlm_sql (sql): Couldn't update SQL >> accounting ALIVE record - You have an error in your SQL syntax; check >> the manual that corresponds to your MySQL server version for the right >> syntax to use near 'AscendDataRate = '24000' USRConnectSpeed = >> '' WHERE AcctSess' at line 1 > >You need a comma between data items: > >'AscendDataRate = '24000', USRConnectSpeed ='' WHERE AcctSess' >^^^ > >-- >Dennis Skinner >Systems Administrator >BlueFrog Internet >http://www.bluefrog.com >- >List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html > > - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Re: mysql accounting connect speeds
Jeff wrote:
> AcctOutputOctets = '%{Acct-Output-Octets}' \
Need comma on live above. This is a MySQL issue, not a FR issue.
Please read the MySQL docs if you don't understand how to create a valid
query.
--
Dennis Skinner
Systems Administrator
BlueFrog Internet
http://www.bluefrog.com
-
List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Re: mysql accounting connect speeds
its not i do not understand its just these stupid bi-focals i have a hard time
seeing.
I overlooked that, sorry for being a blind idiot
_
From: Dennis Skinner [mailto:[EMAIL PROTECTED]
To: FreeRadius users mailing list [mailto:[EMAIL PROTECTED]
Sent: Mon, 16 Jul 2007 13:54:02 -0400
Subject: Re: mysql accounting connect speeds
Jeff wrote:
> AcctOutputOctets = '%{Acct-Output-Octets}' \
Need comma on live above. This is a MySQL issue, not a FR issue.
Please read the MySQL docs if you don't understand how to create a valid
query.
--
Dennis Skinner
Systems Administrator
BlueFrog Internet
http://www.bluefrog.com
-
List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
-
List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Re: 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 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
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
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
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
Re: MySQL Accounting in Freeradius
Andrey <[EMAIL PROTECTED]> wrote: > I have a question about the uniqueness of the "AcctUniqueId" field. Everytime > the user connects, the id is always the same. "AcctSessionId" is always the > same as well. Then there isn't much you can do. Some NAS vendors re-use Acct-Session-Id's, even though the RFC's say the Id's should be unique. In order to work around this, we wrote the acct_unique module, which takes *additional* data, and tries to create a more unique Id. If the AcctUniqueId field is always the same, then the accounting request contain all the same information, or are missing some information. See the acct_unique configuration for a list of attributes it's using. > For some reason I doubt it, but thought I'd ask just to make > sure. If that's a bug, how would I go about fixing it? Get your NAS to send real accounting data. Barring that, there's nothing you can do. As a related question: Pretend you're the RADIUS server, looking at two or more accounting requests. How can you tell different sessions apart of the Acct-Session-Id is the same, and all other information is the same? If you can't tell the difference, neither can FreeRADIUS. Alan DeKok. - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Re: mysql Accounting not working
on the sql.conf add "sqltrace = yes"
start up with "radiusd -X" and see what happen.
You test the mysql conneciotn from the SAME host that freeradius?
On 7/14/05, Leonardo Valente <[EMAIL PROTECTED]> wrote:
> Hello,
>
> I use Debian Linux Sarge, kernel 2.6.8-2(368),
> freeradius 1.0.2, and I'm trying to configure
> freeradius + mysql Accounting.
> I created database from script "db_mysql.sql", and
> created a user to access database with full
> privileges. I tested to access database from another
> host and it's fine.
> I can get authentication from localhost and another
> host. BUT freeradius is not insert accounting
> information in database.
> I used "freeradius -X" to get some debug information
> and I can't see it doing INSERT. But I know that
> freeradius connect into database when I start the
> daemon, I could see that in "mysql.log".
> I looked into "mysql.log" and freeradius is not doing
> INSERT.
> Database name, database username, password and host
> are set in "sql.conf". And "accounting" tag from
> "radiusd.conf" is:
> ---
> accounting {
> sql
> }
>
>
> Someone can tell me if I forgot some configuration? I
> just wanna do Mysql Accounting...
>
> Thanks in advance
>
>
>
> -
> "Dize-me tuas comunidades e te direi quem és..."
> Leonardo Valente
> MSN: [EMAIL PROTECTED]
>
> __
> Converse com seus amigos em tempo real com o Yahoo! Messenger
> http://br.download.yahoo.com/messenger/
> -
> List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
>
-
List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Re: mysql Accounting not working
in my "sql.conf":
-
sqltrace = yes
sqltracefile = ${logdir}/sqltrace.sql
-
freeradius user has privileges to write in ${logdir},
but this file not even is created when I do
"freeradius -X" or "freeradius -x".
Yes, I tested mysql connection from SAME host that
free radius. Like I said, I can see in "mysql.log"
freeradius connected 5 times, when I start up daemon,
but I can't see INSERT TO...
Can debian freeradius package not be compiled with
mysql account support?
I don't have ideas anymore... I think I'll try compile
from source code... and test...
Thanks
--- Mario Alberto Cruz Gartner <[EMAIL PROTECTED]>
escreveu:
> on the sql.conf add "sqltrace = yes"
>
> start up with "radiusd -X" and see what happen.
>
> You test the mysql conneciotn from the SAME host
> that freeradius?
>
> On 7/14/05, Leonardo Valente
> <[EMAIL PROTECTED]> wrote:
> > Hello,
> >
> > I use Debian Linux Sarge, kernel 2.6.8-2(368),
> > freeradius 1.0.2, and I'm trying to configure
> > freeradius + mysql Accounting.
> > I created database from script "db_mysql.sql", and
> > created a user to access database with full
> > privileges. I tested to access database from
> another
> > host and it's fine.
> > I can get authentication from localhost and
> another
> > host. BUT freeradius is not insert accounting
> > information in database.
> > I used "freeradius -X" to get some debug
> information
> > and I can't see it doing INSERT. But I know that
> > freeradius connect into database when I start the
> > daemon, I could see that in "mysql.log".
> > I looked into "mysql.log" and freeradius is not
> doing
> > INSERT.
> > Database name, database username, password and
> host
> > are set in "sql.conf". And "accounting" tag from
> > "radiusd.conf" is:
> > ---
> > accounting {
> > sql
> > }
> >
> >
> > Someone can tell me if I forgot some
> configuration? I
> > just wanna do Mysql Accounting...
> >
> > Thanks in advance
> >
> >
> >
> > -
> > "Dize-me tuas comunidades e te direi quem és..."
> > Leonardo Valente
> > MSN: [EMAIL PROTECTED]
> >
> > __
> > Converse com seus amigos em tempo real com o
> Yahoo! Messenger
> > http://br.download.yahoo.com/messenger/
> > -
> > List info/subscribe/unsubscribe? See
> http://www.freeradius.org/list/users.html
> >
>
> -
> List info/subscribe/unsubscribe? See
> http://www.freeradius.org/list/users.html
>
-
"Dize-me tuas comunidades e te direi quem és..."
Leonardo Valente
MSN: [EMAIL PROTECTED]
___
Yahoo! Acesso Grátis - Internet rápida e grátis.
Instale o discador agora! http://br.acesso.yahoo.com/
-
List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Re: Mysql, Accounting and DialupAdmin
On Wed, Nov 7, 2012 at 10:16 PM, Erich Titl 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 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 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 and DialupAdmin
On Thu, Nov 8, 2012 at 2:43 PM, Erich Titl 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 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 Un
Re: Mysql, Accounting and DialupAdmin
On Thu, Nov 8, 2012 at 4:27 PM, Erich Titl wrote: > I _guess_ it shows some accounting > > rad_recv: Accounting-Request packet from host 194.124.158.62 port 47037, > id=165, length=135 > Acct-Session-Id = "509ACAB9-000F" > Acct-Status-Type = Start Do some stuff first with the client (e.g. browsing), then disconnect. Look for accounting stop packet. If it doesn't show Acct-In-Octets and friends, then your AP is seriously broken. -- Fajar - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Re: MySQL accounting and 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.5&content-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.5&content-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
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: 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.5&content-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
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: 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
