Getting CDR/SMDR from ODBC data source into Call Accounting Mate

[sergey]

CAM 2.6.1.94 has built-in ability to query ODBC data sources for SMDR/CDR records.

Following steps are necessary to configure CAM 2.6.1.94 onwards to collect CDR from and ODBC data source:

A. Download and install ODBC driver for source database server (optional)
1.this step is completely ODBC driver specific, for more information please mail to support@callaccounting.ws

B. Configure CAM to connect to the source database server, managing CDR/SMDR records from a PBX
1. Open Configuration/PBX Communication page
2. Activate Port tab on the page
3. Select ODBC in the Port field on the tab
4. This will show ODBC Parameters section with the fields listed below:

     A. Connection string -  this is ODBC driver specific field, for more information this field please refer to the ODBC driver documentation
     B. SQL Statement - this field contains SQL statement used to query the data source. The statement must have following format:

                                         select [Key Field], [Field1], ..., [FieldN] from [Table Name] where [KeyField] > '?'

    where :

      [Key Field] - key field used to identify CDR/SMDR record, must be first field in the fields list in the SQL query
      [Field1], ..., [FieldN] - list of fields, storing information about CDR/SMDR record
      [Table Name] - name of the table, storing CDR/SMDR records
      ? - placeholder for last [Key Field] stored in the CAM database

     C. Source ID -  this field is used to define name of the variable to store last queried [Key Field], this variable is substituted
                                  instead of the ? placeholder whenever the query is scheduled for run
     D. Every - this field defines schedule for polling the database server for CDR/SMDR records

Note: [Key Field] and the ? placeholder are used to prevent CAM from querying same record more than once. The SQL query should be designed in such a way to retrieve only new CDR/SMDR records registered since the query was run last time.

5. Activate Driver tab and select appropriate driver in the PBX driver field (usually querying an ODBC data source requires special version
    of the driver, explicitly marked for use with ODBC sources)
6. Press Save button to apply the changes and start polling of the source database server

C. Checking the database server connection
1. Current version of CAM has no function, allowing to test the connection, so immediately after saving the ODBC source parameters
    you will have to check main error log for any error and diagnosing information
2. To check the log activate Log files/Main link on the left bar menu and scroll the log to the latest messages