I am going to make this change for 1.1.1 also if no-one objects... -Peter On Tue 07 Mar 2006 00:37, Peter Nixon wrote:
Does anyone disagree that AcctStopDelay should always equal 0 in these queries?
accounting_onoff_query = "UPDATE ${acct_table1} \ SET AcctStopTime = (now() - '%{Acct-Delay-Time:-0}'::interval), \ AcctSessionTime = (EXTRACT(EPOCH FROM(now()::timestamp with time zone - AcctStartTime::timestamp with time zone - '%{Acct-Delay-Time:-0}'::interval)))::BIGINT, \ AcctTerminateCause='%{Acct-Terminate-Cause}', AcctStopDelay = '%{Acct-Delay-Time:-0}' \ WHERE AcctSessionTime IS NULL AND AcctStopTime IS NULL AND NASIPAddress= '%{NAS-IP-Address}' AND AcctStartTime <= now()"
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 }'::interval)))::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"
Note: I am the one who added originally modified the Postgres query to make AcctSessionTime not include Acct-Delay-Time in the first place to save post calculation in reports. (The default queries for other DBs do not do this currently)
It also occurs to me that now() should probably be %S as now() will skew if the database is lagged or queries are being relayed somewhere else..
I guess the question is what is it that we want to default DB queries to do. Should they be faithfully logging the received radius accounting as closely as possible or should they be trying to give as correct a CDR as possible (Which is probably what the end user wants)
In my opinion doing a little extra arithmetic upon reception of a packet is a fair tradeoff if it lets the user do a simple "select * from radacct" or something to that effect when they need to do reporting (Instead of trying to calculate out delay etc each time they do the report.)
-- Peter Nixon http://www.peternixon.net/ PGP Key: http://www.peternixon.net/public.asc