SCRIPT to convert the radacct details log files into mysql or csv file.

[Goke Aruna]

hi all,

I have an sql.conf as below and for almost three weeks now, its not
logging my calls as successful calls again except some few ones but the
detailed log files shows that they are successful calls.

am using freeradius-1.1.4, mysql-5.0.18 on fedora core 5.
my sql.conf is as below.

1. I will be glad if someone can throw light on why is the disparity in
the two detail files and mysql entries.
2. how to manually restore all the lost db entry from the detailed log
files.

thanks


*extract of sql.conf*

  accounting_onoff_query = "UPDATE ${acct_table1} SET AcctStopTime='%S',
AcctSessionTime=unix_timestamp('%S') - unix_timestamp(AcctStartTime),
AcctTerminateCause='%{Quintum-h323-disconnect-cause}', TrunkIdIn =
'%{Quintum-Trunkid-In}', remoteip = '%{Quintum-h323-remote-address}',
TrunkIdOut = '%{Quintum-Trunkid-Out}', AcctStopDelay =
'%{Acct-Delay-Time}' WHERE AcctSessionTime=0 AND AcctStopTime=0 AND
NASIPAddress= '%{NAS-IP-Address}' AND AcctStartTime <= '%S'"

        accounting_start_query = "INSERT into ${acct_table1}
(AcctSessionId, AcctUniqueId, UserName, Realm, NASIPAddress, remoteip,
NASPortType, AcctStartTime, AcctStopTime, AcctSessionTime,
AcctAuthentic, ConnectInfo_start, ConnectInfo_stop, AcctInputOctets,
AcctOutputOctets, CalledStationId, CallingStationId, AcctTerminateCause,
ServiceType, TrunkIdIn, TrunkIdOut, AcctStartDelay, AcctStopDelay)
values('%{Acct-Session-Id}', '%{Acct-Unique-Session-Id}',
'%{SQL-User-Name}', '%{Realm}', '%{NAS-IP-Address}',
'%{Quintum-h323-remote-address}', '%{NAS-Port-Type}', '%S', '0', '0',
'%{Acct-Authentic}', '%{Quintum-h323-connect-time}', '', '0', '0',
'%{Called-Station-Id}', '%{Calling-Station-Id}', '', '%{Service-Type}',
'%{Quintum-Trunkid-In}', '%{Quintum-Trunkid-Out}', '%{Acct-Delay-Time}',
'0')"

        accounting_start_query_alt  = "UPDATE ${acct_table1} SET
AcctStartTime = '%{AcctStartTime}', AcctStartDelay =
'%{Acct-Delay-Time}', ConnectInfo_start =
'%{Quintum-h323-connect-time}', remoteip =
'%{Quintum-h323-remote-address}', TrunkIdOut = '%{Quintum-Trunkid-Out}',
TrunkIdIn = '%{Quintum-Trunkid-In}'  WHERE AcctSessionId =
'%{Acct-Session-Id}' AND UserName = '%{SQL-User-Name}' AND NASIPAddress
= '%{NAS-IP-Address}'"

        accounting_stop_query = "UPDATE ${acct_table2} SET AcctStopTime
= '%S', AcctSessionTime = '%{Acct-Session-Time}', AcctInputOctets =
'%{Acct-Input-Octets}', remoteip = '%{Quintum-h323-remote-address}',
AcctOutputOctets = '%{Acct-Output-Octets}', AcctTerminateCause =
'%{Quintum-h323-disconnect-cause}', AcctStopDelay =
'%{Acct-Delay-Time}', ConnectInfo_stop =
'%{Quintum-h323-disconnect-time}', TrunkIdIn = '%{Quintum-Trunkid-In}',
TrunkIdOut = '%{Quintum-Trunkid-Out}'  WHERE AcctSessionId =
'%{Acct-Session-Id}' AND UserName = '%{SQL-User-Name}' AND NASIPAddress
= '%{NAS-IP-Address}'"

        accounting_stop_query_alt = "INSERT into ${acct_table2}
(AcctSessionId, AcctUniqueId, UserName, Realm, NASIPAddress, remoteip,
NASPortType, AcctStartTime, AcctStopTime, AcctSessionTime,
AcctAuthentic, ConnectInfo_start, ConnectInfo_stop, AcctInputOctets,
AcctOutputOctets, CalledStationId, CallingStationId, AcctTerminateCause,
ServiceType, TrunkIdIn, TrunkIdOut, AcctStartDelay, AcctStopDelay)
values('%{Acct-Session-Id}', '%{Acct-Unique-Session-Id}',
'%{SQL-User-Name}', '%{Realm}', '%{NAS-IP-Address}',
'%{Quintum-h323-remote-address}', '%{NAS-Port-Type}', DATE_SUB('%S',
INTERVAL (%{Acct-Session-Time:-0} + %{Acct-Delay-Time:-0}) SECOND),
'%S', '%{Acct-Session-Time}', '%{Acct-Authentic}', '',
'%{Quintum-h323-disconnect-time}', '%{Acct-Input-Octets}',
'%{Acct-Output-Octets}', '%{Called-Station-Id}',
'%{Calling-Station-Id}', '%{Quintum-h323-disconnect-cause}',
'%{Service-Type}', '%{Quintum-Trunkid-In}', '%{Quintum-Trunkid-Out}',
'0', '%{Acct-Delay-Time}')"