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).
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.
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
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);
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);
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; /
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;
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
);
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) )
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) )
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) )
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