Hi list!
I have tested authentication in the database using mysql, and it
works fine now, my next problem here is ACCOUNTING, I have set MAXTIME in
the database equal to 3600 (Session-Timeout). My question is, how will I
be able to deduct the time used by the users ?
Below is a fragment of radius.cfg , default in SQL :
<AuthBy SQL>
Identifier SQL
DBSource dbi:mysql:radius
DBUsername root
DBAuth
AuthColumnDef 0, User-Password, check
AuthColumnDef 1, GENERIC, check
AuthColumnDef 2, Session-Timeout, reply
AuthSelect select password, CHECKATTR, MAXTIME from SUBSCRIBERS
where Ausername='%n'
AccountingStopsOnly
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 ACCTSESSIONID,Acct-Session-Id
AcctColumnDef ACCTSESSIONTIME,Acct-Session-Time,integer
AcctColumnDef ACCTTERMINATECAUSE,Acct-Terminate-Cause
AcctColumnDef NASIDENTIFIER,NAS-Identifier
AcctColumnDef NASPORT,NAS-Port,integer
AcctColumnDef FRAMEDIPADDRESS,Framed-IP-Address
AcctColumnDef TIME_STAMP,Timestamp,formatted-date,to_date(''%e %m%Y %
AcctSQLStatement update ACCOUNTING set
MAXTIME=MAXTIME-0%{Acct-Session-Time},OCTETSINLEFT=OCTETSINLEFT-0%{Acct-Input-Octets},
OCTETSOUTLEFT=OCTETSOUTLEFT=OCTETSOUTLEFT-0%{Acct-Output-Octets}
where USERNAME='%n'
</AuthBy SQL>
<SessionDatabase SQL>
Attached also is the logfile when I run radpwtst using the above
config.
Please help.
thanks,
Rose
Tue Nov 9 19:12:39 1999: DEBUG: Packet dump:
*** Received from 127.0.0.1 port 1382 ....
Code: Access-Request
Identifier: 9
Authentic: 1234567890123456
Attributes:
User-Name = "rosetst"
Service-Type = Framed-User
NAS-IP-Address = 203.63.154.1
NAS-Port = 1234
NAS-Port-Type = Async
User-Password = "<139><228>,<200><175>\<4><246><188>8<9><160><216>}x<153>"
Tue Nov 9 19:12:39 1999: DEBUG: Handling request with Handler 'Realm=DEFAULT'
Tue Nov 9 19:12:39 1999: DEBUG: Deleting session for rosetst, 203.63.154.1, 1234
Tue Nov 9 19:12:39 1999: DEBUG: do query is: delete from RADONLINE where
NASIDENTIFIER='203.63.154.1' and NASPORT=1234
Tue Nov 9 19:12:39 1999: DEBUG: Handling with Radius::AuthSQL
Tue Nov 9 19:12:39 1999: DEBUG: Handling with Radius::AuthSQL
Tue Nov 9 19:12:39 1999: DEBUG: Query is: select password, CHECKATTR, MAXTIME from
SUBSCRIBERS where username='rosetst'
Tue Nov 9 19:12:39 1999: DEBUG: Radius::AuthSQL looks for match with rosetst
Tue Nov 9 19:12:39 1999: DEBUG: Radius::AuthSQL ACCEPT:
Tue Nov 9 19:12:39 1999: DEBUG: Access accepted for rosetst
Tue Nov 9 19:12:39 1999: DEBUG: Packet dump:
*** Sending to 127.0.0.1 port 1382 ....
Code: Access-Accept
Identifier: 9
Authentic: 1234567890123456
Attributes:
Session-Timeout = 3600
Tue Nov 9 19:12:39 1999: DEBUG: Packet dump:
*** Received from 127.0.0.1 port 1382 ....
Code: Accounting-Request
Identifier: 10
Authentic: <169><176>U$Y<164><127>h}<29>Q<172><236>K<178>b
Attributes:
User-Name = "rosetst"
Service-Type = Framed-User
NAS-IP-Address = 203.63.154.1
NAS-Port = 1234
NAS-Port-Type = Async
Acct-Session-Id = "00001234"
Acct-Status-Type = Start
Tue Nov 9 19:12:39 1999: DEBUG: Handling request with Handler 'Realm=DEFAULT'
Tue Nov 9 19:12:39 1999: DEBUG: Adding session for rosetst, 203.63.154.1, 1234
Tue Nov 9 19:12:39 1999: DEBUG: do query is: delete from RADONLINE where
NASIDENTIFIER='203.63.154.1' and NASPORT=1234
Tue Nov 9 19:12:39 1999: DEBUG: do query is: insert into RADONLINE (USERNAME,
NASIDENTIFIER, NASPORT, ACCTSESSIONID, TIME_STAMP, FRAMEDIPADDRESS, NASPORTTYPE,
SERVICETYPE) values ('rosetst', '203.63.154.1', 1234, '00001234', 942145959, '',
'Async', 'Framed-User')
Tue Nov 9 19:12:39 1999: DEBUG: Handling with Radius::AuthSQL
Tue Nov 9 19:12:39 1999: DEBUG: Handling accounting with Radius::AuthSQL
Tue Nov 9 19:12:39 1999: DEBUG: Accounting accepted
Tue Nov 9 19:12:39 1999: DEBUG: Packet dump:
*** Sending to 127.0.0.1 port 1382 ....
Code: Accounting-Response
Identifier: 10
Authentic: <169><176>U$Y<164><127>h}<29>Q<172><236>K<178>b
Attributes:
Tue Nov 9 19:12:39 1999: DEBUG: Packet dump:
*** Received from 127.0.0.1 port 1382 ....
Code: Accounting-Request
Identifier: 11
Authentic: <0><15><152>H<196><177><194><165><160>-<252><243>?.0<129>
Attributes:
User-Name = "rosetst"
Service-Type = Framed-User
NAS-IP-Address = 203.63.154.1
NAS-Port = 1234
NAS-Port-Type = Async
Acct-Session-Id = "00001234"
Acct-Status-Type = Stop
Acct-Delay-Time = 0
Acct-Session-Time = 1000
Acct-Input-Octets = 20000
Acct-Output-Octets = 30000
Tue Nov 9 19:12:39 1999: DEBUG: Handling request with Handler 'Realm=DEFAULT'
Tue Nov 9 19:12:39 1999: DEBUG: Deleting session for rosetst, 203.63.154.1, 1234
Tue Nov 9 19:12:39 1999: DEBUG: do query is: delete from RADONLINE where
NASIDENTIFIER='203.63.154.1' and NASPORT=1234
Tue Nov 9 19:12:39 1999: DEBUG: Handling with Radius::AuthSQL
Tue Nov 9 19:12:39 1999: DEBUG: Handling accounting with Radius::AuthSQL
Tue Nov 9 19:12:39 1999: DEBUG: do query is: update ACCOUNTING set
MAXTIME=MAXTIME-01000, OCTETSINLEFT=OCTETSINLEFT-020000,
OCTETSOUTLEFT=OCTETSOUTLEFT-030000 where USERNAME='rosetst'
Tue Nov 9 19:12:39 1999: ERR: do failed for 'update ACCOUNTING set
MAXTIME=MAXTIME-01000, OCTETSINLEFT=OCTETSINLEFT-020000,
OCTETSOUTLEFT=OCTETSOUTLEFT-030000 where USERNAME='rosetst'': Unknown column 'MAXTIME'
in 'field list'
Tue Nov 9 19:12:39 1999: ERR: do failed for 'update ACCOUNTING set
MAXTIME=MAXTIME-01000, OCTETSINLEFT=OCTETSINLEFT-020000,
OCTETSOUTLEFT=OCTETSOUTLEFT-030000 where USERNAME='rosetst'': Unknown column 'MAXTIME'
in 'field list'
