(RADIATOR) How To Log To A MySQL-database [Public]
Miedema, Hugo
Hugo.Miedema at Getronics.com
Fri Apr 29 03:12:02 CDT 2005
I'm quite new on Radiator.
I'm facing a problem I can't solve. I have Radiator 3.12 running on
FreeBSD and MySQL-database.
To create the right database and tables I used the
'mysqlCreate.sql'-script from the goodies-directory. Now I want to write
logging to the database.
When I use the 'radpwtst'-tool, logging goes to the MySQL-database.
When I try to authenticate through a pc, no logging comes into the
database.
The radiator-configfile:
# radius.cfg
#Foreground
LogStdout
LogDir /var/log/radius
DbDir /etc/radiator
LogFile %L/%Y-%m-radius.log
Trace 4
AuthPort 1812
AcctPort 1813
<Client 10.10.10.10>
Secret mysecret
DupInterval 0
IdenticalClients 10.10.10.11 localhost
Identifier Cisco-switches
</Client>
<Log SQL>
Identifier LogSQL
DBSource dbi:mysql:dekey
DBUsername radiator
DBAuth de_key
#>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>><<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
<<<<<<<
# for some unknown reason the LogSuccess and LogFailure results in a
error ....
#>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>><<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
<<<<<<<
# LogSuccess
# SuccessQuery insert into RADLOG (TIME_STAMP, USERNAME, TYPE)
values (%G, '%n', 1)
# LogFailure
# FailureQuery insert into RADLOG (TIME_STAMP, USERNAME, TYPE,
REASON) values (%G, '%n', 0, %1)
# Trace 1
insert into %3 (TIME_STAMP, USERNAME, TYPE) values (%G, '%n', 1)
Trace 1
</Log>
<AuthLog SQL>
# This database spec usually should be exactly the same
# as in <AuthBy RADMIN> above
Identifier AuthLogSQL
DBSource dbi:mysql:dekey:localhost
DBUsername radiator
DBAuth de_key
#>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>><<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
<<<<<<<
# for some unknown reason the LogSuccess and LogFailure results in a
error ....
#>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>><<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
<<<<<<<
# LogSuccess
# SuccessQuery insert into RADAUTHLOG (TIME_STAMP, USERNAME,
TYPE) values (%G, '%n', 1)
# LogFailure
# FailureQuery insert into RADAUTHLOG (TIME_STAMP, USERNAME,
TYPE, REASON) values (%G, '%n', 0, %1)
insert into RADAUTHLOG (TIME_STAMP, USERNAME, TYPE) values (%G,
'%n', 1)
</AuthLog>
<Handler Realm=xeon.com>
RewriteUsername s/^([^@]+).*/$1/
<AuthBy FILE>
EAPType MD5-Challenge
Filename %D/users
StripFromReply Tunnel-Type, Tunnel-Medium-Type,
Tunnel-Private-Group-ID
AddToReply Tunnel-Type=VLAN,
Tunnel-Medium-Type=Ether_802, Tunnel-Private-Group-ID=100
</AuthBy>
</Handler>
<Handler Realm=klopjacht.com>
<AuthBy FILE>
Log LogSQL
RewriteUsername s/^([^@]+).*/$1/
EAPType MD5-Challenge
Filename %D/users
StripFromReply Tunnel-Type, Tunnel-Medium-Type,
Tunnel-Private-Group-ID
AddToReply Tunnel-Type=VLAN,
Tunnel-Medium-Type=Ether_802, Tunnel-Private-Group-ID=200
</AuthBy>
</Handler>
<Handler Realm=tv.nl>
<AuthBy FILE>
# Log LogSQL
RewriteUsername s/^([^@]+).*/$1/
EAPType MD5-Challenge
Filename %D/users
StripFromReply Tunnel-Type, Tunnel-Medium-Type,
Tunnel-Private-Group-ID
AddToReply Tunnel-Type=VLAN,
Tunnel-Medium-Type=Ether_802, Tunnel-Private-Group-ID=100
</AuthBy>
</Handler>
<AuthBy SQL>
Identifier LOKALE-ACCOUNTING
DBSource dbi:mysql:dekey
DBUsername radiator
DBAuth de_key
AuthSelect
AccountingTable ACCOUNTING
AcctColumnDef USERNAME,User-Name
AcctColumnDef TIME_STAMP,Timestamp,integer-date
AcctColumnDef ACCTSTATUSTYPE,Acct-Status-Type
AcctColumnDef ACCTDELAYTIME,Acct-Delay-Time,integer
AcctColumnDef ACCTINPUTOCT,Acct-Input-Octets,integer
AcctColumnDef ACCTOUTPUTOCT,Acct-Output-Octets,integer
AcctColumnDef ACCTSESSIONID,Acct-Session-Id
AcctColumnDef ACCTSESSTIME,Acct-Session-Time,integer
AcctColumnDef ACCTTERMINATECAUSE,Acct-Terminate-Cause
AcctColumnDef NASIDENTIFIER,NAS-Identifier
AcctColumnDef NASPORT,NAS-Port,integer
AcctColumnDef NASIPADDRESS,NAS-IP-Address
AcctColumnDef CALLEDSTATIONID,Called-Station-Id
AcctColumnDef CALLINGSTATIONID,Calling-Station-Id
IgnoreAuthentication
</AuthBy>
<AuthBy RADIUS>
Identifier SURFNET-RADIUS
Host some.host.on.the.internet.org
AuthPort 1812
AcctPort 1813
Secret [XXXXXXXXXXXXXXXXXXXX]
StripFromReply
Tunnel-Type,Tunnel-Medium-Type,Tunnel-Private-Group-ID
AddToReply Tunnel-Type=VLAN, Tunnel-Medium-Type=Ether_802,
Tunnel-Private-Group-ID=300
</AuthBy>
<Handler Realm="/.+\..{2,3}$/", EAP-Message="/.+/">
AuthBy SURFNET-RADIUS
</Handler>
<Handler Realm="/.+\..{2,3}$/", Request-Type=Accounting-Request>
<AuthBy GROUP>
AuthByPolicy ContinueWhileAccept
AuthBy LOKALE-ACCOUNTING
AuthBy SURFNET-RADIUS
</AuthBy>
</Handler>
And this is the database:
mysql> show tables;
+----------------------+
| Tables_in_dekey |
+----------------------+
| ACCOUNTING |
| RADAUTHLOG |
| RADCLIENTLIST |
| RADLASTAUTH |
| RADLOG |
| RADONLINE |
| RADPOOL |
| RADSQLRADIUS |
| RADSQLRADIUSINDIRECT |
| RADSTATSLOG |
| SUBSCRIBERS |
| TBL_VASCODP |
+----------------------+
mysql> show columns from RADLOG;
+------------+-----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+-----------+------+-----+---------+-------+
| TIME_STAMP | int(11) | YES | | NULL | |
| PRIORITY | int(11) | YES | | NULL | |
| MESSAGE | char(200) | YES | | NULL | |
+------------+-----------+------+-----+---------+-------+
mysql> select * from RADLOG
-> ;
+------------+----------+-----------------------------------------------
---------------------------------+
| TIME_STAMP | PRIORITY | MESSAGE
|
+------------+----------+-----------------------------------------------
---------------------------------+
| 0 | 0 | Unknown keyword 'insert' in
/etc/radiator/radius.cfg line 131 |
| 0 | 0 | Unknown keyword 'insert' in
/etc/radiator/radius.cfg line 131 |
| 0 | 0 | Unknown keyword 'insert' in
/etc/radiator/radius.cfg line 131 |
| 1113921818 | 0 | Unknown keyword 'insert' in
/etc/radiator/radius.cfg line 131 |
| 1113987174 | 0 | Unknown keyword 'insert' in
/etc/radiator/radius.cfg line 57 |
| 1113987867 | 0 | Unknown keyword 'insert' in
/etc/radiator/radius.cfg line 57 |
| 1114172136 | 0 | Unknown keyword 'insert' in
/etc/radiator/radius.cfg line 57 |
| 1114172388 | 0 | Unknown keyword 'InsertQuery' in
/etc/radiator/radius.cfg line 57 |
| 1114172657 | 0 | Unknown keyword 'AddQuery' in
/etc/radiator/radius.cfg line 57 |
| 1114180143 | 0 | Unknown keyword 'LogSuccess' in
/etc/radiator/radius.cfg line 57 |
| 1114180143 | 0 | Unknown keyword 'SuccessQuery' in
/etc/radiator/radius.cfg line 58 |
| 1114180143 | 0 | Unknown keyword 'LogFailure' in
/etc/radiator/radius.cfg line 59 |
| 1114180143 | 0 | Unknown keyword 'FailureQuery' in
/etc/radiator/radius.cfg line 60 |
| 1114423144 | 0 | Unknown keyword 'LogSuccess' in
/etc/radiator/radius.cfg line 57 |
| 1114423144 | 0 | Unknown keyword 'SuccessQuery' in
/etc/radiator/radius.cfg line 58 |
| 1114423144 | 0 | Unknown keyword 'LogFailure' in
/etc/radiator/radius.cfg line 59 |
| 1114423144 | 0 | Unknown keyword 'FailureQuery' in
/etc/radiator/radius.cfg line 60 |
| 1114423309 | 0 | Unknown keyword 'LogSuccess' in
/etc/radiator/radius.cfg line 57 |
| 1114423309 | 0 | Unknown keyword 'SuccessQuery' in
/etc/radiator/radius.cfg line 58 |
| 1114423309 | 0 | Unknown keyword 'LogFailure' in
/etc/radiator/radius.cfg line 59 |
| 1114423309 | 0 | Unknown keyword 'FailureQuery' in
/etc/radiator/radius.cfg line 60 |
| 1114423541 | 0 | Unknown keyword 'insert' in
/etc/radiator/radius.cfg line 62 |
| 1114423541 | 0 | Unknown keyword 'insert' in
/etc/radiator/radius.cfg line 78 |
| 1114517115 | 0 | Unknown keyword 'insert' in
/etc/radiator/radius.cfg line 62 |
| 1114517115 | 0 | Unknown keyword 'insert' in
/etc/radiator/radius.cfg line 78 |
| 1114517622 | 1 | Could not find a handler for fred: request is
ignored |
| 1114517627 | 1 | Could not find a handler for fred: request is
ignored |
| 1114517632 | 1 | Could not find a handler for fred: request is
ignored |
| 1114517872 | 1 | Could not find a handler for
customer at guest.nl: request is ignored |
+------------+----------+-----------------------------------------------
---------------------------------+
My questions:
How can I get the TIME_STAMP in another format (e.g. Apr 04 2005 14:23)
How can I get logging and accounting to the Database?
I have studied the goodies directory, but that was no help to me. Can
someone please provide me a working config?
regard,
Hugo Miedema
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://www.open.com.au/pipermail/radiator/attachments/20050429/cd31c15a/attachment.html>
More information about the radiator
mailing list