Write Call Details Records into a database (kb2491)

The information in this article applies to:

• SwyxWare 2015

[ Summary | Information | Links ]

Summary

SwyxWare is able to write call details records into a database instead of text files. This article describes how to configure this.

Information

SwyxServer writes CDRs into a table named IpPbxCDR. To create this table, use one of the SQL initialization scripts downloadable at the end of this article, or create it with the same columns using an appropriate database tool.

Using Microsoft SQLServer

To write CDRs into a Microsoft SQLServer database table the connection string has to be the following

Provider=SQLOLEDB[;Data Source=<serverName>];Initial Catalog=<databaseName>[;Integrated Security=SSPI|User ID=<UserID>;Password=<Password>]

where

• <servername> is the SQLServer name to connect to. Omit this parameter when using a local SQLServer
• <databasename> is the database on the sqlserver to connect to. If you omit this parameter the default database of the user is used (see below).
• Parameters in square brackets "[]" are optional. The "|" character defines aalternative parameters.

If you specifyIntegrated Security=SSPI the database will be accessed using windows authentication (recommended). You have to allow local group SwyxWare Administrators or the SwyxServer service account read and write access to the database table IpPbxCDR.

Alternatively you can use legacy SQLServer authentication by specifying a userid and password: User ID=<UserID>;Password=<Password> where <UserID> is the SQLServer login to use and <Password> the password. You have to grant read and write access to the IpPbxCDR database table for this login.

Example

• To write CDRs into a Microsoft SQLServer database named "SwyxCallDetails" on a computer named "ALICE" using Windows authentication the connection string is Provider=SQLOLEDB;Data Source=ALICE;Initial Catalog=SwyxCallDetails;Integrated Security=SSPI

  SwyxWare uses the SwyxServer service account to connect to the database in this case.

• To write CDRs into a Microsoft SQLServer database named "SwyxCallDetails" on a computer named "ALICE" using SQLServer login "Bob" with password "1234" the connection string is Provider=SQLOLEDB;Data Source=ALICE;Initial Catalog=SwyxCallDetails;User ID=Bob;Password=1234

  Note: Always use an own database and not the SwyxWare database ippbx.

Using an ODBC data source

To write CDRs into a ODBC datasource the connection string has to be created as following: DSN=<datasourcename>

Datasources have to be created as System DSN in the Windows ODBC Data Source Administrator (32-bit). This requires that the ODBC driver required for the database is installed.

Starting with SwyxWare 2015 R40.2, the following databases are supported for the writing of CDR entries:

• Microsoft SQL Server
• MySQL
• PostgreSQL

Troubleshooting

SwyxServer writes a warning event log entry into the application event log when the specified database cannot be connected and uses the CDR setting configured in SwyxWare Administration.

Use new schema (Update to SwyxWare 2015 R40)

Since SwyxWare 2015 R40 a new column has been added to the database schema. To alter existing databases please use the ALTER script provided below or do the necessary steps manually. The new column links corresponding calls that included the Mobile Extension Manager. To enable the new column, open the Windows Registry by starting regedit.exe and go to the following path:

HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Swyx\IpPbxSrv\CurrentVersion\Options 

Create a new DWORD (32-Bit) Value with the name "CDRVersion" and the value 2. After that restart the services.

Links

• Update existing MS SQL CDR database to new scheme of SwyxWare 2015 R40
  The following ZIP file contains an SQL statement to adapt an existing MS SQL CDR database to the new scheme of SwyxWare 2015 R40.
  kb2491_cdr_alter.zip
• Write Call Details Records into a database (from SwyxWare 2015 R40-2)
  The following ZIP file contains an SQL CreateTable script to create the needed table into an MS SQL, MySQL and PostgreSQL database.
  kb2491_cdr_from_2015_R40-2.zip
• Write Call Details Records into a database (from SwyxWare 2015)
  The following ZIP file contains an SQL CreateTable script to create the needed table on a MS SQL Server.
  kb2491_cdr_from_2015.zip

The third-party contact information included in this article is provided to help you find the technical support you need. This contact information is subject to change without notice. Swyx in no way guarantees the accuracy of this third-party contact information nor is responsible for it's content.