How to setup Message reporter

With the help of the Message reporter you can store your messages and the data belonging to them in a database. The capacity of the message history is limited in order to keep the system stable that is why if you wish to store all of your incoming and outgoing messages you can do this using the Message reporter. You can store all relevant information related to the messages in any kind of database like the creation time, the sender of the message, the recipient, the content of the message, the status of the message and so on. With the help of the message reporter you can easily track the sent and received messages. In this guide you will see how to install and configure Message reporter SQL API in Ozeki Phone System XE.

Step 1: How to install Message reporter SQL API
Step 2: How to configure Message reporter SQL API
Step 3: Table structure
Step 4: Create table scripts

Step 1: How to install Message reporter SQL API

First of all, open Ozeki Phone System XE, in a web browser, and login with your username and password (Figure 1).

Figure 1 - Login page

On the main page of Ozeki Phone System XE, select Productivity from the upper menu bar and then select SQL API (Figure 2).

Figure 2 - Main page

On the following page, click on Install button (Figure 3).

Figure 3 - SQL API page

On the next page, click on the Install button right next to the Message reporter SQL API (Figure 4).

Figure 4 - Install Message reporter

Step 2: How to configure Message reporter SQL API

In this step you will see how to configure Message reporter SQL API.

Under the Database Connection tab you should to setup the following options.

First you need to enter the Connection name which will be the name of the extension by default. Next choose the Data source. If you select the MSSQL or the MySQL you don't need to install any other plugin or connector to your system, the Ozeki Phone System XE can handle these databases easily. But if you choose Odbc or OleDb you need to install connector for them to access database management systems.

At the Configuration mode section you can choose between Simple or Advanced configuration mode. The Simple configuration mode can only be used when you connect Ozeki Phone System XE to MSSQL or MySQL data sources. (If you selected Odbc or OleDb data sources you can reach only the Advanced configuration mode.) The Simple mode is intended to make the configuration easier for these connection types. If you select this option you need to add four informations:

• the Server is the IP address or URL of the database server.
• the Database name is the name of the databse to use by the system.
• the Username is for access the selected database.
• the Password.

Figure 5 - Database Connection Simple options

But if you would like to add more informations and parameters to connect to the database, choose the Advanced configuration mode and you can change the Connection string which is a description of a database connection regarding the selected data source type. You can find different connection strings related to a lot of database servers here.

Figure 6 - Database Connection Advanced options

On the configuration form of the Message reporter API, under the SQL templates tab, you need to provide the SQL queries that will store the main changes of the messages. The followings will be inserted into the database:

• id: The unique id of the message.
• creationtime: The time when the message was created.
• destination: The outside line or extension to where the message is routed.
• message: The first 400 characters of the message content.
• messagetype: The message type (e.g. Email, SMS).
• recipient: The receiver of the message.
• sender: The sender of the message.
• source: The outside line or extension from which the message is coming.
• status: The state in which the message is at the given moment (e.g. Sent).
• subject: The subject of the Email regarding Email messages.

Figure 6 - SQL templates of message reporting

Step 3: Table structure

After the Message Reporter SQL API has been configured in the Ozeki Phone System XE, the only thing left to do is to create the database table for Message Reporter SQL API. We recommend you to use the following table layout:

• ozpbxmessages table

Step 4: Create table scripts

Microsoft SQL Server

CREATE TABLE `ozpbxmessages` (
  id int identity(1,1) PRIMARY KEY,
  creationtime datetime DEFAULT NULL,
  source varchar(150) DEFAULT NULL,
  sender varchar(150) DEFAULT NULL,
  recipient varchar(150) DEFAULT NULL,
  messagetype varchar(150) DEFAULT NULL,
  message varchar(400) DEFAULT NULL,
  destination varchar(150) DEFAULT NULL,
  status varchar(150) DEFAULT NULL,
  subject varchar(150) DEFAULT NULL
) 
CREATE INDEX ozpbxmessagesindex ON ozpbxmessages (id);

Microsoft SQL Express

