How to setup Call reporter

Follow up your phone conversations by using any kind of database server to store call information as caller ID, dialed number, time and duration of the call, call state and other useful data related to a call. By inserting call recording information into your call history database table, it can be really simple to track incoming and outgoing calls in your PBX. In this guide you will see how to install and configure Call reporter SQL API in Ozeki Phone System.

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

Step 1: How to install Call reporter SQL API

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

how to install call reporter in ozeki phone system
Figure 1 - Install Call reporter

Step 2: How to configure Call reporter SQL API

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

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

  • starttime: The time when the call was started.
  • source: The outside line or extension from which the call is coming.
  • callerid: The phone number of the caller.
  • dialed: The number dialled by the caller.
  • destination: The outside line or extension to where the call is routed.
  • duration: The duration of the call.
  • callstate: The state in which the call is at the given moment (e.g. CalleeHungUp, CallerHungUp, Cancelled, Busy, NotFound).
  • recordurl: The URL from where the call can be downloaded.

sql templates of call reporting
Figure 2 - SQL templates of call reporting

Step 3: Table structure

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

  • ozpbxcalls table
Name Type
id int(11) (primary key)
starttime datetime
source varchar(150)
callerid varchar(150)
dialed varchar(150)
destination varchar(150)
duration int(10)
callstate varchar(150)
recordurl varchar(150)

Step 4: Create table scripts

Microsoft SQL Server
Call reporter 
CREATE TABLE ozpbxcalls (
id int identity(1,1) PRIMARY KEY,
starttime datetime DEFAULT NULL,
source varchar(150) DEFAULT NULL,
callerid varchar(150) DEFAULT NULL,
dialed varchar(150) DEFAULT NULL,
destination varchar(150) DEFAULT NULL,
duration int(10) DEFAULT NULL,
callstate varchar(150) DEFAULT NULL,
recordurl varchar(150) DEFAULT NULL
) 
CREATE INDEX ozpbxcallsindex ON ozpbxcalls (id);
Microsoft SQL Express
Call reporter 
CREATE TABLE ozpbxcalls (
id int identity(1,1) PRIMARY KEY,
starttime datetime DEFAULT NULL,
source varchar(150) DEFAULT NULL,
callerid varchar(150) DEFAULT NULL,
dialed varchar(150) DEFAULT NULL,
destination varchar(150) DEFAULT NULL,
duration int(10) DEFAULT NULL,
callstate varchar(150) DEFAULT NULL,
recordurl varchar(150) DEFAULT NULL
) 
CREATE INDEX ozpbxcallsindex ON ozpbxcalls (id);
Oracle
Call reporter 
CREATE TABLE ozpbxcalls (
id integer PRIMARY KEY,
starttime timestamp DEFAULT NULL,
source varchar(150) DEFAULT NULL,
callerid varchar(150) DEFAULT NULL,
dialed varchar(150) DEFAULT NULL,
destination varchar(150) DEFAULT NULL,
duration integer DEFAULT NULL,
callstate varchar(150) DEFAULT NULL,
recordurl varchar(150) DEFAULT NULL
)

CREATE SEQUENCE id_seq START WITH 1 INCREMENT BY 1;

CREATE OR REPLACE ozpbxcalls_insert
BEFORE INSERT ON ozpbxcalls
FOR EACH ROW
BEGIN
SELECT id_seq.nextval INTO :new.id FROM dual;
END;
/
MySQL
Call reporter 
CREATE TABLE `ozpbxcalls` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`starttime` datetime DEFAULT NULL,
`source` varchar(150) DEFAULT NULL,
`callerid` varchar(150) DEFAULT NULL,
`dialed` varchar(150) DEFAULT NULL,
`destination` varchar(150) DEFAULT NULL,
`duration` int(10) DEFAULT NULL,
`callstate` varchar(150) DEFAULT NULL,
`recordurl` varchar(150) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=54 DEFAULT CHARSET=UTF8;
PostgreSQL
Call reporter 
CREATE TABLE ozpbxcalls (
id integer PRIMARY KEY DEFAULT nextval('serial'),
starttime date DEFAULT NULL,
source varchar(150) DEFAULT NULL,
callerid varchar(150) DEFAULT NULL,
dialed varchar(150) DEFAULT NULL,
destination varchar(150) DEFAULT NULL,
duration integer DEFAULT NULL,
callstate varchar(150) DEFAULT NULL,
recordurl varchar(150) DEFAULT NULL
);
Sybase (SQL Anywhere)
Call reporter 
CREATE TABLE ozpbxcalls (
id int,
starttime timestamp DEFAULT NULL,
source char(150) DEFAULT NULL,
callerid char(150) DEFAULT NULL,
dialed char(150) DEFAULT NULL,
destination char(150) DEFAULT NULL,
duration int DEFAULT NULL,
callstate char(150) DEFAULT NULL,
recordurl char(150) DEFAULT NULL
PRIMARY KEY(id)
)
DB2
Call reporter 
CREATE TABLE ozpbxcalls (
id int,
starttime date,
source varchar(150),
callerid varchar(150),
dialed varchar(150),
destination varchar(150),
duration int,
callstate varchar(150),
recordurl varchar(150)
PRIMARY KEY(id)
)
Informix
Call reporter 
CREATE TABLE ozpbxcalls (
id SERIAL PRIMARY KEY,
starttime date,
source char(150),
callerid char(150),
dialed char(150),
destination char(150),
duration int,
callstate char(150),
recordurl char(150)
)
FoxPro
Call reporter 
CREATE TABLE ozpbxcalls (
id i AUTOINC NEXTVALUE 1 STEP 1 PRIMARY KEY,
starttime t,
source c(150),
callerid c(150),
dialed c(150),
destination c(150),
duration i,
callstate c(150),
recordurl c(150)
)

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

More information