(RADIATOR) Accounting Start-Stops

[Jaime Elizaga Jr.]

Hi Everyone,

We are currently running Radiator 2.18.4 with MySQL.  

I do notice that with ACCOUNTING, there are two records generated for every user session, one is when the user connects and the other is when it the user disconnects. In just a small amount of time, our accounting database has grown to very big, about 200,000 records within two months of operation. Within half a year, we can generate as much as 1,000,000 records. I also notice that both the records pertaining to a user session is almost identical except for the timestamp, status type and session time. This means that we are having redundant records.

Is there a way to have the Accounting to insert a record upon a user's connection and just update that same record upon the user's disconnection?  There will be some modified fields like the Timestamp will be changed to Time_Start and add another column like Time_Stop.

My config file looks something like this:

Foreground
LogStdout
LogDir          /var/log/radius
LogFile         /var/log/radius/%m/%d%Y-trace
FingerProg      /usr/bin/finger
PidFile         /var/run/radius.pid
DictionaryFile  /usr/local/etc/dictionary
Trace           4

<Client xxx.xxx.xxx.xxx>
        Secret yyyxxx
        DupInterval 3
</Client>

<Realm DEFAULT>
        AcctLogFileName  /var/log/radius/default/details
        PasswordLogFileName   /var/log/radius/Test/%m%d%Y-passlog
    <AuthBy SQL>
        # Adjust DBSource, DBUsername, DBAuth to suit your DB
        DBSource      dbi:mysql:XXXDb
        DBUsername  xxxxxxxxxx
        DBAuth          xxxxxxxxx

        AuthSelect      select PASSWORD, TIMELEFT from SUBSCRIBERS where USERNAME='%n'
        AuthColumnDef   0,User-Password,check
        AuthColumnDef   1,Session-Timeout,reply
        DefaultReply    Service-Type=Framed-User, Framed-Protocol=PPP, Framed-Routing=None
        AccountingTable ACCOUNTING
        AcctColumnDef   USERNAME,User-Name
        AcctColumnDef   TIMESTAMP,Timestamp,integer
        AcctColumnDef   STATUS_TYPE,Acct-Status-Type
        AcctColumnDef   SESSION_ID,Acct-Session-Id
        AcctColumnDef   SESSION_TIME,Acct-Session-Time,integer
        AcctColumnDef   TERMINATE_CAUSE,Ascend-Disconnect-Cause,integer
        AcctColumnDef   NAS_IDENTIFIER,NAS-IP-Address
        AcctColumnDef   NAS_PORT,NAS-Port,integer
        AcctColumnDef   IP_ADDRESS,Framed-IP-Address
        AcctColumnDef   CALLER_ID,Calling-Station-Id
        AcctColumnDef   CALLED_STATION,Called-Station-Id
        AcctSQLStatement update SUBSCRIBERS set TIMELEFT=TIMELEFT-0%{Acct-Session-Time}  \
                                     where USERNAME='%n'
    </AuthBy>
</Realm>

I am thinking if it is possible to have two accounting sequences wherein one would take care of accounting start and the other would take care of the accounting stops. 

AccountingTable ACCOUNTING
AccountingStartsOnly
        AcctColumnDef   USERNAME,User-Name
        AcctColumnDef   TIMESTART,Timestamp,integer
        AcctColumnDef   STATUS_TYPE,Acct-Status-Type
        AcctColumnDef   SESSION_ID,Acct-Session-Id
        AcctColumnDef   SESSION_TIME,Acct-Session-Time,integer
        AcctColumnDef   TERMINATE_CAUSE,Ascend-Disconnect-Cause,integer
        AcctColumnDef   NAS_IDENTIFIER,NAS-IP-Address
        AcctColumnDef   NAS_PORT,NAS-Port,integer
        AcctColumnDef   IP_ADDRESS,Framed-IP-Address
        AcctColumnDef   CALLER_ID,Calling-Station-Id
        AcctColumnDef   CALLED_STATION,Called-Station-Id
AccountingStopsOnly
        AcctColumnDef   USERNAME,User-Name
        AcctColumnDef   TIMESTOP,Timestamp,integer
        AcctColumnDef   STATUS_TYPE,Acct-Status-Type
        AcctColumnDef   SESSION_ID,Acct-Session-Id
        AcctColumnDef   SESSION_TIME,Acct-Session-Time,integer
        AcctColumnDef   TERMINATE_CAUSE,Ascend-Disconnect-Cause,integer
        AcctColumnDef   NAS_IDENTIFIER,NAS-IP-Address
        AcctColumnDef   NAS_PORT,NAS-Port,integer
        AcctColumnDef   IP_ADDRESS,Framed-IP-Address
        AcctColumnDef   CALLER_ID,Calling-Station-Id
        AcctColumnDef   CALLED_STATION,Called-Station-Id
        AcctSQLStatement update SUBSCRIBERS set TIMELEFT=TIMELEFT-0%{Acct-Session-Time}  \
                                    where USERNAME='%n'


Is there any other means that is more efficient in dealing about with this type of scenario? Please enlighten me.



Thank you very much,


Jaime Elizaga Jr.
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://www.open.com.au/pipermail/radiator/attachments/20010920/1a9f253f/attachment.html>