(RADIATOR) How to time-stamp SQL Accounting database w/o timestamp attribute

Mary Grace Sun, 27 Jul 2003 20:24:42 -0700

Hello list! Hugh Irvine has been very kind and helped us tremendously in getting AuthBy SQL going with MySQL. Hopefully Hugh will not think we are as clueless now as he probably has been thinking, but compared to you all we are still really un-clued. However, one little thing is not working, and I wondered if anyone on the list has a neat way they would share for how to timestamp each accounting record in the MySQL database when both the attributes Timestamp and Event-Timestamp are missing from the incoming accounting packets the NAS sends. Some of the NAS from whom we receive packets send this timestamping, and some do not.

Although we have muddled through and customised the tables a bit, none of us are MySQL gurus and have diligently searched the Archives of this List, lest Hugh scold us yet again :-)

Could someone please share a clever method with us that we could use? Our Trace 4 output and the radius cfg is given below. If you notice start-date commented out, it is because it does not work :-( Thank you!

Sr. Mary Grace

PS - is this the correct way to have all AuthBy Radius packets, auth and acctg, forwarded to a remote radius client to be auth'ed and have acctg packets copied to them, while still being able to auth the packets ourselves of the remote site does not answer, AND have a copy for accounting purposes of everything that goes to the remote site to also be copied into our accounting tables in MySQL?

Thanks :-)

��Foreground

LogStdout

Trace     5

PidFile C:radius\radiusd.pid

BindAddress 64.137.199.1

AuthPort       1645

AcctPort       1646

LogDir C:\radius\radlogs

DbDir     C:\radius

LogFile        %L/logfile-64-137-199-4.log

DictionaryFile %D/dictionary

<Client 216.99.225.49>

       Secret plurality

</Client>

<Realm ms.edu>

RewriteUsername tr/[A-Z]/[a-z]/

AcctLogFileName %L/detail-199-ms.log

PasswordLogFileName %L/password-199-ms.log

<AuthBy SQL>

DBSource dbi:mysql:grace:64.137.199.4:3306

DBUsername humility

DBAuth chastity

AccountingTable accounting%Y%m

AcctColumnDef user_name,User-Name

AcctColumnDef nas_ip_address,NAS-IP-Address

AcctColumnDef nas_port,NAS-Port

AcctColumnDef service_type,Service-Type

AcctColumnDef 
framed_protocol,Framed-Protocol

AcctColumnDef 
framed_ip_address,Framed-IP-Address

AcctColumnDef class,Class

AcctColumnDef 
called_station_id,Called-Station-Id

AcctColumnDef 
calling_station_id,Calling-Station-Id

AcctColumnDef 
acct_status_type,Acct-Status-Type

AcctColumnDef 
acct_delay_time,Acct-Delay-Time

AcctColumnDef 
acct_input_octets,Acct-Input-Octets

AcctColumnDef 
acct_output_octets,Acct-Output-Octets

AcctColumnDef 
acct_session_id,Acct-Session-Id

AcctColumnDef acct_authentic,Acct-Authentic

AcctColumnDef 
acct_session_time,Acct-Session-Time,integer

#AcctColumnDef 
start_time,%b-%0{Acct-Session-Time},literal

AcctColumnDef 
acct_input_packets,Acct-Input-Packets

AcctColumnDef 
acct_output_packets,Acct-Output-Packets

AcctColumnDef 
acct_terminate_cause,Acct-Terminate-Cause

AcctColumnDef 
event_timestamp,Event-Timestamp,integer

AcctColumnDef nas_port_type,NAS-Port-Type

AcctColumnDef connect_info,Connect-Info

AcctColumnDef 
ascend_pre_input_octets,Ascend-Pre-Input-Octets

AcctColumnDef 
ascend_pre_output_octets,Ascend-Pre-Output-Octets

AcctColumnDef 
ascend_pre_input_packets,Ascend-Pre-Input-Packets

AcctColumnDef 
ascend_pre_output_packets,Ascend-Pre-Output-Packets

AcctColumnDef 
ascend_disconnect_cause,Ascend-Disconnect-Cause

AcctColumnDef 
ascend_connect_progress,Ascend-Connect-Progress

AcctColumnDef 
ascend_data_rate,Ascend-Data-Rate

