(RADIATOR) Help getting MySQL Accounting working
Steve Caporossi
capoross at musc.edu
Wed Aug 7 12:24:56 CDT 2002
Thanks all for the help. All is good now...the server is working as needed.
Thanks to all : )
Steve
----- Original Message -----
From: Frank Danielson
To: Steve Caporossi
Cc: radiator at open.com.au
Sent: Wednesday, August 07, 2002 11:15 AM
Subject: RE: (RADIATOR) Help getting MySQL Accounting working
If you are using MySQL the FROM_UNIXTIME function works real well for this situation. Try SELECT FROM_UNIXTIME(TIME_STAMP) FROM ACCOUNTING to get the time in human readable format. This function and other useful MySQL time functions are documented at http://www.mysql.com/doc/en/Date_and_time_functions.html
-----Original Message-----
From: Steve Caporossi [mailto:capoross at musc.edu]
Sent: Wednesday, August 07, 2002 8:55 AM
To: Hugh Irvine
Cc: radiator at open.com.au
Subject: Re: (RADIATOR) Help getting MySQL Accounting working
The host is running NTP and the time is correct. I am now getting the UNIX time but have been unable to convert it to a date-time format.
This is my first mysql project and I have no programming experience so, I apologize for so many questions for what may be simple problems.
Steve
----- Original Message -----
From: Hugh Irvine
To: Steve Caporossi
Cc: radiator at open.com.au
Sent: Tuesday, August 06, 2002 9:35 PM
Subject: Re: (RADIATOR) Help getting MySQL Accounting working
Hello Steve -
Thanks for sending the files - everything looks correct.
As has been mentioned elsewhere, have you checked the time on the Radiator host?
It is generally a good idea to run NTP on all network hosts to avoid this sort of problem.
regards
Hugh
On Wednesday, August 7, 2002, at 01:56 AM, Steve Caporossi wrote:
Hugh,
Attached is my config and Trace 4. I did download and install the timedate module from CPAN.
Thanks, Steve
# radius.cfg
#
# Radiator configuration file.
#
# This will authenticate from the /etc/shadow file.
# Accounting will be logged to a SQL Database
#
# It will and try to handle request for any realm.
#
#
#Foreground
#LogStdout
LogDir /var/log/radius
DbDir /etc/radiator
# Use a low trace level in production systems. Increase
# it to 4 or 5 for debugging, or use the -trace flag to radiusd
Trace 4
# Add other Clients below...
<Client 128.23.10.1>
Identifier NASPPP
NasType Cisco
Secret mysecret
DupInterval 0
</Client>
<Client 128.23.10.2>
Identifier NASPPP
NasType Cisco
Secret mysecret
DupInterval 0
</Client>
<Client 128.23.11.1>
Identifier NAS-vpn
NasType Cisco
Secret mysecret
DupInterval 0
</Client>
<Realm DEFAULT>
AuthByPolicy ContinueAlways
<AuthBy SQL>
DBSource dbi:mysql:radiuslogs
DBUsername myuser
DBAuth mypasswd
AuthSelect
AccountingTable ACCOUNTING
AcctColumnDef USERNAME,User-Name
AcctColumnDef TIME_STAMP,Timestamp,integer
AcctColumnDef ACCTSTATUSTYPE,Acct-Status-Type
AcctColumnDef ACCTDELAYTIME,Acct-Delay-Time,integer
AcctColumnDef ACCTINPUTOCTETS,Acct-Input-Octets,integer
AcctColumnDef ACCTOUTPUTOCTETS,Acct-Output-Octets,integer
AcctColumnDef ACCTINPUTPACKETS,Acct-Input-Packets,integer
AcctColumnDef ACCTOUTPUTPACKETS,Acct-Output-Packets,integer
AcctColumnDef ACCTSESSIONID,Acct-Session-Id
AcctColumnDef ACCTSESSIONTIME,Acct-Session-Time,integer
AcctColumnDef ACCTTERMINATECAUSE,Acct-Terminate-Cause
AcctColumnDef NASIDENTIFIER,NAS-Identifier
AcctColumnDef NASIPADDRESS,NAS-IP-Address
AcctColumnDef NASPORT,NAS-Port,integer
AcctColumnDef FRAMEDIPADDRESS,Framed-IP-Address
AcctColumnDef CALLEDSTATIONID,Called-Station-Id
AcctColumnDef CALLINGSTATIONID,Calling-Station-Id
AcctColumnDef ACCTAUTHENTIC,Acct-Authentic
AcctColumnDef CLASS,Class
AcctColumnDef ACCOUNTSESSIONTIME,Acct-Session-Time,integer
AcctColumnDef TUNNELCLIENTENDPOINT,Tunnel-Client-Endpoint
AcctFailedLogFileName %L/%{Client:Identifier}/%m%d%y.missedaccountin.log
</AuthBy>
<AuthBy UNIX>
Filename /etc/shadow
</AuthBy>
# Log accounting to a detail file
AcctLogFileName %L/%{Client:Identifier}/%m%d%y.log
</Realm>
********************TRACE 4*********************************
Tue Aug 6 11:39:11 2002: INFO: Server started: Radiator 3.1 on myserver
Tue Aug 6 11:40:12 2002: DEBUG: Packet dump:
*** Received from xxx.xxx.xxx.2 port 1645 ....
Code: Access-Request
Identifier: 178
Authentic: <184>%<146><135><190><24><199><31>WXS<2><161><205>!<211>
Attributes:
NAS-IP-Address = xxx.xxx.xxx.2
NAS-Port = 59
NAS-Port-Type = Async
User-Name = "username"
Called-Station-Id = "yyyyyyy"
Calling-Station-Id = "??????????"
User-Password = "<140><228><235><243>Z<27><21><136>5%<208><6><229>y<213><140>"
Service-Type = Framed-User
Framed-Protocol = PPP
Tue Aug 6 11:40:12 2002: DEBUG: Handling request with Handler 'Realm=DEFAULT'
Tue Aug 6 11:40:12 2002: DEBUG: Deleting session for username, xxx.xxx.xxx.2, 59
Tue Aug 6 11:40:12 2002: DEBUG: Handling with Radius::AuthSQL
Tue Aug 6 11:40:12 2002: DEBUG: Handling with Radius::AuthUNIX:
Tue Aug 6 11:40:12 2002: DEBUG: Radius::AuthUNIX looks for match with username
Tue Aug 6 11:40:12 2002: DEBUG: Radius::AuthUNIX ACCEPT:
Tue Aug 6 11:40:12 2002: DEBUG: Access accepted for username
Tue Aug 6 11:40:12 2002: DEBUG: Packet dump:
*** Sending to xxx.xxx.xxx.2 port 1645 ....
Code: Access-Accept
Identifier: 178
Authentic: <184>%<146><135><190><24><199><31>WXS<2><161><205>!<211>
Attributes:
Tue Aug 6 11:40:13 2002: DEBUG: Packet dump:
*** Received from xxx.xxx.xxx.2 port 1646 ....
Code: Accounting-Request
Identifier: 179
Authentic: ;<155><137><244>LD<216>C}<180><1><161><220><26>{<143>
Attributes:
NAS-IP-Address = xxx.xxx.xxx.2
NAS-Port = 59
NAS-Port-Type = Async
User-Name = "username"
Called-Station-Id = "yyyyyyy"
Calling-Station-Id = "??????????"
Acct-Status-Type = Start
Acct-Authentic = RADIUS
Service-Type = Framed-User
Acct-Session-Id = "000001EB"
Framed-Protocol = PPP
Acct-Link-Count = 1
Acct-Multi-Session-Id = "65"
Framed-IP-Address = xxx.xxx.xxx.33
Acct-Delay-Time = 0
Tue Aug 6 11:40:13 2002: DEBUG: Handling request with Handler 'Realm=DEFAULT'
Tue Aug 6 11:40:13 2002: DEBUG: Adding session for username, xxx.xxx.xxx.2, 59
Tue Aug 6 11:40:13 2002: DEBUG: Handling with Radius::AuthSQL
Tue Aug 6 11:40:13 2002: DEBUG: Handling accounting with Radius::AuthSQL
Tue Aug 6 11:40:13 2002: DEBUG: do query is: insert into ACCOUNTING
(USERNAME, TIME_STAMP, ACCTSTATUSTYPE, ACCTDELAYTIME, ACCTSESSIONID, NASIPADDRESS, NASPORT, FRAMEDIPADDRESS, CALLEDSTATIONID, CALLINGSTATIONID, ACCTAUTHENTIC)
values
('username', 1028648413, 'Start', 0, '000001EB', 'xxx.xxx.xxx.2', 59, 'xxx.xxx.xxx.33', 'yyyyyyy', '??????????', 'RADIUS')
Tue Aug 6 11:40:13 2002: DEBUG: Handling with Radius::AuthUNIX:
Tue Aug 6 11:40:13 2002: DEBUG: Accounting accepted
Tue Aug 6 11:40:13 2002: DEBUG: Packet dump:
*** Sending to xxx.xxx.xxx.2 port 1646 ....
Code: Accounting-Response
Identifier: 179
Authentic: ;<155><137><244>LD<216>C}<180><1><161><220><26>{<143>
Attributes:
Tue Aug 6 11:40:32 2002: DEBUG: Packet dump:
*** Received from xxx.xxx.xxx.2 port 1646 ....
Code: Accounting-Request
Identifier: 180
Authentic: <152><177><241>h<203>Z<201><247>nf<242><223><184><183>9<134>
Attributes:
NAS-IP-Address = xxx.xxx.xxx.2
NAS-Port = 59
NAS-Port-Type = Async
User-Name = "username"
Called-Station-Id = "yyyyyyy"
Calling-Station-Id = "??????????"
Acct-Status-Type = Stop
Acct-Authentic = RADIUS
Service-Type = Framed-User
Acct-Session-Id = "000001EB"
Framed-Protocol = PPP
Acct-Link-Count = 1
Acct-Multi-Session-Id = "65"
Framed-IP-Address = xxx.xxx.xxx.33
Acct-Terminate-Cause = User-Request
Acct-Input-Octets = 1202
Acct-Output-Octets = 339
Acct-Input-Packets = 33
Acct-Output-Packets = 11
Acct-Session-Time = 18
Acct-Delay-Time = 0
Tue Aug 6 11:40:32 2002: DEBUG: Handling request with Handler 'Realm=DEFAULT'
Tue Aug 6 11:40:32 2002: DEBUG: Deleting session for username, xxx.xxx.xxx.2, 59
Tue Aug 6 11:40:32 2002: DEBUG: Handling with Radius::AuthSQL
Tue Aug 6 11:40:32 2002: DEBUG: Handling accounting with Radius::AuthSQL
Tue Aug 6 11:40:32 2002: DEBUG: do query is: insert into ACCOUNTING
(USERNAME, TIME_STAMP, ACCTSTATUSTYPE, ACCTDELAYTIME, ACCTINPUTOCTETS, ACCTOUTPUTOCTETS, ACCTINPUTPACKETS, ACCTOUTPUTPACKETS, ACCTSESSIONID, ACCTSESSIONTIME, ACCTTERMINATECAUSE, NASIPADDRESS, NASPORT, FRAMEDIPADDRESS, CALLEDSTATIONID, CALLINGSTATIONID, ACCTAUTHENTIC, ACCOUNTSESSIONTIME)
values
('username', 1028648432, 'Stop', 0, 1202, 339, 33, 11, '000001EB', 18, 'User-Request', 'xxx.xxx.xxx.2', 59, 'xxx.xxx.xxx.33', 'yyyyyyy', '??????????', 'RADIUS', 18)
Tue Aug 6 11:40:32 2002: DEBUG: Handling with Radius::AuthUNIX:
Tue Aug 6 11:40:32 2002: DEBUG: Accounting accepted
Tue Aug 6 11:40:32 2002: DEBUG: Packet dump:
*** Sending to xxx.xxx.xxx.2 port 1646 ....
Code: Accounting-Response
Identifier: 180
Authentic: <152><177><241>h<203>Z<201><247>nf<242><223><184><183>9<134>
Attributes:
----- Original Message -----
From: Hugh Irvine
To: Steve Caporossi
Cc: radiator at open.com.au
Sent: Monday, August 05, 2002 7:21 PM
Subject: Re: (RADIATOR) Help getting MySQL Accounting working
Hello Steve -
I will need to see a copy of the current configuration file (no secrets) together with a trace 4 debug showing what is happening. You should be aware that the Timestamp is the time on the Radiator host that is automatically corrected for any Acct-Delay-Time included in the accounting request.
regards
Hugh
On Tuesday, August 6, 2002, at 05:17 AM, Steve Caporossi wrote:
Thanks Hugh.
I have a couple more questions, Why is the timestamp inserted with the incorrect date? I tried all the permutations of the timestamp but, I either get 0000-00-00 00:00:00 or an incorrect date such as 2000-10-28 57:17:65 which is from 5 minutes ago.
Thanks,
Steve
--
Radiator: the most portable, flexible and configurable RADIUS server
anywhere. Available on *NIX, *BSD, Windows 95/98/2000, NT, MacOS X.
-
Nets: internetwork inventory and management - graphical, extensible,
flexible with hardware, software, platform and database independence.
--
Radiator: the most portable, flexible and configurable RADIUS server
anywhere. Available on *NIX, *BSD, Windows 95/98/2000, NT, MacOS X.
-
Nets: internetwork inventory and management - graphical, extensible,
flexible with hardware, software, platform and database independence.
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://www.open.com.au/pipermail/radiator/attachments/20020807/e6c9c0dc/attachment.html>
More information about the radiator
mailing list