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.

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

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

how to install message reporter in ozeki phone system
Figure 1 - 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.

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.

sql templates of message reporting in ozeki phone system
Figure 2 - SQL templates of message reporting

Step 3: Table structure

After the Message Reporter SQL API has been configured in the Ozeki Phone System, 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
Name Type
id varchar(150) (primary key)
creationtime datetime
source varchar(150)
sender varchar(150)
recipient varchar(150)
messagetype varchar(150)
message varchar(400)
destination varchar(150)
status varchar(150)
subject varchar(150)

Step 4: Create table scripts

Microsoft SQL Server
Message reporter 
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
Message reporter 
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
Message reporter 
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
Message reporter 
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
Message reporter 
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)
Message reporter 
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
Message reporter 
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
Message reporter 
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
Message reporter 
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.

More information