Arthur B Olsen wrote:
The Acct-Interim-Interval does not update the acctinputoctets and acctoutputoctets in postgresql as it does in files.
I dont know if this is the desired behavior, or if this is a mistake.
But to correct/change this, you can change your postgresql.conf like this:
# accounting_update_query = "UPDATE ${acct_table1} \
# SET FramedIPAddress = NULLIF('%{Framed-IP-Address}', '')::inet \
# WHERE AcctSessionId = '%{Acct-Session-Id}' AND UserName = '%{SQL-User-Name}' \
# AND NASIPAddress= '%{NAS-IP-Address}' AND AcctStopTime IS NULL"
accounting_update_query = "UPDATE ${acct_table1} \
SET FramedIPAddress = NULLIF('%{Framed-IP-Address}', '')::inet, \
AcctInputOctets = (('%{Acct-Input-Gigawords:-0}'::bigint << 32) + '%{Acct-Input-Octets:-0}'::bigint), \
AcctOutputOctets = (('%{Acct-Output-Gigawords:-0}'::bigint << 32) + '%{Acct-Output-Octets:-0}'::bigint) \
WHERE AcctSessionId = '%{Acct-Session-Id}' AND UserName = '%{SQL-User-Name}' \
AND NASIPAddress= '%{NAS-IP-Address}' AND AcctStopTime IS NULL"
And your accounting will be updated with the interval set with Acct-Interim-Interval.
Commited. thanks
After thinking about this patch a little I have added an extra line in addition to your suggested ones. It updates AcctSessionTime also. The final query is:
accounting_update_query = "UPDATE ${acct_table1} \
SET FramedIPAddress = NULLIF('%{Framed-IP-Address}', '')::inet, \
AcctSessionTime = (EXTRACT(EPOCH FROM(now()::timestamp with time zone - AcctStartTime::timestamp with time zone - '%{Acct-Delay-Time:-0}'::inter
val)))::BIGINT, \
AcctInputOctets = (('%{Acct-Input-Gigawords:-0}'::bigint << 32) + '%{Acct-Input-Octets:-0}'::bigint), \
AcctOutputOctets = (('%{Acct-Output-Gigawords:-0}'::bigint << 32
) + '%{Acct-Output-Octets:-0}'::bigint) \
WHERE AcctSessionId = '%{Acct-Session-Id}' AND UserName = '%{SQL
-User-Name}' \
AND NASIPAddress= '%{NAS-IP-Address}' AND AcctStopTime IS NULL"
If there is any MySQL users around, they might like to take a look at some of the queries we are using in postgresql.conf and see if they can be ported to MySQL. There is several sanity checks and also automatic adjustment of Start and Stop times to take out accounting skew that only happens if you use postgresql currently...
Any takers??
Peter
- List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
