(RADIATOR) <SessionDatabase SQL>

Hugh Irvine hugh at open.com.au
Wed May 16 07:40:57 CDT 2001


Hello Mariano -

This is in fact in the manual - section 6.2 (Radiator 2.18.1).

The first set of special characters for time operate on the current 
time on the host system, which in Perl terms is the same as UNIX - 
the number of seconds since midnight, January 1, 1970. The second set 
of special characters refer to the "Timestamp" attribute in the 
request packet, which is a derived time referring to the actual time 
of the actual event occuring, ie. corrected by the AcctDelayTime (if 
present in the packet) - again in seconds as above.

Just to be clear - the only time you will see an AcctDelayTime is in 
a retransmitted accounting packet - ie. corrected by the 
retransmission timeout that the NAS uses.

At 11:42 -0300 15/5/01, Mariano Absatz wrote:
>El 15 May 2001, a las 10:50, Hugh Irvine escribió:
>
>>
>>  Hello Mariano -
>>
>>  Why don't you use this:
>>
>>	%d%H%M%S
>
>Right, now it's:
>     AddQuery INSERT INTO USUARIOS_EN_LINEA \
>         (USU_CODIGO, VISP_CODIGO, USUA_SESION_ID, \
>         USUA_IP_NAS, POOL_NAME, USUA_PORT, USUA_BYTES, USUA_TIEMPO, \
>         USUA_HORA_CONEXION, USUA_CALL_ID, USUA_DNIS, USUA_IP_ASIGNADA) \
>         VALUES \
>         ('%U', '%R', '%{Acct-Session-Id}', \
>         '%N', 'nombre del pool', %{NAS-Port}, 0, 0, \
>         TO_DATE('%Y-%m-%d %H:%M:%S', 'YYYY-MM-DD HH24:MI:SS'), \
>         '%{Calling-Station-Id}', '%{Called-Station-Id}', 
>'%{Framed-IP-Address}')
>and it works but... where am I supposed to get things from?
>
>Where does the timestamp in this sql statement coming from?
>
>In the manual:
>
>http://www.open.com.au/radiator/ref.html#25705 (TABLE 2. DateFormat
>special characters)
>
>is giving me characters to format an (arbitrary?) epoch and
>
>http://www.open.com.au/radiator/ref.html#22600 (TABLE 1. Special string
>formatting characters)
>
>is giving me characters to insert either the current time or the current
>packet timestamp (being the later what I wanted, but would settle for the
>other)
>
>As I had it configured before
>TO_DATE('%f-%g-%i %j:%k:%p', 'YYYY-MM-DD HH24:MI:SS')
>I understood I was directly taking the current packet timestamp and
>generating 'YYYY-MM-DD HH:mm:SS' where (from the manual's Table 1)
>YYYY: The Timestamp year (4 digits)
>MM: The Timestamp month number (2 digits)
>DD: The Timestamp day of the month (2 digits)
>HH: The Timestamp hour (0-23)
>mm: The Timestamp minute (0-59)
>SS: the Timestamp second (0-59)
>     ^BTW, the lowcase "t" in the manual is inconsistent :-)
>
>The problem here was that at least %p doesn't add a leading "0".
>
>In the current configuration, I am using the fields from Table 2
>formatting an unknown date-time (I guess it's the current one).
>
>TO_DATE('%Y-%m-%d %H:%M:%S', 'YYYY-MM-DD HH24:MI:SS')
>
>As the info in the manual wasn't all that clear about it, I changed the
>date in the machine and confirmed that %m and %d also use a leading "0"
>(THAT isn't written in the manual).
>
>So now I have it working, now for the "theoretical" part of the question,
>if I had a radius attribute with an arbitrary date-time in it (say, the
>birthday -and time- of the nas manufacturer's mother) and I would like to
>put it in a column in my on-line users database, what would be the idiom
>to do it so?. That is I understand (I think) how to put it in an
>accounting database by means of a
>
>AcctColumnDef   DB_COLUMN,Radius-Attribute-Name,integer-date, \
>                 TO_DATE('%Y-%m-%d %H:%M:%S', 'YYYY-MM-DD HH24:MI:SS')
>
>but in the AddQuery (or AnythingQuery) I don't know how to use Radius-
>Attribute-Name here.

For this you would have to write some code - probably in a hook. But 
again, you need to understand if you are dealing with a numeric 
number of seconds (as above), or whether you just have some string 
that happens to represent a date in some ASCII format as you describe 
for your database. They are very different representations of 
(possibly) the same thing.

hth

Hugh

-- 

NB: I am travelling this week, so there may be delays in our correspondence.

Radiator: the most portable, flexible and configurable RADIUS server
anywhere. SQL, proxy, DBM, files, LDAP, NIS+, password, NT, Emerald,
Platypus, Freeside, Interbiller, TACACS+, PAM, external, etc, etc.
Available on Unix, Linux, FreeBSD, Windows 95/98/2000, NT, MacOS X.
===
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.


More information about the radiator mailing list