I am looking for a good reference on how to integrate MYSQL and Freeradius. I've set up the basics and imported the schema's and modified the sql.conf file and the sites-enabled/default files. All starting and running fine. What I don't know about are how (if at all) you can log the rad_recv messages to sql. For instance my NAS is sending back the following: *rad_recv: Accounting-Request packet from host x.x.x.120 port 51637, id=50, length=95 Acct-Status-Type = Interim-Update Acct-Session-Id = "C2594B9A71DB" Acct-Delay-Time = 0 User-Name = "joe.bobuser" NAS-Identifier = "M20" Juniper-Interactive-Command = "run start shell " NAS-IP-Address = x.x.x.120 *+- entering group preacct {...} ++[preprocess] returns ok [acct_unique] WARNING: Attribute NAS-Port was not found in request, unique ID MAY be inconsistent [acct_unique] Hashing ',Client-IP-Address = x.x.x.120,NAS-IP-Address = x.x.x.120,Acct-Session-Id = "C2594B9A71DB",User-Name = "joe.bobuser"' [acct_unique] Acct-Unique-Session-ID = "c8c99ca6ea5c67dc". ++[acct_unique] returns ok [suffix] No '@' in User-Name = "joe.bobuser", looking up realm NULL [suffix] No such realm "NULL" ++[suffix] returns noop ++[files] returns noop +- entering group accounting {...} [detail] expand: /var/log/radius/radacct/%{Client-IP-Address}/detail-%Y%m%d -> /var/log/radius/radacct/x.x.x.120/detail-20100607 [detail] /var/log/radius/radacct/%{Client-IP-Address}/detail-%Y%m%d expands to /var/log/radius/radacct/x.x.x.120/detail-20100607 [detail] expand: %t -> Mon Jun 7 11:13:24 2010 ++[detail] returns ok ++[unix] returns noop [radutmp] expand: /var/log/radius/radutmp -> /var/log/radius/radutmp [radutmp] expand: %{User-Name} -> joe.bobuser rlm_radutmp: No NAS-Port seen. Cannot do anything. rlm_radumtp: WARNING: checkrad will probably not work! ++[radutmp] returns noop [sql] expand: %{User-Name} -> joe.bobuser [sql] sql_set_user escaped user --> 'joe.bobuser' [sql] expand: %{Acct-Input-Gigawords} -> [sql] ... expanding second conditional [sql] expand: %{Acct-Input-Octets} -> [sql] ... expanding second conditional [sql] expand: %{Acct-Output-Gigawords} -> [sql] ... expanding second conditional [sql] expand: %{Acct-Output-Octets} -> [sql] ... expanding second conditional [sql] expand: UPDATE radacct SET framedipaddress = '%{Framed-IP-Address}', acctsessiontime = '%{Acct-Session-Time}', acctinputoctets = '%{%{Acct-Input-Gigawords}:-0}' << 32 | '%{%{Acct-Input-Octets}:-0}', acctoutputoctets = '%{%{Acct-Output-Gigawords}:-0}' << 32 | '%{%{Acct-Output-Octets}:-0}' WHERE acctsessionid = '%{Acct-Session-Id}' AND username = '%{SQL-User-Name}' AND nasipaddress = '%{NAS-IP-Address}' -> UPDATE radacct SET framedipaddress = '', acctsessiontime = '', acctinputoctets = '0' << 32 | '0', acctoutputoctets = '0' << 32 | '0' WHERE acctsessionid = 'C2594B9A71DB' AND username = 'joe.bobuser' AND nasipaddress = [sql] expand: /var/log/radius/sqltrace.sql -> /var/log/radius/sqltrace.sql rlm_sql (sql): Reserving sql socket id: 0 rlm_sql_mysql: query: UPDATE radacct SET framedipaddress = '', acctsessiontime = '', acctinputoctets = '0' << 32 | '0', acctoutputoctets = '0' << 32 | '0' WHERE acctsessionid = 'C2594B9A71DB' AND username = 'joe.bobuser' AND nasipaddress = 'x.x.x.120' rlm_sql (sql): Released sql socket id: 0 ++[sql] returns ok [attr_filter.accounting_response] expand: %{User-Name} -> joe.bobuser attr_filter: Matched entry DEFAULT at line 12 ++[attr_filter.accounting_response] returns updated Sending Accounting-Response of id 50 to x.x.x.120 port 51637 Finished request 5. Cleaning up request 5 ID 50 with timestamp +397 Going to the next request Ready to process requests. rad_recv: Accounting-Request packet from host x.x.x.120 port 51637, id=51, length=84 Acct-Status-Type = Interim-Update Acct-Session-Id = "C2594B9A71DB" Acct-Delay-Time = 0 User-Name = "joe.bobuser" NAS-Identifier = "M20" Juniper-Interactive-Command = "quit " NAS-IP-Address = x.x.x.120 ... As can be seen above the rad_recv: section is sending back a few items that I am interested in having logged. For instance the Juniper-Interactive-Command. Thanks if you can help or point me in the right direction. N
Hi,
rad_recv: Accounting-Request packet from host x.x.x.120 port 51637, id=50, length=95 Acct-Status-Type = Interim-Update Acct-Session-Id = "C2594B9A71DB" Acct-Delay-Time = 0 User-Name = "joe.bobuser" NAS-Identifier = "M20" Juniper-Interactive-Command = "run start shell " NAS-IP-Address = x.x.x.120 +- entering group preacct {...} ++[preprocess] returns ok [acct_unique] WARNING: Attribute NAS-Port was not found in request, unique ID MAY be inconsistent
your NAS is doing all sorts of things wrong really. its supposed to supply a NAS-Port and that value is supposed to change for each session. oh well. edit your modules/acct_unique to put something else into the hashing function
[radutmp] expand: /var/log/radius/radutmp -> /var/log/radius/radutmp [radutmp] expand: %{User-Name} -> joe.bobuser rlm_radutmp: No NAS-Port seen. Cannot do anything. rlm_radumtp: WARNING: checkrad will probably not work!
are you using radutmp? if not, comment it out.
As can be seen above the rad_recv: section is sending back a few items that I am interested in having logged. For instance the Juniper-Interactive-Command.
Thanks if you can help or point me in the right direction.
simply add those attributes (if present!) into the SQL that create the accounting INSERT/UPDATE commands - sql/mysql/dialup.conf - and ensure the table is ready for them! :-) alan
Thanks for the help Alan, I see what you mean now. I guess I assumed that the radacct database was more than a recording of session start and end times. Is there another table that I should be seeing user activity in? In other words I was thinking that user activity (ie. the commands they entered) would be logged somewhere in the sql tables vice in the /var/log/radius/radacct/IP_ADDR/detail_`date` file. Can I simply create a new table called for instance Acct_activity and modify the dialup.conf file to dump that data in that table keying on session-id and time for instance? I'm sure that's not supposed to be in the radacct table. btw: where do I comment out radutmp? N On Mon, Jun 7, 2010 at 11:56 AM, Alan Buxey <A.L.M.Buxey@lboro.ac.uk> wrote:
Hi,
rad_recv: Accounting-Request packet from host x.x.x.120 port 51637,
id=50, length=95
Acct-Status-Type = Interim-Update Acct-Session-Id = "C2594B9A71DB" Acct-Delay-Time = 0 User-Name = "joe.bobuser" NAS-Identifier = "M20" Juniper-Interactive-Command = "run start shell " NAS-IP-Address = x.x.x.120 +- entering group preacct {...} ++[preprocess] returns ok [acct_unique] WARNING: Attribute NAS-Port was not found in request,
unique ID MAY be inconsistent
your NAS is doing all sorts of things wrong really. its supposed to supply a NAS-Port and that value is supposed to change for each session. oh well. edit your modules/acct_unique to put something else into the hashing function
[radutmp] expand: /var/log/radius/radutmp -> /var/log/radius/radutmp [radutmp] expand: %{User-Name} -> joe.bobuser rlm_radutmp: No NAS-Port seen. Cannot do anything. rlm_radumtp: WARNING: checkrad will probably not work!
are you using radutmp? if not, comment it out.
As can be seen above the rad_recv: section is sending back a few items that I am interested in having logged. For instance the Juniper-Interactive-Command.
Thanks if you can help or point me in the right direction.
simply add those attributes (if present!) into the SQL that create the accounting INSERT/UPDATE commands - sql/mysql/dialup.conf - and ensure the table is ready for them! :-)
alan - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Alan, I'm not sure I understand what you mean with the following statement. "simply add those attributes (if present!) into the SQL that create the accounting INSERT/UPDATE commands - sql/mysql/dialup.conf - and ensure the table is ready for them! :-)" The particular part is (if present!). Can you by chance explain that part or see my previous post concerning a new table? Appreciate the feedback. N On Mon, Jun 7, 2010 at 1:03 PM, Natr Brazell <natrbrazell@gmail.com> wrote:
Thanks for the help Alan,
I see what you mean now. I guess I assumed that the radacct database was more than a recording of session start and end times. Is there another table that I should be seeing user activity in? In other words I was thinking that user activity (ie. the commands they entered) would be logged somewhere in the sql tables vice in the /var/log/radius/radacct/IP_ADDR/detail_`date` file. Can I simply create a new table called for instance Acct_activity and modify the dialup.conf file to dump that data in that table keying on session-id and time for instance? I'm sure that's not supposed to be in the radacct table. btw: where do I comment out radutmp?
N
On Mon, Jun 7, 2010 at 11:56 AM, Alan Buxey <A.L.M.Buxey@lboro.ac.uk>wrote:
Hi,
rad_recv: Accounting-Request packet from host x.x.x.120 port 51637,
id=50, length=95
Acct-Status-Type = Interim-Update Acct-Session-Id = "C2594B9A71DB" Acct-Delay-Time = 0 User-Name = "joe.bobuser" NAS-Identifier = "M20" Juniper-Interactive-Command = "run start shell " NAS-IP-Address = x.x.x.120 +- entering group preacct {...} ++[preprocess] returns ok [acct_unique] WARNING: Attribute NAS-Port was not found in request,
unique ID MAY be inconsistent
your NAS is doing all sorts of things wrong really. its supposed to supply a NAS-Port and that value is supposed to change for each session. oh well. edit your modules/acct_unique to put something else into the hashing function
[radutmp] expand: /var/log/radius/radutmp -> /var/log/radius/radutmp [radutmp] expand: %{User-Name} -> joe.bobuser rlm_radutmp: No NAS-Port seen. Cannot do anything. rlm_radumtp: WARNING: checkrad will probably not work!
are you using radutmp? if not, comment it out.
As can be seen above the rad_recv: section is sending back a few items that I am interested in having logged. For instance the Juniper-Interactive-Command.
Thanks if you can help or point me in the right direction.
simply add those attributes (if present!) into the SQL that create the accounting INSERT/UPDATE commands - sql/mysql/dialup.conf - and ensure the table is ready for them! :-)
alan - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
participants (2)
-
Alan Buxey -
Natr Brazell