AcctColumnDef 
ascend_presession_time,Ascend-PreSession-Time,integer

AcctColumnDef 
ascend_xmit_rate,Ascend-Xmit-Rate

AcctColumnDef timestamp,Timestamp,integer

DefaultSimultaneousUse 1000

RejectEmptyPassword

</AuthBy>

#<AuthBy FILE>

#Filename     C:\radius\users-ms.wri

#DefaultSimultaneousUse 1000

#RejectEmptyPassword

#</AuthBy>

</Realm>

<Realm catholic.org>

RewriteUsername tr/[A-Z]/[a-z]/

AcctLogFileName %L/detail-199-catholic.log

PasswordLogFileName 
%L/password-199-catholic.log

<AuthBy SQL>

DBSource 
dbi:mysql:vatican:64.137.199.4:3306

DBUsername pius

DBAuth ascension

AccountingTable acct_catholic_com%Y%m

AcctColumnDef user_name,User-Name

AcctColumnDef nas_ip_address,NAS-IP-Address

AcctColumnDef nas_port,NAS-Port

AcctColumnDef service_type,Service-Type

AcctColumnDef 
framed_protocol,Framed-Protocol

AcctColumnDef 
framed_ip_address,Framed-IP-Address

AcctColumnDef class,Class

AcctColumnDef 
called_station_id,Called-Station-Id

AcctColumnDef 
calling_station_id,Calling-Station-Id

AcctColumnDef 
acct_status_type,Acct-Status-Type

AcctColumnDef 
acct_delay_time,Acct-Delay-Time

AcctColumnDef 
acct_input_octets,Acct-Input-Octets

AcctColumnDef 
acct_output_octets,Acct-Output-Octets

AcctColumnDef 
acct_session_id,Acct-Session-Id

AcctColumnDef acct_authentic,Acct-Authentic

AcctColumnDef 
acct_session_time,Acct-Session-Time,integer

#AcctColumnDef 
start_time,%b-%0{Acct-Session-Time},literal

AcctColumnDef 
acct_input_packets,Acct-Input-Packets

AcctColumnDef 
acct_output_packets,Acct-Output-Packets

AcctColumnDef 
acct_terminate_cause,Acct-Terminate-Cause

AcctColumnDef 
event_timestamp,Event-Timestamp,integer

AcctColumnDef nas_port_type,NAS-Port-Type

AcctColumnDef connect_info,Connect-Info

AcctColumnDef 
ascend_pre_input_octets,Ascend-Pre-Input-Octets

AcctColumnDef 
ascend_pre_output_octets,Ascend-Pre-Output-Octets

AcctColumnDef 
ascend_pre_input_packets,Ascend-Pre-Input-Packets

AcctColumnDef 
ascend_pre_output_packets,Ascend-Pre-Output-Packets

AcctColumnDef 
ascend_disconnect_cause,Ascend-Disconnect-Cause

AcctColumnDef 
ascend_connect_progress,Ascend-Connect-Progress

AcctColumnDef 
ascend_data_rate,Ascend-Data-Rate

AcctColumnDef 
ascend_presession_time,Ascend-PreSession-Time,integer

AcctColumnDef 
ascend_xmit_rate,Ascend-Xmit-Rate

AcctColumnDef timestamp,Timestamp,integer

DefaultSimultaneousUse 1000

RejectEmptyPassword

</AuthBy>

<AuthBy RADIUS>

Host 1.2.3.4

Secret perseverance

AuthPort 1812

AcctPort 1813

</AuthBy>

</Realm>

<Log FILE>

Filename %L/backuplog64-137-199-4.log

Trace 5

</Log>



*****************************************************************************************

Attributes:

        User-Name = "[EMAIL PROTECTED]"

        NAS-IP-Address = 170.147.100.72

        NAS-Port = 40

        Service-Type = Framed-User

        Framed-Protocol = PPP

        Framed-IP-Address = 205.184.197.150

        Class = 
"BWS<0><1><0><14>/<0>&<0><1><2><26><1><1><0><0><0><1>
 <209>PP<0><0><0

><0><0><0><0><0><0><0><0><0><1><1>"

        Class = 
