3. Open Database Connectivity (ODBC)¶
3.1. Overview¶
This document describes how to install the PostgreSQL ODBC driver, connect to the PostgreSQL database residing in a NexLog recording system, and generate a customized report of the data contained therein.
You’ll learn how to create a user with permissions to the Eventide database schema, select individual tables for inclusion in the report, and how to design a report using Microsoft Office Excel and Access.
Note: This exercise requires that you have Microsoft Office 2010 or higher and you are running Microsoft Windows 7 (32-bit or 64-bit). Your recorder must be configured correctly in the network. This exercise requires administrator access to create or change user rights. It also involves working with incidents and data from an Eventide NexLog recorder; make sure that you have at least one incident for testing. Use Eventide MediaWorks Plus when working with incidents.
At the end of this document there is a list of all accessible PostgreSQL tables and views with descriptions of their fields.
3.2. Installing the ODBC Driver¶
ODBC is an acronym for Open Database Connectivity, a standard, widely adopted method for accessing databases. The goal of ODBC is to make it possible to access any data from any application, regardless of which database management system (DBMS) is handling the data. ODBC manages this by inserting a middle layer, called a database driver, between an application and the DBMS. The purpose of this layer is to translate the application’s data queries into commands that the DBMS can accurately interpret.
The Eventide NexLog series of call logging recorders employs PostgreSQL, an open system, Linux-based DBMS. In order for you to access the database from Microsoft Windows, you must first install the PostgreSQL ODBC driver.
To do this, simply download, and install the version that works with your operating system from PostgreSQL. The files can be located from the source at:
http://www.postgresql.org/ftp/odbc/versions/msi/
Make sure that you install the 32- or 64-bit version based on which version of Windows 7 you are using. After the installation is complete you will need to create the connection to the recorder. First though, you must create a user that has the necessary rights to connect.
3.3. Adding a User with Database Access Permissions¶
You must have administrator rights to create users and change permissions in the recorder. The following steps can be done using the front panel of the recorder or using the Configuration Manager portal of the recorder.
Log on to the recorder with administrator rights.
Under Setup locate Users and Security.
Click on Users.
Note
By default, the Eventide user has full administrative rights, and although you may use this account to establish the connection it is recommended to use a restricted account.
Click on Add User provide a name, and a password. Note: usernames and passwords are case sensitive.
The user must be part of the Researchers permission group.
User information can be left as defaults, and the account must be enabled. If there are password expiration policies, set this password to never expire or make a note of the frequency for changing the passwords in the ODBC configuration page.
At this time you can exit out of the Setup menu.
Fig. 3.1 User Security Page on the Front Panel¶
3.4. Establishing an ODBC Database Connection¶
After the installation of the PostgreSQL ODBC driver, you must create a connection within Windows.
In this document we will use Windows 7 64-bit, but the configurations are identical for the 32-bit version.
Open Control Panel, locate the Administrative Tools
Open Data Sources (OBDC); you may also use the RUN command: odbcad32.exe
In the ODBC Data Source Administrator windows select Add.
There are two versions ANSI, or Unicode. For this test we will use Unicode.
Select PostgreSQL Unicode(x64) from the list, then Finish.
Fig. 3.2 Add PostgreSQL Connector¶
Fig. 3.3 ODBC Window¶
Select PostgreSQL Unicode, then configure using the following settings: (see Fig. 3.4)
Datasource: meaningful name for this connection
Database: lj (case sensitive)
SSL Mode: Disable
Server: IP address of the recorder
Port: 5432
User Name: From Adding Database Access Permissions
Password: From Adding Database Access Permissions
Fig. 3.4 ODBC Configuration¶
After completing the steps above test the connection. The connection test should show successful. Then press OK, save the connection, and close all other windows.
3.5. Setting up a Connection using Microsoft Excel¶
The following procedures may help to connect other software products that support OBDC data querying.
Launch Microsoft Excel, this document uses version 2010. Open a new book, from the ribbon menu select Data, and from the ribbon select from Other Sources.
Other Sources will provide a drop down you have two options. Data Connection Wizard, or Microsoft Query. This example will take a look at the Microsoft Query Wizard.
You may add tables to the report by means of selecting them from the list of available tables. In the example below we took the following tables:
‘v_incident’, ‘callincidentgrouping’, ‘v_call’, ‘v_record’
The unique identifier for the tables ‘callincidentgrouping’, ‘v_call’, ‘v_record’ is the ‘callguid’.
Fig. 3.5 Excel Query Editor¶
3.5.1. V_RECORD¶
This view provides detail information of all call records on the system.
Column Name | Field Type | Width | Description |
|---|---|---|---|
callguid | character | 16 | |
channelid | integer | ||
datatype | smallint | Compression Type | |
calldirection | character | 1 | I for Incoming, O for Outgoing, U for Unknown. |
callerguid | character | 16 | Custom. |
datastatus | character | 1 | C for Complete, P for In Progress, R for Removed, I for Partial Removed. |
isarchived | boolean | Custom. | |
issuppressed | boolean | Was the call audio for this call Suppressed? | |
save | boolean | Is this call marked Protected. | |
extension | integer | Custom. | |
starttime_timestamp | timestamp without time zone | ||
stoptime_timestamp | timestamp without time zone | ||
duration | integer | Duration in seconds. | |
duration_text | interval | Interval datatype for duration. | |
lostdata | integer | Custom. | |
datablocks | integer | Mediasize in kilobytes; one block is 1k of data. | |
samplerate | integer | Sample rate of recording. | |
suppresedtime | integer | ||
unsuppressedtime | integer | ||
sourceid | integer | ||
channelname | text | ||
id | integer | Database record ID. | |
callguid | character | 16 | Custom; will be blank if there is no metadata associated with the call. |
dtmf | text | DTMF | |
calling_party | text | ||
caller_id | text | Caller ID. | |
annotations | text | Stored in XML format. |
V_Record will also include any Custom Fields, such as Location, Speech, Agent_ID, User_ID, etc., that are configured on the recorder.
3.5.2. V_ALERTHISTORY¶
This view provides detail information of all system alerts.
Column Name | Field Type | Width | Description |
|---|---|---|---|
alertcode | Integer | Numerical value that represents the alert code. | |
eventtime | Timestamp | The time the alert occurred | |
serial | Serial | Auto-incrementing field | |
alertguid | Character | 16 | A guide used by the system to track alerts |
displaytext | Character | 512 | The text message that displays in the alert |
isacknowledged | Integer | Has this alert been acknowledge | |
timeacknowledged | Integer | The time the alert was acknowledge | |
acknowledginguser | Character | 63 | The user that acknowledge the alert |
acknowledgingprocess | Character | 63 | The process used to acknowledge the alert |
isresolved | Integer | Has this alert been resolved. Only used if the alert requires resolution | |
resolveddisplaytext | Character | 512 | Message that is displayed once the alert is resolved |
timeresolved | Timestamp | Time the alert was resolved | |
resolvinguser | Character | 63 | User that resolved the alert |
resolvingprocess | Character | 63 | The process used to resolve the alert |
triggeringprocessname | Character | 63 | The process that triggered the alert |
triggeringusername | Character | 63 | The user that triggered the alert |
3.5.3. V_DAILYSTATISTICS¶
This view provides detail information of daily system statistics.
Column Name | Field Type | Width | Description |
|---|---|---|---|
Id | Integer | Auto-incrementing field | |
datetime | Timestamp | Date and time without time zone | |
callcount | Integer | Record count from date time | |
callcountsincelast | Integer | Difference between the call count and the | |
displaytext | Character | 512 | The text message that displays in the alert |