(RADIATOR) How To Log To A MySQL-database [Public]

Hugh Irvine hugh at open.com.au
Sat Apr 30 03:18:02 CDT 2005


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:
>  
> # 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
>  
>

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