(RADIATOR) How To Log To A MySQL-database [Public]
Hugh Irvine
hugh at open.com.au
Tue May 3 03:45:16 CDT 2005
Hello Hugo -
Thanks for the information.
What exactly are you trying to log?
As mentioned in my previous mail there are some problems with your Log
and AuthLog clauses which is why you are seeing the error messages in
the RADLOG table. If you are talking about accounting logging, there
are no accounting requests shown in the debug, therefore there is
nothing in the ACCOUNTING table. You will need to check the
configuration of your access equipment to enable accounting (if it is
supported at all).
regards
Hugh
On 2 May 2005, at 23:13, Miedema, Hugo wrote:
> 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>hu
> go 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.
>
>
NB:
Have you read the reference manual ("doc/ref.html")?
Have you searched the mailing list archive
(www.open.com.au/archives/radiator)?
Have you had a quick look on Google (www.google.com)?
Have you included a copy of your configuration file (no secrets),
together with a trace 4 debug showing what is happening?
--
Radiator: the most portable, flexible and configurable RADIUS server
anywhere. Available on *NIX, *BSD, Windows, MacOS X.
-
Nets: internetwork inventory and management - graphical, extensible,
flexible with hardware, software, platform and database independence.
-
CATool: Private Certificate Authority for Unix and Unix-like systems.
--
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