Is there a way to dynamically update an MS-SQL database from the CDR records in the Asterisk MySQL database?

I would like to capture the Call Descriptor Records from the Asterisk MySQL database for further analysis in MS-SQL.  Is there a gracefull way to periodically (every couple of minutes?) run a remote query from the MS SQL server to extract them from the Asterisk/Centos box? Asterisk 1.4.29.1, CentOS, MySQL 5.0.77.   Thanks!
hmaupinAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
afsarsalConnect With a Mentor Commented:
Hi,

You may listen with AMI and save to SQL Server, Oracle, txt files, etc...
It'll be online...

If you are a developer you can use AMI Event_CdrEventHandler for this purpose.

The following is very simple C# code that I've used which writes CDR records to .txt file.

        void ami_Event_CdrEvent(object AccountCode, object Source, object Destination, object DestinationContext, object CallerID, object Channel, object DestinationChannel, object LastApplication, object LastData, object StartTime, object AnswerTime, object EndTime, object Duration, object BillableSeconds, object Disposition, object AMAFlags, object UserField)
        {
            WriteCdrLog(AccountCode + ";" + Source + ";" + Destination + ";" + DestinationContext
                 + ";" + CallerID + ";" + Channel + ";" + DestinationChannel + ";" + LastApplication
                 + ";" + LastData + ";" + StartTime + ";" + AnswerTime + ";" + EndTime + ";" + Duration + ";" + BillableSeconds
                 + ";" + Disposition + ";" + AMAFlags + ";" + UserField);
        }

        public void WriteCdrLog(string log)
        {
            string logFileName = Application.StartupPath.ToString() + "\\" + DateTime.Now.Year.ToString() + DateTime.Now.Month.ToString() + DateTime.Now.Day.ToString() + "_cdr.txt";
            StreamWriter sw = new StreamWriter(logFileName, true);
            sw.WriteLine(log);
            sw.Flush();
            sw.Close();
            sw.Dispose();
            sw = null;
        }
0
 
nasirbestConnect With a Mentor Commented:
I think Yes! even I have not tried it yet

Asterisk -> CDR -> odbc -> MSSQL

for more detail please have a look at following urls

http://www.voip-info.org/wiki/view/Asterisk+cdr+odbc
http://www.unixodbc.org/doc/FreeTDS.html
0
 
hmaupinAuthor Commented:
Thanks, this was very helpful.  yum info unixodbc returns "Install unixODBC if you want to access databases through ODBC.  This package includes low-level drivers for MySQL, PostgreSQL, and local files. However, the included drivers are not as up-to-date as the ones distributed separately.  It is recommended that you install and use the MyODBC package if you need a driver for MySQL, and/or the postgresql-odbc package for  PostgreSQL.

How difficult is it to install and set up MyODBC (this is a live installation)?  Thanks!
0
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

 
nasirbestCommented:
FreeTDS is not much mature,   You have to do some "trail and error" things. So I do not advice you to do it on live server.
0
 
hmaupinAuthor Commented:
Thanks, will build up a test system and give it a try.
0
 
hmaupinAuthor Commented:
Sorry, have not had time to pursue this due to more pressing issues, thanks!
0
 
hmaupinAuthor Commented:
Still haven't gotten the Q down to where I can test this, regrets!
0
 
hmaupinAuthor Commented:
Still fighting other issues, regrets!
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
0
All Courses

From novice to tech pro — start learning today.