Overview
The prerequisites for this tutorial are that MySQL has already been installed and that a database has been created. The database name in these examples will be mcs.
Create Table
The CREATE TABLE statement below can be used to create a table named myvoip. The table includes some generic test metrics, such as ISP, IP and Session ID (SID), along with the results of the test.
CREATE TABLE myvoip (recordid int, testid int, jitter float, djitter float, loss float, dloss float, uorder float, dorder float, discards float, mos float, reg int, invite int, bye int, PRIMARY KEY (recordid));The image below is an example of copying and pasting the above statement into a MySQL command prompt. Once the table has been created, the describe tablename; command can be used to view the newly created table structure.
Enable MySQL
The next step is to enable MySQL in MyConnection Server (MCS). This step may have already been done. If not, click here.
Create Profile
With MySQL enabled and the table created, configure MCS to export data. The first step is to create a SQL profile.
From the MCS main menu, click Manage SQL Profiles in the administration panel.
On the resulting page, click Create a new SQL profile.
The new profile form is split into three sections: Profile, Connection, and Options. Complete them as described below.
| Setting | Description |
|---|---|
| Profile name | A name for this profile. It appears in the SQL profiles list so you can identify it later. |
| Server type | The database server type. Select MySQL for this tutorial. MCS also supports Microsoft SQL Server and PostgreSQL. |
| Server / Port | The host name or IP address of the MySQL server, and the port it listens on. The MySQL default is 3306. |
| User / Password | The credentials MCS uses to log in to the MySQL server. The account needs permission to insert into the target database. |
| Database name | The database that contains the quality table you created earlier (mcs in this tutorial). |
| Auto run | Tick Run this profile automatically on every future test posted to MCS so that each Quality test result is exported as soon as it is recorded. Without this, the profile only runs when triggered manually. |
| SSL | Tick Connect using SSL to encrypt the connection between MCS and the database server. The database server must be configured to accept SSL/TLS connections; leave it unticked for a standard, unencrypted connection. |
Save the profile, which will return to the profiles list. Immediately modify the profile, as the next step is defining the export data.
Define Export Data
The SQL Statements to run text box is where the export data is specified, in the form of an INSERT INTO statement.
The INSERT INTO statement below matches the CREATE TABLE specified in the first step. Copy and paste it into the text box.
[voip] INSERT INTO myvoip (recordid, testid, jitter, djitter, loss, dloss, uorder, dorder, discards, mos, reg, invite, bye) VALUES (%RECORDID%, %TESTID%, %VOIP.JITTER%, %VOIP.DJITTER%, %VOIP.LOSS%, %VOIP.DLOSS%, %VOIP.ORDER%, %VOIP.DORDER%, %VOIP.DISCARDS%, %VOIP.MOS%, %VOIP.REGISTER%, %VOIP.INVITE%, %VOIP.BYE%);Once pasted, it should look like this:
Save the profile to complete the process.
Now, whenever a VoIP test is run, this MySQL export should run and put data in the database.
When using a MySQL command prompt, the data for a table can be viewed by using the command select * from tablename.
If data does not appear in the database, check the SQL logs. These can be found in the /logs/ directory of the root install of MCS.