RE: Accounting CISCO MySQL problem! Need HELP!! URGENT!
Hi there,
I've this problem solved,
but still follow problem:
When I account only stop packets, I have succesful call records in the
MySQL, where is unsuccessful?
What I need change?
Oleg
Please help urgent!
-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] Behalf Of Oleg
Ustinov
Sent: Thursday, July 17, 2003 7:35 PM
To: [EMAIL PROTECTED]
Subject: Accounting CISCO MySQL problem! Need HELP!! URGENT!
Hi there,
I bukld CISCO h323 accounting using FreeRadius,
first problem is that I do accounting start-stop packets,
It works well, it is full info in radacct files, but I have the problem with
MySQL.
I have no disconnect couse in MySQL base!
It works only when I make only Stop packets accounting.
I've changed sql.conf file like this:
accounting_onoff_query = "UPDATE ${acct_table1} SET AcctStopTime='%S',
AcctSessionTime=unix_timestamp('%S') - unix_timestamp AcctStartTime),
AcctTerminateCause='%{Acct-Terminate-Cause}', AcctStopDelay =
%{Acct-Delay-Time} WHERE AcctSessionTime=0 AND AcctStopTime=0 AND
NASIPAddress= '%{NAS-IP-Address}' AND AcctStartTime <= '%S'"
accounting_update_query = "UPDATE ${acct_table1} SET FramedIPAddress =
'%{Framed-IP-Address}' WHERE AcctSessionId = '%{Acct-
Session-Id}' AND UserName = '%{SQL-User-Name}' AND NASIPAddress=
'%{NAS-IP-Address}' AND AcctStopTime = 0"
accounting_start_query = "INSERT into ${acct_table1} (RadAcctId,
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('', '%{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}', '0')"
accounting_start_query_alt = "UPDATE ${acct_table1} SET AcctStartTime =
'%S', AcctStartDelay = '%{Acct-Delay-Time}', ConnectInfo_start =
'%{Connect-Info}' WHERE AcctSessionId = '%{Acct-Session-Id}' AND UserName =
'%{SQL-User-Name}' AND NASIPAddress = '%{NAS-IP-Address}' AND AcctStopTime =
0"
accounting_stop_query = "UPDATE ${acct_table2} SET AcctStopTime = '%S',
AcctSessionTime = '%{Acct-Session-Time}', AcctInputO
ctets = '%{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"
accounting_stop_query_alt = "INSERT into ${acct_table2} (RadAcctId,
AcctSessionId, AcctUniqueId, UserName, Realm, NASIPAddre
ss, NASPortId, NASPortType, AcctStartTime, AcctStopTime, AcctSessionTime,
AcctAuthentic, ConnectInfo_start, ConnectInfo_stop, AcctInputOctets,
AcctOutputOctets, CalledStationId, CallingStationId, AcctTerminateCause,
ServiceType, FramedProtocol, FramedIPAddress, AcctStartDelay, AcctStopDelay)
values('', '%{Acct-Session-Id}', '%{Acct-Unique-Session-Id}',
'%{SQL-User-Name}', '%{Realm}', '%{NAS-IP-Address}', '%{NAS-Port}',
'%{NAS-Port-Type}', '0', '%S', '%{Acct-Session-Time}', '%{Acct-Authentic}',
'', '%{Connect-Info}', '%{Acct-Input-Octets}', '%{Acct-Output-Octets}',
'%{Called-Station-Id}', '%{Calling-Station-Id}', '%{h323-disconnect-cause}',
'%{Service-Type}', '%{Framed-Protocol}', '%{Framed-IP-Address}', '0',
'%{Acct-Delay-Time}')"
Maybe something is wrong.
Please help!!!
It is really URGENT!
-
List info/subscribe/unsubscribe? See
http://www.freeradius.org/list/users.html
-
List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Accounting CISCO MySQL problem! Need HELP!! URGENT!
Hi there,
I bukld CISCO h323 accounting using FreeRadius,
first problem is that I do accounting start-stop packets,
It works well, it is full info in radacct files, but I have the problem with
MySQL.
I have no disconnect couse in MySQL base!
It works only when I make only Stop packets accounting.
I've changed sql.conf file like this:
accounting_onoff_query = "UPDATE ${acct_table1} SET AcctStopTime='%S',
AcctSessionTime=unix_timestamp('%S') - unix_timestamp AcctStartTime),
AcctTerminateCause='%{Acct-Terminate-Cause}', AcctStopDelay =
%{Acct-Delay-Time} WHERE AcctSessionTime=0 AND AcctStopTime=0 AND
NASIPAddress= '%{NAS-IP-Address}' AND AcctStartTime <= '%S'"
accounting_update_query = "UPDATE ${acct_table1} SET FramedIPAddress =
'%{Framed-IP-Address}' WHERE AcctSessionId = '%{Acct-
Session-Id}' AND UserName = '%{SQL-User-Name}' AND NASIPAddress=
'%{NAS-IP-Address}' AND AcctStopTime = 0"
accounting_start_query = "INSERT into ${acct_table1} (RadAcctId,
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('', '%{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}', '0')"
accounting_start_query_alt = "UPDATE ${acct_table1} SET AcctStartTime =
'%S', AcctStartDelay = '%{Acct-Delay-Time}', ConnectInfo_start =
'%{Connect-Info}' WHERE AcctSessionId = '%{Acct-Session-Id}' AND UserName =
'%{SQL-User-Name}' AND NASIPAddress = '%{NAS-IP-Address}' AND AcctStopTime =
0"
accounting_stop_query = "UPDATE ${acct_table2} SET AcctStopTime = '%S',
AcctSessionTime = '%{Acct-Session-Time}', AcctInputO
ctets = '%{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"
accounting_stop_query_alt = "INSERT into ${acct_table2} (RadAcctId,
AcctSessionId, AcctUniqueId, UserName, Realm, NASIPAddre
ss, NASPortId, NASPortType, AcctStartTime, AcctStopTime, AcctSessionTime,
AcctAuthentic, ConnectInfo_start, ConnectInfo_stop, AcctInputOctets,
AcctOutputOctets, CalledStationId, CallingStationId, AcctTerminateCause,
ServiceType, FramedProtocol, FramedIPAddress, AcctStartDelay, AcctStopDelay)
values('', '%{Acct-Session-Id}', '%{Acct-Unique-Session-Id}',
'%{SQL-User-Name}', '%{Realm}', '%{NAS-IP-Address}', '%{NAS-Port}',
'%{NAS-Port-Type}', '0', '%S', '%{Acct-Session-Time}', '%{Acct-Authentic}',
'', '%{Connect-Info}', '%{Acct-Input-Octets}', '%{Acct-Output-Octets}',
'%{Called-Station-Id}', '%{Calling-Station-Id}', '%{h323-disconnect-cause}',
'%{Service-Type}', '%{Framed-Protocol}', '%{Framed-IP-Address}', '0',
'%{Acct-Delay-Time}')"
Maybe something is wrong.
Please help!!!
It is really URGENT!
-
List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Re: mysql problem?
eh.. i just vacuum radacct.. thx anyway On Wed, 22 May 2002, Alexandre Strube wrote: > On Wed, 22 May 2002 16:50:06 +0300 (EEST), Petre L. Daniel wrote: > > Was the radiusd stopped? Are you running it with supervise or something like > that? > This happens here when radius is not responding on the very same moment > that the dialup user disconnects, because of the obvious fact that the STOP > packet is not logged on mysql. Some days ago, I don't know why, both supervise > and radiusd stopped working at 3am. As the stop packets weren't logged, my > dialup-admin was a mess. I had to delete all invalid entries... > > The quickest way to do this is to remove all lines from sql which have the >AcctStopTime > filled with -00-00 00:00:00 > > Something like DELETE FROM radacct WHERE AcctStopTime = '-00-00 00:00:00' > > As the dialup users will eventually disconnect, the correct stop times will be >logged and > everything will come back to normal. > > >heyah. > >it seems that since some days ago my dialup admin interface shows users > >that logged off long time ago and doesn show the new users when the max > >line is reached. > >like i think something is not updated and i cant see in real time who's > >logged in.. > >what could be the problem? > >thx in advance. > > > As opiniões formuladas neste e-mail são de caráter > exclusivamente pessoal. Minha opinião não necessariamente > representa a opinião do meu Moto Grupo nem da empresa onde > trabalho. > > Mene Sakkhet ur-seveh > Alexandre Ganso - Diretor Steel Goose Moto Group > 6, 7 e 8 de setembro - Aniversario 10 anos Steel Goose - Ouro Branco - MG > 500 Four Vermelha > [EMAIL PROTECTED] > ICQ# 3778773 > > > > > > > - > List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html > -- Petre L. Daniel,System Administrator, Canad Systems Pitesti SRL Romania, tel:+4048206200,+4048206201 email:[EMAIL PROTECTED] http://www.cyber.ro - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Re: mysql problem?
On Wed, 22 May 2002 16:50:06 +0300 (EEST), Petre L. Daniel wrote: Was the radiusd stopped? Are you running it with supervise or something like that? This happens here when radius is not responding on the very same moment that the dialup user disconnects, because of the obvious fact that the STOP packet is not logged on mysql. Some days ago, I don't know why, both supervise and radiusd stopped working at 3am. As the stop packets weren't logged, my dialup-admin was a mess. I had to delete all invalid entries... The quickest way to do this is to remove all lines from sql which have the AcctStopTime filled with -00-00 00:00:00 Something like DELETE FROM radacct WHERE AcctStopTime = '-00-00 00:00:00' As the dialup users will eventually disconnect, the correct stop times will be logged and everything will come back to normal. >heyah. >it seems that since some days ago my dialup admin interface shows users >that logged off long time ago and doesn show the new users when the max >line is reached. >like i think something is not updated and i cant see in real time who's >logged in.. >what could be the problem? >thx in advance. As opiniões formuladas neste e-mail são de caráter exclusivamente pessoal. Minha opinião não necessariamente representa a opinião do meu Moto Grupo nem da empresa onde trabalho. Mene Sakkhet ur-seveh Alexandre Ganso - Diretor Steel Goose Moto Group 6, 7 e 8 de setembro - Aniversario 10 anos Steel Goose - Ouro Branco - MG 500 Four Vermelha [EMAIL PROTECTED] ICQ# 3778773 - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
mysql problem?
heyah. it seems that since some days ago my dialup admin interface shows users that logged off long time ago and doesn show the new users when the max line is reached. like i think something is not updated and i cant see in real time who's logged in.. what could be the problem? thx in advance. -- Petre L. Daniel,System Administrator, Canad Systems Pitesti SRL Romania, tel:+4048206200,+4048206201 email:[EMAIL PROTECTED] http://www.cyber.ro - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
RE: mysql problem
[EMAIL PROTECTED] writes:
>Because freeradius has to authenticate with a database which is not owned
>by
>us.
>I can't add anything to that database.
>
>What I mean is that database just have field Username, Password and other
>fields which is
>not meaningful to freeradius.
>
>I have modified sql.conf, I dont know how to modify the authorisation and
>accounting setting in sql.conf
Ah, with you now: basically, what you want to do is auth against one SQL
(which you have no control over) yet account against another (which, I
assume, you do?).
I think you can do that. Something like this I'd guess (anyone?):
You need to set up multiple separate SQL instances in sql.conf (e.g. 'sql1'
to talk to your existing db for auth, editing the SQL statements there in
as necessary for the existing server's field and db names, and 'sql2' for
the accounting (using the standard freeradius accounting table structure)).
Basically, in sql.conf duplicate the existing main 'sql' entry so you have
two, rename each one to sql1 and sql2, point the first at your auth db (and
edit the SQL lines as needed) and the second at your new accounts db. Put
sql1 in the authorization section of radiusd.conf and sql2 in the
accounting section. Or something like that. I think...
Regards,
SB
Scott Bartlett
BTA Limited, 100 High Street Wandsworth, London SW18 4LA, United Kingdom
e: [EMAIL PROTECTED]v: +44 (0)20 8871 4240 f: +44 (0)20 8871 4584
Network Consultancy and Support for Windows 9x/NT and MacOS.
Internet connectivity, solutions, and business services.
http://www.bta.com.
This message is intended only for the use of the person(s) ("The intended
recipient(s)") to whom it is addressed. It may contain information which is
privileged and confidential within the meaning of applicable law. If you
are not the intended recipient, please contact the sender as soon as
possible. The views expressed in this communication may not necessarily be
the views held by BTA.
-
List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
RE: mysql problem
Title: RE: mysql problem Because freeradius has to authenticate with a database which is not owned by us. I can't add anything to that database. What I mean is that database just have field Username, Password and other fields which is not meaningful to freeradius. I have modified sql.conf, I dont know how to modify the authorisation and accounting setting in sql.conf -Original Message- From: freeradius-users [mailto:[EMAIL PROTECTED]] Sent: Sunday, May 12, 2002 5:05 PM To: [EMAIL PROTECTED] Subject: RE: mysql problem [EMAIL PROTECTED] wrote: >But the radcheck table does not have the field "Attribute" >and no table usergroup, radgroupcheck, radreply, radgroupreply > >When I dial to the modem pool, no error message is shown >even debug mode is enabled, and the authentication is fail. This doesn't seem to make sense against what you asked previously - do you want to use the 'standard' MySQL tables, or your own ones?? If you want to use the standard ones you need to set them up in MySQL as per the schema file included in the tarball. Check out http://www.frontios.com/freeradius.html for some possibly useful info and tips. If you want to use your own, you just edit the actual SQL statements in sql.conf to reflect your own database structure. Regards, SB Scott Bartlett e: [EMAIL PROTECTED] - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
RE: mysql problem
[EMAIL PROTECTED] wrote: >But the radcheck table does not have the field "Attribute" >and no table usergroup, radgroupcheck, radreply, radgroupreply > >When I dial to the modem pool, no error message is shown >even debug mode is enabled, and the authentication is fail. This doesn't seem to make sense against what you asked previously - do you want to use the 'standard' MySQL tables, or your own ones?? If you want to use the standard ones you need to set them up in MySQL as per the schema file included in the tarball. Check out http://www.frontios.com/freeradius.html for some possibly useful info and tips. If you want to use your own, you just edit the actual SQL statements in sql.conf to reflect your own database structure. Regards, SB Scott Bartlett e: [EMAIL PROTECTED] - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
RE: mysql problem
Title: RE: mysql problem But the radcheck table does not have the field "Attribute" and no table usergroup, radgroupcheck, radreply, radgroupreply When I dial to the modem pool, no error message is shown even debug mode is enabled, and the authentication is fail. -Original Message- From: Alan DeKok [mailto:[EMAIL PROTECTED]] Sent: Friday, May 10, 2002 11:12 PM To: [EMAIL PROTECTED] Subject: Re: mysql problem Patrick Chan <[EMAIL PROTECTED]> wrote: > I have a user table just have two fields: > > Username, Password > > Can I use SQL only for authentication with these two fields? > and use "users" file for authorization (radreply)? Yes. That's why the SQL queries come from a configuration file, so you can edit them yourself. Alan DeKok. - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Re: mysql problem
Patrick Chan <[EMAIL PROTECTED]> wrote: > I have a user table just have two fields: > > Username, Password > > Can I use SQL only for authentication with these two fields? > and use "users" file for authorization (radreply)? Yes. That's why the SQL queries come from a configuration file, so you can edit them yourself. Alan DeKok. - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
mysql problem
Title: mysql problem Hi all, I have a user table just have two fields: Username, Password Can I use SQL only for authentication with these two fields? and use "users" file for authorization (radreply)? Because our client are unwilling to modify their database, they just have a table with only Username and Password fields. So I wanna use DEFAULT entry in users file for the reply attributes. Thanks. Patrick
Re: MySQL problem
Hi Patrick!
> but when dialing to model pool, it gave me the following error
-snip-
> User-Name = "peterl"
-snip-
> rlm_sql: Reserving sql socket id: 4
> rlm_sql: User DEFAULT not found and DEFAULT not found
> rlm_sql: Released sql socket id: 4
>
> It can read the username from NAS, but why the username changes to DEFAULT
> when talking to mySQL?
Sounds to me as if the user ('peterl' in your case) just doesn't exist in
your mysql db?
Correct me if I'm wrong, but afaik DEFAULT is used for all users that are
not explicitly listed in your user config.
Regards,
Chris
--
Christoph R. Hartel
Linux System Administrator
eMind GmbH | phone: +49 671 794613 - 0
Rosenheimer Strasse 2-4 | fax:+49 671 794613 - 50
D-55543 Bad Kreuznach| mobile: +49 163 723
www.emindgroup.com | email: [EMAIL PROTECTED]
-
List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
MySQL problem
Title: MySQL problem
Hi,
I am using FreeRadius 0.5 and MySQL.
I have put the followings in radiusd.conf:
authorise {
preprocess
suffix
sql
}
authenticate {
pap <--- I remove sql as I know sql is not allowed in 0.5 version
}
but when dialing to model pool, it gave me the following error
rad_recv: Access-Request packet from host 202.70.19.252:1645, id=60, length=85
NAS-IP-Address = 202.70.19.252
NAS-Port = 132
NAS-Port-Type = Async
User-Name = "peterl"
Calling-Station-Id = "97424222"
User-Password = "\331u[:\273\251.\305\315\025Pvs/+\273"
Service-Type = Framed-User
Framed-Protocol = PPP
rlm_sql: Reserving sql socket id: 4
rlm_sql: User DEFAULT not found and DEFAULT not found
rlm_sql: Released sql socket id: 4
It can read the username from NAS, but why the username changes to DEFAULT when talking to mySQL?
Thanks.
MySQL problem
Title: MySQL problem
Hi,
I am using FreeRadius 0.5 and MySQL.
I have put the followings in radiusd.conf:
authorise {
preprocess
suffix
sql
}
authenticate {
pap <--- I remove sql as I know sql is not allowed in 0.5 version
}
but when dialing to model pool, it gave me the following error
rad_recv: Access-Request packet from host 202.70.19.252:1645, id=60, length=85
NAS-IP-Address = 202.70.19.252
NAS-Port = 132
NAS-Port-Type = Async
User-Name = "peterl"
Calling-Station-Id = "97424222"
User-Password = "\331u[:\273\251.\305\315\025Pvs/+\273"
Service-Type = Framed-User
Framed-Protocol = PPP
rlm_sql: Reserving sql socket id: 4
rlm_sql: User DEFAULT not found and DEFAULT not found
rlm_sql: Released sql socket id: 4
It can read the username from NAS, but why the username changes to DEFAULT when talking to mySQL?
Thanks.
