<html><head></head><body style="word-wrap: break-word; -webkit-nbsp-mode: space; -webkit-line-break: after-white-space; ">The accounting section of rlm_sql has been modified to use reference based accounting queries.<div><br></div><div>Accounting and post-auth now have their own subsections, and contain a config pair called reference. The value of this is expanded to give a config path, and the config pair this resolves to is used as the query value.</div><div><br></div><div>If the query fails and the config path matches multiple pairs the next pair is used. This emulates the 'alt' behaviour that was were before but can fail between N queries. </div><div><br></div><div>I've tested the MySQL version pretty thoroughly, but it'd be nice if people using Postgresql, Mssql, and Oracle could also test, just to make sure no query errors have crept in with the reformatting.</div><div><br></div><div>Code is available here: <a href="https://github.com/arr2036/freeradius-server/tree/dynamic_acct_query_selection">https://github.com/arr2036/freeradius-server/tree/dynamic_acct_query_selection</a></div><div><br></div><div>There's also a patch in that branch from Alan to strip off whitespace from the start ofline continuations if the config pair value begins with "<a href="smb://n">\\n</a> - slightly magic but seems to be pretty effective - debug output is much more readable.</div><div><br></div><div>The MySQL schemas have also been updated with new features. If anyone fancies adding the following to the default schemas/configs it'd be much appreciated:</div><div><span class="Apple-tab-span" style="white-space:pre"> </span>* Calculating all times from %{integer:Event-Timestamp} (you'll have to convert from unix timestamp using an SQL function) and removing the acctstartdelay and acctstopdelay columns.</div><div><span class="Apple-tab-span" style="white-space:pre"> </span>* Adding an acctupdatetime column, and updating it when a new session is created or an interim-update packet is received.</div><div><span class="Apple-tab-span" style="white-space:pre"> </span>* Adding an acctinterval column and updating it using the delta (in seconds) between the last acctupdatetime value and the current time (this is really easy way to do stale session detection if your NAS supports interim-updates). This is difficult in some SQL dialects, because there's no guarantee that fields will be modified in the order specified in the query.</div><div><span class="Apple-tab-span" style="white-space:pre"> </span>* Removing the xascend column, and using a single list of fields for all insert operations.</div><div><br></div><div>Look at the MySQL schema if the above are unclear, and please post to the devel list if you want to work on one of the schemas/databases, so we don't have duplicate effort.</div><div><br></div><div>-Arran</div><div><br></div><div><div>Note: Redundant queries are now supported in post-auth too, so it should be possible to maintain a table of just the last authentication attempt for a user if thats all the post-auth data you're interested in.</div><div><br></div><div>Note: As part of the rewrite i've removed the Cisco session length hack. If anyone was still using this please contact me, and we'll work something out.</div></div></body></html>