Dalo Radius support - Exporting Data Usage into Excel file

Skills Required: MySql, Free Radius, PHP/Perl We are using a radius server called Freeradius which authenticates the users and receives accounting updates from the NBN access concentrator. The accounting data is being updated correctly and can be accessed without issue from the MySQL database backend. The difficulty I am facing is that the accounting data is incremental, and increments every 15 minutes, so when the updates are sent to the radius server, and subsequently SQL, the usage counters continue to increase until the user logs off and then a new session is started, whereby the counters reset to zero. What we need to obtain is actually the difference between the last entry for usage and the current one, then log that to a different table in the database so that we can report on it more accurately. I have been working with trigger scripts in SQL to pick up when a record is being updated and then run a script to update the information into different fields in that table, the problem is that the trigger function doesn’t seem to be able to differentiate between the individual records that are being updated, or the data which is coming through doesn’t identify it properly, so it does multiple updates to the table at once, and I am seeing duplicated data being written. There are other options which use MySQL procedures, but they seem to complicate matters more than simply intercepting the update and writing it somewhere else as well. This really all comes about due to a requirement from the our system where it needs to get an update every hour with usage stats. The issue with this is that if the user remains logged in for multiple days, which is likely and normal, then the usage stats won’t get updated using the method we have already until they have logged out, or we kick their session off. This is due to the way we look for the accounting data now, we currently look for a start time and date, and an end time and date, and gather the accounting data from the date range. If we can determine how much data they are using from each accounting update then we can accurately report on the usage. So to summarise: The access concentrator sends accounting (usage) data to radius, it then logs that to MySQL Database We need to intercept that accounting update and determine the difference between the current usage value in SQL, assuming it’s lower, and write the difference between the current value and the new value to a different table along with the username, this has to be recorded for upload (inoctets) and download (outoctets). Once we can get that working it will be a simple matter of updating our PHP script to read that data and export it to a CSV file to be FTP’d to the people hosting the billing system, they will then import the CSV file into their system. I have already written the scripts to do this and tested them, but I need more accurate data to implement them properly, so hopefully you can help determine how we can intercept that update and grab the updated figures each time.