(RADIATOR) start_date calculation directly in the accounting configuration file

[Hugh Irvine]

Hello Carlos -

You just need to put a "0" prefix so you always get a number, for  
example:

	0%{Acct-Session-Time}

regards

Hugh


On 18 Jun 2008, at 00:05, onrubia carlos wrote:

> 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,
>



NB:

Have you read the reference manual ("doc/ref.html")?
Have you searched the mailing list archive (www.open.com.au/archives/ 
radiator)?
Have you had a quick look on Google (www.google.com)?
Have you included a copy of your configuration file (no secrets),
together with a trace 4 debug showing what is happening?
Have you checked the RadiusExpert wiki:
http://www.open.com.au/wiki/index.php/Main_Page

-- 
Radiator: the most portable, flexible and configurable RADIUS server
anywhere. Available on *NIX, *BSD, Windows, MacOS X.
Includes support for reliable RADIUS transport (RadSec),
and DIAMETER translation agent.
-
Nets: internetwork inventory and management - graphical, extensible,
flexible with hardware, software, platform and database independence.
-
CATool: Private Certificate Authority for Unix and Unix-like systems.


--
Archive at http://www.open.com.au/archives/radiator/
Announcements on radiator-announce at open.com.au
To unsubscribe, email 'majordomo at open.com.au' with
'unsubscribe radiator' in the body of the message.