Dalo Radius support - Exporting Data Usage into Excel file

已关闭 已发布的 Jan 7, 2013 货到付款
已关闭 货到付款

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.

MySQL Perl PHP 软件构架

项目ID: #4105749

关于项目

4个方案 远程项目 活跃的Feb 13, 2013

有4名威客正在参与此工作的竞标,均价$344/小时

lokeshk9936

Hi, I have expertise in SQL/MySQL ( Have more than 7 years of experience as a database expert ) and want to do this. Please see PM.

$175 USD 在4天内
(5条评论)
2.9
voflik79

ok testing only

$800 USD 在14天内
(0条评论)
0.0
thiagomaia

Hello I got here customers using pppoe over radius to connect to the internet, when they call the "call center", the guys on the phone need to know if the customer internet is working.. how long... Seems th 更多

$50 USD 在2天内
(0条评论)
1.0