CREATE TABLE `ozpbxmessages` (
  id int identity(1,1) PRIMARY KEY,
  creationtime datetime DEFAULT NULL,
  source varchar(150) DEFAULT NULL,
  sender varchar(150) DEFAULT NULL,
  recipient varchar(150) DEFAULT NULL,
  messagetype varchar(150) DEFAULT NULL,
  message varchar(400) DEFAULT NULL,
  destination varchar(150) DEFAULT NULL,
  status varchar(150) DEFAULT NULL,
  subject varchar(150) DEFAULT NULL
) 
CREATE INDEX ozpbxmessagesindex ON ozpbxmessages (id);

Oracle

CREATE TABLE ozpbxmessages (
  id integer PRIMARY KEY,
  creationtime timestamp DEFAULT NULL,
  source varchar(150) DEFAULT NULL,
  sender varchar(150) DEFAULT NULL,
  recipient varchar(150) DEFAULT NULL,
  messagetype varchar(150) DEFAULT NULL,
  message varchar(400) DEFAULT NULL,
  destination varchar(150) DEFAULT NULL,
  status varchar(150) DEFAULT NULL,
  subject varchar(150) DEFAULT NULL
)

CREATE SEQUENCE id_seq START WITH 1 INCREMENT BY 1;

CREATE OR REPLACE ozpbxmessages_insert
BEFORE INSERT ON ozpbxmessages
FOR EACH ROW
BEGIN
    SELECT id_seq.nextval INTO :new.id FROM dual;
END;
/

MySQL

CREATE TABLE `ozpbxmessages` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `creationtime` datetime DEFAULT NULL,
  `source` varchar(150) DEFAULT NULL,
  `sender` varchar(150) DEFAULT NULL,
  `recipient` varchar(150) DEFAULT NULL,
  `messagetype` varchar(150) DEFAULT NULL,
  `message` varchar(400) DEFAULT NULL,
  `destination` varchar(150) DEFAULT NULL,
  `status` varchar(150) DEFAULT NULL,
  `subject` varchar(150) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=54 DEFAULT CHARSET=UTF8;

PostgreSQL

CREATE TABLE ozpbxmessages (
  id integer PRIMARY KEY DEFAULT nextval('serial'),
  creationtime date DEFAULT NULL,
  source varchar(150) DEFAULT NULL,
  sender varchar(150) DEFAULT NULL,
  recipient varchar(150) DEFAULT NULL,
  messagetype varchar(150) DEFAULT NULL,
  message varchar(400) DEFAULT NULL,
  destination varchar(150) DEFAULT NULL,
  status varchar(150) DEFAULT NULL,
  subject varchar(150) DEFAULT NULL
);

Sybase (SQL Anywhere)

CREATE TABLE ozpbxmessages (
  id int,
  creationtime timestamp DEFAULT NULL,
  source char(150) DEFAULT NULL,
  sender char(150) DEFAULT NULL,
  recipient char(150) DEFAULT NULL,
  messagetype char(150) DEFAULT NULL,
  message char(400) DEFAULT NULL,
  destination char(150) DEFAULT NULL,
  status char(150) DEFAULT NULL,
  subject char(150) DEFAULT NULL
  PRIMARY KEY(id)
) 

DB2

CREATE TABLE ozpbxmessages (
  id int,
  creationtime date,
  source varchar(150),
  sender varchar(150),
  recipient varchar(150),
  messagetype varchar(150),
  message varchar(400),
  destination varchar(150),
  status varchar(150),
  subject varchar(150)
  PRIMARY KEY(id)
)

Informix

CREATE TABLE ozpbxmessages (
  id SERIAL PRIMARY KEY,
  creationtime date,
  source char(150),
  sender char(150),
  recipient char(150),
  messagetype char(150),
  message char(400),
  destination char(150),
  status char(150),
  subject char(150)
) 

FoxPro

CREATE TABLE ozpbxmessages (
  id i AUTOINC NEXTVALUE 1 STEP 1 PRIMARY KEY,
  creationtime t,
  source c(150),
  sender c(150),
  recipient c(150),
  messagetype c(150),
  message c(400),
  destination c(150),
  status c(150),
  subject c(150)
) 

If you have any questions or need assistance, please contact us at info@ozekiphone.com.

Dig deeper!
People who read this also read...

• How to setup call routing for Messages
• How to send and receive SMS messages using your database server
• How to connect your VoIP phone to Ozeki PBX using your database server