(RADIATOR) How To Log To A MySQL-database [Public]
Miedema, Hugo
Hugo.Miedema at Getronics.com
Mon May 2 08:13:43 CDT 2005
Hugh,
Here is the requested data. It's the logging of one authentication-request of the account hugo at xeon.com. The authentication is successful, but no logging is added to the database.
# radius.cfg
#Foreground
LogStdout
LogDir /var/log/radius
DbDir /etc/radiator
LogFile %L/%Y-%m-radius.log
Trace 4
AuthPort 1812
AcctPort 1813
# You will probably want to add other Clients to suit your site,
# one for each NAS you want to work with
<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:thekey
DBUsername radiator
DBAuth the_key
# 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:thekey:localhost
DBUsername radiator
DBAuth the_key
# 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=getronics.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:thekey
DBUsername radiator
DBAuth the_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
# IgnoreAccountingResponse
</AuthBy>
<AuthBy RADIUS>
Identifier INTERNET-RADIUS
Host some.host.on.the.internet.com
AuthPort 1812
AcctPort 1813
Secret [XXXXXXXXXXXXXXXXXX]
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 INTERNET-RADIUS
</Handler>
<Handler Realm="/.+\..{2,3}$/", Request-Type=Accounting-Request>
<AuthBy GROUP>
# AuthByPolicy ContinueWhileIgnore
AuthByPolicy ContinueWhileAccept
AuthBy LOKALE-ACCOUNTING
AuthBy INTERNET-RADIUS
</AuthBy>
</Handler>
##################################################################################
#
# Trace 4 logging:
#
# Tried to authenticate with the account hugo at xeon.com
#
##################################################################################
Mon May 2 13:54:28 2005: DEBUG: Packet dump:
*** Received from 10.10.10.10 port 1812 ....
Code: Access-Request
Identifier: 111
Authentic: HYJ<224><234>(0;u,u<134><170><16><128>t
Attributes:
NAS-IP-Address = 10.10.10.10
NAS-Port = 50002
NAS-Port-Type = Ethernet
User-Name = "hugo at xeon.com"
Called-Station-Id = "00-11-92-68-4D-C2"
Calling-Station-Id = "00-A0-C9-FB-6F-8C"
Service-Type = Framed-User
Framed-MTU = 1500
EAP-Message = <2><0><0><18><1>hugo at xeon.com
Message-Authenticator = <203><231>><201>H<195><155><251><31>&Q<177><22><190>)3
Mon May 2 13:54:28 2005: DEBUG: Handling request with Handler 'Realm=xeon.com'
Mon May 2 13:54:28 2005: DEBUG: Rewrote user name to hugo
Mon May 2 13:54:28 2005: DEBUG: Deleting session for hugo at xeon.com, 10.10.10.10, 50002
Mon May 2 13:54:28 2005: DEBUG: Handling with Radius::AuthFILE:
Mon May 2 13:54:28 2005: DEBUG: Handling with EAP: code 2, 0, 18
Mon May 2 13:54:28 2005: DEBUG: Response type 1
Mon May 2 13:54:28 2005: DEBUG: EAP result: 3, EAP MD5-Challenge
Mon May 2 13:54:28 2005: DEBUG: AuthBy FILE result: CHALLENGE, EAP MD5-Challenge
Mon May 2 13:54:28 2005: DEBUG: Access challenged for hugo: EAP MD5-Challenge
Mon May 2 13:54:28 2005: DEBUG: Packet dump:
*** Sending to 10.10.10.10 port 1812 ....
Code: Access-Challenge
Identifier: 111
Authentic: HYJ<224><234>(0;u,u<134><170><16><128>t
Attributes:
EAP-Message = <1><1><0> <4><16>v<130><249><207><4><172><241>/<136><155><176><11>(<22>3<173>verwarming
Message-Authenticator = <0><0><0><0><0><0><0><0><0><0><0><0><0><0><0><0>
Mon May 2 13:54:28 2005: DEBUG: Packet dump:
*** Received from 10.10.10.10 port 1812 ....
Code: Access-Request
Identifier: 112
Authentic: p<219>Z<202>DL<198><164>c.<142>V<224><235><210><223>
Attributes:
NAS-IP-Address = 10.10.10.10
NAS-Port = 50002
NAS-Port-Type = Ethernet
User-Name = "hugo at xeon.com"
Called-Station-Id = "00-11-92-68-4D-C2"
Calling-Station-Id = "00-A0-C9-FB-6F-8C"
Service-Type = Framed-User
Framed-MTU = 1500
EAP-Message = <2><1><0>#<4><16><191><204><241>=<133>z<151>V\$F<252><224>w<143><193>hugo at xeon.com
Message-Authenticator = <174>w<216>72<192><134>Qi<155><221>t<138> <176><238>
Mon May 2 13:54:28 2005: DEBUG: Handling request with Handler 'Realm=xeon.com'
Mon May 2 13:54:28 2005: DEBUG: Rewrote user name to hugo
Mon May 2 13:54:28 2005: DEBUG: Deleting session for hugo at xeon.com, 10.10.10.10, 50002
Mon May 2 13:54:28 2005: DEBUG: Handling with Radius::AuthFILE:
Mon May 2 13:54:28 2005: DEBUG: Handling with EAP: code 2, 1, 35
Mon May 2 13:54:28 2005: DEBUG: Response type 4
Mon May 2 13:54:28 2005: DEBUG: Radius::AuthFILE looks for match with hugo at xeon.com
Mon May 2 13:54:28 2005: DEBUG: Radius::AuthFILE ACCEPT:
Mon May 2 13:54:28 2005: DEBUG: EAP result: 0,
Mon May 2 13:54:28 2005: DEBUG: AuthBy FILE result: ACCEPT,
Mon May 2 13:54:28 2005: DEBUG: Access accepted for hugo
Mon May 2 13:54:28 2005: DEBUG: Packet dump:
*** Sending to 10.10.10.10 port 1812 ....
Code: Access-Accept
Identifier: 112
Authentic: p<219>Z<202>DL<198><164>c.<142>V<224><235><210><223>
Attributes:
EAP-Message = <3><1><0><4>
Message-Authenticator = <0><0><0><0><0><0><0><0><0><0><0><0><0><0><0><0>
Tunnel-Type = VLAN
Tunnel-Medium-Type = Ether_802
Tunnel-Private-Group-ID = 100
Reagrds,
Hugo Miedema
Netwerkbeheerder
-----Original Message-----
From: Hugh Irvine [mailto:hugh at open.com.au]
Sent: Saturday, April 30, 2005 10:18
To: Miedema, Hugo
Cc: radiator at open.com.au
Subject: Re: (RADIATOR) How To Log To A MySQL-database [Public]
Hello Hugo -
Your configuration file looks OK, however you have a problem in the
<Log SQL> and the <AuthLog SQL> clauses with the comment (#) which you
have not put in front of the "insert ...." line which is causing an
error.
There is also an error for the user "fred" because there is no Realm
suffix, and there is an error with "customer at guest.nl" because there is
no Handler for "guest.nl".
You can specify any date format you wish - see sections 6.2 and 6.29 in
the Radiator 3.12 reference manual ("doc/ref.html").
To say any more I will need to see a trace 4 debug from Radiator
showing what is happening.
regards
Hugh
On 29 Apr 2005, at 18:12, Miedema, Hugo wrote:
> 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: (see: above)
>
>
> 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
>
>
--
Archive at http://www.open.com.au/archives/radiator/
Announcements on radiator-announce at open.com.au
To unsubscribe, email 'majordomo at open.com.au' with
'unsubscribe radiator' in the body of the message.
More information about the radiator
mailing list