(RADIATOR) start_date calculation directly in the accounting configuration file

[onrubia carlos]

Hello,


I have read some messages in the mailing list on the 'timestamp' attribute
given by radiator
to the accounting requests.
If I am not wrong this value is the UTC time from the radiator server minus
the Acct-Delay-Time
(if present) of the received accounting request.

I would like to know if it is easy to calculate the start_time of the
session from an accounting
request using the Acct-Session-Time attribute (without using any hooks).
This is to have the start_date in the onlineusers table.

My present accounting configuration file is:


<SessionDatabase SQL>

 Identifier SkynetOnline

 DBSource dbi:Oracle:xxxxxxx
 DBUsername xxxx
 DBAuth  xxxx
 #DBAuth  xxxx
 #DBSource dbi:mysql:xxxxxxxxxx
 #DBUsername xxxx
 #DBAuth  xxxx
 Timeout  1

 AddQuery INSERT into onlineusers (login, \
                                                nas_id, \
                                                session_id, \
                                                time_stamp, \
                                                framed_ip, \
                                                nas_port_type, \
                                                service_type, \
                                                called_id, \
                                                caller_id, \
                                                nasidport, \
                                                radsrv, \
                                                status_type, \
      radtype, \
      cnx_type) \
                                       VALUES ('%n', \
                                               '%N', \
                                               '%{Acct-Session-Id}', \

(to_date('01-JAN-1970','DD-MON-YYYY')+%{Timestamp}/86400), \
                                               '%{Framed-IP-Address}', \
                                               '%{NAS-Port-Type}', \
                                               '%{Service-Type}', \
                                               '%{Calling-Station-Id}', \
                                               '%{Calling-Station-Id}', \
                                               '%N %{NAS-Port}', \
                                               'radius_name.isp.belgacom.be',
\
                                               '%{Acct-Status-Type}', \
      %{BGCBitType}, \
      '%{Connect-Info}')
 DeleteQuery DELETE FROM onlineusers WHERE nasidport='%N %{NAS-Port}'
 ClearNasQuery DELETE FROM onlineusers WHERE nas_id = '%N'
 CountQuery SELECT nas_id, 0 AS nas_port, session_id FROM onlineusers WHERE
login = '%n'

</SessionDatabase>



This SessionDatabase is used for start accounting requests and also update
accounting requests.
This works fine but the timestamp in the DB is only the start_time when a
start accounting request is
treated.
When an update accounting request is received, the timestamp value in the
onlineusers table is no more the start_time.

I have tried to modify this part of the configuration file using the
Acct-Session-Time in the following way:

VALUES ('%n', \
       '%N', \
       '%{Acct-Session-Id}', \

(to_date('01-JAN-1970','DD-MON-YYYY')+(%{Timestamp}-%{Acct-Session-Time})/86400),
\


  ==> calculation...

But...if the attribute is not present...the insert can not be done (always
the case for a start).


Tue Jun 17 15:51:35 2008 339409: ERR: do failed for 'INSERT into
onlineusers_test (login, nas_id, session_id, time_stamp, framed_ip,
nas_port_type, service_type, called_id, caller_id, nasidport, radsrv,
status_type, radtype, cnx_type, start_date) VALUES ('fa051981', '
192.168.255.142', '17062008-155135',
(to_date('01-JAN-1970','DD-MON-YYYY')+1213710695/86400), '17.6.51.1',
'Virtual', 'Framed-User', '*BAS-CHARLEROI*17/1*215*159',
'*BAS-CHARLEROI*17/1*215*159', '192.168.255.142 17065135', '
radius011.isp.belgacom.be', 'Start', 4, 'user-speed-go',
(to_date('01-JAN-1970','DD-MON-YYYY')+(1213710695-)/86400))': ORA-00936:
missing expression (DBD ERROR:
OCIStmtExecute)

 ==>       No values after the minus sign:
 (to_date('01-JAN-1970','DD-MON-YYYY')+(1213710695-)/86400))'

Can you help me? How to avoid the error if the attribute is not present?


Thanks in advance,


Carlos,
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://www.open.com.au/pipermail/radiator/attachments/20080617/f6d89436/attachment.html>