How to setup Database Logger

Using Database Logger you can log PBX events into your database to archive all PBX events for a long period and to make it easy to work with the information provided by the PBX. In this guide you will see how to install, then how to configure Database Logger SQL API in Ozeki Phone System.

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

Step 1: How to install Database Logger SQL API

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

how to install database logger extension in ozeki phone system
Figure 1 - Install Database logger

Step 2: How to configure Database Logger SQL API

In this step you will see how to configure Database logger SQL API.

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

  • Time: The time when the log entry was created.
  • Level: The level of the log (INFO, DEBUG, ERROR or WARNING).
  • Code: The id of the log event.
  • Logger: The module of the PBX in which the event occured.
  • Message: The message of the event.

sql templates of database logger
Figure 2 - SQL templates of Database logger

Step 3: Table structure

After the Database Logger SQL API has been configured in the Ozeki Phone System XE, you need to create the database tables for Database Logging SQL API. Use the following table layout:

  • ozpbxlog table
Name Type
id int (primary key, auto increment)
Time datetime
Thread int
Level varchar(20)
Code varchar(20)
Logger varchar(255)
Message text

Step 4: Create table scripts

Microsoft SQL Server
Database Logger 
CREATE  TABLE ozpbxlog
(
id int identity(1,1) PRIMARY KEY,
Time datetime NOT NULL,
Thread int NOT NULL,
Level varchar(20) NOT NULL,
Code varchar(20) NOT NULL,
Logger varchar(255) NOT NULL,
Message text NOT NULL,
)
Microsoft SQL Express
Database Logger 
CREATE  TABLE ozpbxlog
(
id int identity(1,1) PRIMARY KEY,
Time datetime NOT NULL,
Thread int NOT NULL,
Level varchar(20) NOT NULL,
Code varchar(20) NOT NULL,
Logger varchar(255) NOT NULL,
Message text NOT NULL,
)
Oracle
Database Logger 
CREATE TABLE ozpbxlog
(
id integer PRIMARY KEY,
Time timestamp NOT NULL,
Thread integer NOT NULL,
Level varchar(20) NOT NULL,
Code varchar(20) NOT NULL,
Logger varchar(255) NOT NULL,
Message nclob NOT NULL,
)

CREATE SEQUENCE id_seq START WITH 1 INCREMENT BY 1;

CREATE OR REPLACE ozpbxlog_insert
BEFORE INSERT ON ozpbxlog
FOR EACH ROW
BEGIN
SELECT id_seq.nextval INTO :new.id FROM dual;
END;
/
MySQL
Database Logger 
CREATE  TABLE IF NOT EXISTS `ozpbxlog` (
`id` int(11) AUTO_INCREMENT,
`Time` datetime NOT NULL,
`Thread` int(11) NOT NULL,
`Level` varchar(20) NOT NULL,
`Code` varchar(20) NOT NULL,
`Logger` varchar(255) NOT NULL,
`Message` text NOT NULL,
PRIMARY KEY (`id`))
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8;
PostgreSQL
Database Logger 
CREATE TABLE ozpbxlog
(
id serial PRIMARY KEY, 
Time date NOT NULL,
Thread integer NOT NULL,
Level varchar(20) NOT NULL,
Code varchar(20) NOT NULL,
Logger varchar(255) NOT NULL,
Message text NOT NULL
)
Sybase (SQL Anywhere)
Database Logger 
CREATE TABLE ozpbxlog (
id integer NOT NULL DEFAULT autoincrement,
Time datetime NOT NULL,
Thread integer NOT NULL,
Level varchar(20) NOT NULL,
Code varchar(20) NOT NULL,
Logger varchar(255) NOT NULL,
Message text NOT NULL,
PRIMARY KEY ("id")
)

go 
commit work 
go
DB2
Database Logger 
CREATE TABLE ozpbxlog (
(id integer NOT NULL GENERATED ALWAYS AS 
   IDENTITY (START WITH 1 INCREMENT BY 1),
Time          TIMESTAMP    NOT NULL,
Thread        INTEGER      NOT NULL,
Level      VARCHAR(20)  NOT NULL,
Code      VARCHAR(20)  NOT NULL,
Logger        VARCHAR(255) NOT NULL,
Message       CLOB         NOT NULL,
PRIMARY KEY (id))
Informix
Database Logger 
CREATE TABLE ozpbxlog
(
id serial PRIMARY KEY, 
Time datetime NOT NULL,
Thread int NOT NULL,
Level varchar(20) NOT NULL,
Code varchar(20) NOT NULL,
Logger varchar(255) NOT NULL,
Message text NOT NULL
)
FoxPro
Database Logger 
CREATE TABLE ozpbxlog
(id I AUTOINC NEXTVALUE 1 STEP 1 PRIMARY KEY, 
Time T NOT NULL,
Thread I NOT NULL,
Level C(20) NOT NULL,
Code C(20) NOT NULL,
Logger C(255) NOT NULL,
Message N NOT NULL)

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

More information