"BWS<0><1><0><14>/<0><19><0><1><3><7><1><0><0>Z<143>"

        Called-Station-Id = "9859230108"

        Calling-Station-Id = "3039630062"

        Acct-Status-Type = Start

        Acct-Delay-Time = 0

        Acct-Session-Id = "00026308"

        Acct-Authentic = RADIUS

        NAS-Port-Type = Async

        Connect-Info = "28800/26400 
V34+/V42bis/LAPM (26400)"

        Ascend-Connect-Progress = 60



Sun Jul 27 22:46:35 2003: DEBUG: Handling 
request with Handler 'Realm=ms.edu'

Sun Jul 27 22:46:35 2003: DEBUG: Rewrote 
user name to [EMAIL PROTECTED]

Sun Jul 27 22:46:35 2003: DEBUG:  Adding 
session for [EMAIL PROTECTED], 170.147.100.72

, 40

Sun Jul 27 22:46:35 2003: DEBUG: Handling 
with Radius::AuthSQL

Sun Jul 27 22:46:35 2003: DEBUG: Handling 
accounting with Radius::AuthSQL

Sun Jul 27 22:46:35 2003: DEBUG: do query 
is: 'insert into accounting200307 (ascend_c

onnect_progress,nas_port,acct_authentic,service_type,acct_session_id,user_name,nas_ip

_address,connect_info,class,nas_port_type,calling_station_id,called_station

_id,acct_delay_time,acct_status_type,framed_ip_address,framed_protoco

l) values 
('60','40','RADIUS','Framed-User','00026308','[EMAIL
 PROTECTED]','170.147.10

0.72','28800/26400 V34+/V42bis/LAPM 
(26400)','BWS\0:&\0k&/\0&\0:&;&\Z:&:&\0\0\0:& 
d%PP\0\0\0\

0\0\0\0\0\0\0\0\0:&:&','Async','3039630062','9859230108','0','Start','205.18

4.197.150','PPP')':



Sun Jul 27 22:46:35 2003: DEBUG: 
Accounting accepted

Sun Jul 27 22:46:35 2003: DEBUG: Packet 
dump:

*** Sending to 170.147.97.59 port 47938 
....



Packet length = 20

05 0a 00 14 86 18 3f b7 81 7f 8b 42 98 d3 
ab 5e

83 f0 ba 6f

Code:       Accounting-Response

Identifier: 10

Authentic:  
<20><27><221><223><7><192><14><240><169><166><216><132><164><210><215><24

1>

Attributes:



Sun Jul 27 22:46:37 2003: DEBUG: Packet 
dump:

*** Received from 170.147.97.59 port 47975 
....



Packet length = 321

04 2f 01 41 af 77 33 f2 76 52 c6 ed 73 32 
9b d9

f5 97 0e 5e 01 12 69 63 67 63 6f 6d 6d 40 
72 69

76 61 2e 6e 65 74 04 06 aa 93 64 48 05 06 
00 00

00 28 06 06 00 00 00 02 07 06 00 00 00 01 
08 06

cd b8 c5 96 19 28 42 57 53 00 01 00 0e 2f 
00 26

00 01 02 1a 01 01 00 00 00 01 20 d1 50 50 
00 00

00 00 00 00 00 00 00 00 00 00 01 01 19 15 
42 57

53 00 01 00 0e 2f 00 13 00 01 03 07 01 00 
00 5a

8f 1e 0c 39 38 35 39 32 33 30 31 30 38 1f 
0c 33

30 33 39 36 33 30 30 36 32 28 06 00 00 00 
02 29

06 00 00 00 00 2a 06 00 00 00 69 2b 06 00 
00 00

69 2c 0a 30 30 30 32 36 33 30 38 2d 06 00 
00 00

01 2e 06 00 00 00 03 2f 06 00 00 00 07 30 
06 00

00 00 07 31 06 00 00 00 01 37 06 3f 24 8e 
8e 3d

06 00 00 00 00 4d 26 32 38 38 30 30 2f 32 
36 34

30 30 20 56 33 34 2b 2f 56 34 32 62 69 73 
2f 4c

41 50 4d 20 28 32 36 34 30 30 29 be 06 00 
00 00

7f bf 06 00 00 00 71 c0 06 00 00 00 04 c1 
06 00

00 00 04 c3 06 00 00 00 2d c4 06 00 00 00 
3c c5

06 00 00 67 20 c6 06 00 00 00 17 ff 06 00 
00 70

