We help IT Professionals succeed at work.

Capturing SMDR data into MySQL

hellblazeruk asked

I am trying to put together a basic SMDR logger to record calls made in the office, we are using a mitel 3300, I can view the call data by Telnet on port 1752, how can I record this data to a MySQL db so it can be viewed?
Watch Question

Can the Mitel export data to text?  I would assume it could.

There are applications designed to work with any phone system.  These can extract-transform-load the data into SQL databases.  The ETL process could be developed with a programming language such as Python, Java, or even Bash.  It could then be automated.

The text data from the Mitel 3300 may need to be cleaned and formatted properly.  The MySQL database would need to be designed to have the proper datatypes for the ETL process to run smoothly.  Sometimes data immediately after being exported from a phone system can be anomalous or unusual.  This can cause the automated ETL process to fail unless there is robust exception-handling in the program that does the ETL processing.  I would first figure out how to export the data from the Mitel 3300 to plain text by calling the vendor.  Then I'd develop an in-house ETL solution or find a third party software company that has an application that can do this.

I would figure out how long the Mitel 3300 stores historic data.  You may want to back up the plain text data for historic reasons.  If the data is sensitive, you may not want to store back up copies of it.


the output can be viewed using telnet not saved to a txt file, so I need a way of saving from telnet to the MySQL db?
This thread may be relevant:

Topic: Access SMDR Logs on Mitel 3300

Since you mentioned Perl, the way you would develop a script that performed this transfer would be to use the Expect module to interact with the Mitel over telnet and then send the data to MySQL using module DBD::MySql. I don't think it would be a trivial script to write, however, so it would be wise to look into whatever the Mitel community may already have developed.
I'm curious how this would be automated without getting the data to a text file or CSV file.  I may not be able to help.  If a telnet session can be established, I would think an SFTP (or possibly an FTP) session could be established.  Do you want the text that is echoed to the telnet console to be written somewhere else as it is streamed?  That would be complex in my opinion.  Good luck.