(RADIATOR) Help getting MySQL Accounting working
Frank Danielson
fdanielson at dataonair.com
Wed Aug 7 10:15:00 CDT 2002
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
<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 <mailto:hugh at open.com.au>
To: Steve Caporossi <mailto:capoross at musc.edu>
Cc: radiator at open.com.au <mailto: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:
<?fontfamily><?param Arial><?smaller>Hugh,<?/smaller><?/fontfamily>
<?fontfamily><?param Arial><?smaller>Attached is my config and Trace 4. I
did download and install the timedate module from CPAN.
<?/smaller><?/fontfamily>
<?fontfamily><?param Arial><?smaller>Thanks, Steve<?/smaller><?/fontfamily>
<?fontfamily><?param Arial><?smaller># 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.
#
#<?/smaller><?/fontfamily>
<?fontfamily><?param Arial><?smaller>#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<?/smaller><?/fontfamily>
<?fontfamily><?param Arial><?smaller># Add other Clients below...
<Client 128.23.10.1>
Identifier NASPPP
NasType Cisco
Secret mysecret
DupInterval 0
</Client><?/smaller><?/fontfamily>
<?fontfamily><?param Arial><?smaller><Client 128.23.10.2>
Identifier NASPPP
NasType Cisco
Secret mysecret
DupInterval 0
</Client><?/smaller><?/fontfamily>
<?fontfamily><?param Arial><?smaller><Client 128.23.11.1>
Identifier NAS-vpn
NasType Cisco
Secret mysecret
DupInterval 0
</Client><?/smaller><?/fontfamily>
<?fontfamily><?param Arial><?smaller><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: <?color><?param 1999,1999,FFFF>Hugh Irvine<?/color>
To: <?color><?param 1999,1999,FFFF>Steve Caporossi<?/color>
Cc: <?color><?param 1999,1999,FFFF>radiator at open.com.au<?/color>
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.
<?/smaller><?/fontfamily>
--
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/b6708350/attachment.html>
More information about the radiator
mailing list