80

Code:       Accounting-Request

Identifier: 47

Authentic:  
<175>w3<242>vR<198><237>s2<155><217><245><151><14>^

Attributes:

        User-Name = "[EMAIL PROTECTED]"

        NAS-IP-Address = 170.147.100.72

        NAS-Port = 40

        Service-Type = Framed-User

        Framed-Protocol = PPP

        Framed-IP-Address = 205.184.197.150

        Class = 
"BWS<0><1><0><14>/<0>&<0><1><2><26><1><1><0><0><0><1>
 <209>PP<0><0><0

><0><0><0><0><0><0><0><0><0><1><1>"

        Class = 
"BWS<0><1><0><14>/<0><19><0><1><3><7><1><0><0>Z<143>"

        Called-Station-Id = "9859230108"

        Calling-Station-Id = "3039630062"

        Acct-Status-Type = Stop

        Acct-Delay-Time = 0

        Acct-Input-Octets = 105

        Acct-Output-Octets = 105

        Acct-Session-Id = "00026308"

        Acct-Authentic = RADIUS

        Acct-Session-Time = 3

        Acct-Input-Packets = 7

        Acct-Output-Packets = 7

        Acct-Terminate-Cause = User-Request

        NAS-Port-Type = Async

        Connect-Info = "28800/26400 
V34+/V42bis/LAPM (26400)"

        Ascend-Pre-Input-Octets = 127

        Ascend-Pre-Output-Octets = 113

        Ascend-Pre-Input-Packets = 4

        Ascend-Pre-Output-Packets = 4

        Ascend-Disconnect-Cause = 45

        Ascend-Connect-Progress = 60

        Ascend-Data-Rate = 26400

        Ascend-PreSession-Time = 23

        Ascend-Xmit-Rate = 28800



Sun Jul 27 22:46:37 2003: DEBUG: Handling 
request with Handler 'Realm=ms.edu'

Sun Jul 27 22:46:37 2003: DEBUG: Rewrote 
user name to [EMAIL PROTECTED]

Sun Jul 27 22:46:37 2003: DEBUG:  Deleting 
session for [EMAIL PROTECTED], 170.147.100.72, 40

Sun Jul 27 22:46:37 2003: DEBUG: Handling 
with Radius::AuthSQL

Sun Jul 27 22:46:37 2003: DEBUG: Handling 
accounting with Radius::AuthSQL

Sun Jul 27 22:46:37 2003: DEBUG: do query 
is: 'insert into accounting200307 
(ascend_presession_time,ascend_connect_progress,acct_input_octets,ascend_pre_input_octets,ascend_pre_output_octets,nas_port,acct_authentic,service_type,acct_session_id,user_name,nas_ip_address,connect_info,class,ascend_disconnect_cause,nas_port_type,ascend_xmit_rate,timestamp,acct_input_packets,calling_station_id,called_station_id,acct_output_octets,ascend_data_rate,acct_delay_time,acct_terminate_cause,ascend_pre_output_packets,acct_status_type,acct_session_time,framed_ip_address,acct_output_packets,ascend_pre_input_packets,framed_protocol)
 
values(23,'60','105','127','113','40','RADIUS','Framed-User','00026308','[EMAIL
 PROTECTED]','170.147.100.72','28800/26400 
V34+/V42bis/LAPM 
26400)','BWS\0:&\0k&/\0&\0:&;&\Z:&:&\0\0\0:&d%PP\0\0\0\0\0\0\0\0\0\0\0\0:&:&','45','Async','28800','7','3039630062','9859230108','105',26400','0','User-Request','4','Stop',3,'205.184.197.150','7','4','PPP')':



Sun Jul 27 22:46:37 2003: DEBUG: 
Accounting accepted

Sun Jul 27 22:46:37 2003: DEBUG: Packet 
dump:

*** Sending to 170.147.97.59 port 47975 
....



Packet length = 20

05 2f 00 14 e9 d5 e9 74 c0 04 7e d3 08 a3 
41 45

af 6a b9 e3

Code:       Accounting-Response

Identifier: 47

Authentic:  
<175>w3<242>vR<198><237>s2<155><217><245><151><14>^

Attributes:

(RADIATOR) How to time-stamp SQL Accounting database w/o timestamp attribute

Reply via email to