Set the Destination Path & Filename option to the location where the retrieved CDR records are to be saved. We recommend you set this value to c:\bill\smdr.raw.
Alternatively, you can press the Browse button to locate and select the desired location.
Use the Overwrite existing file when merging new transfers option to identify if the file identified in the Destination Path & Filename option is to be overwritten each time CDR records are retrieved from the ShoreTel server. This option is typically only utilized during testing. Activating this option may cause records to be lost. As a result, we strongly recommend leaving this box unchecked.
Use the Polling Mode option to determine which CDR records are to be retrieved from the ShoreTel server. Set this option to “1 Most Recent Records”. This option instructs the WinLINK ODBC software to retrieve only new CDR records from the ShoreTel server. Using the All Records option will instruct the WinLINK ODBC software to retrieve all CDR records from the ShoreTel server. Warning: Using the All Records
option could result in duplicate call records being stored in the Revolution Web Call Accounting database. As a result, this option should only be utilized if the Revolution Web Call Accounting software database has been corrupted and lost all call records.
The Unique Id field is only utilized when the Polling Mode option is set to “1 Most Recent Records”. Only CDR records on the ShoreTel server whose ID field is greater than the value in the Unique ID field will be retrieved. At the conclusion of each CDR retrieval the Unique Id value will be updated with the largest ID value contained in the retrieved CDR records. For newly created profiles the Unique ID field should either be blank or set to minus 1 (-1).
Use the SQL Statement(s) text box to specify the SQL query utilized to retrieve CDR records from the ShoreTel server. The required SQL statements can be automatically generated by setting the Populate with drop down list box to “ShoreTel 5.x/6.x” and then pressing the Set button. The recommended SQL statement(s) value is as follows:
Select *,ID as IDN from CALL where ID > {idn} order by ID 10. Press the OK button to save your profile.
You have now successfully configured a WinLINK ODBC Profile to retrieve CDR records from a ShoreTel server (version 6.x or earlier). You must now configure the Revolution Web Call Accounting software to utilize this profile (see Configuring Revolution Web Retrieving CDR via an ODBC Connection).
ODBC Connection to ShoreTel MySQL CDR Database
Revolution Web software can be configured to retrieve CDR records from a ShoreTel MySQL CDR database (requires ShoreTel 7.x or later). The following details the configuration steps required to support an ODBC connection to a ShoreTel MySQL CDR database.
1. Start the Revolution Web WinLINK ODBC software module. Press the Windows Start button and then select the Run command. The following window should be visible on your screen.
Type the following bolded text “c:\program files\rsi\cms\drivers\wodbc.exe” into the Open drop down box and press the OK button.
Please note on Windows 64 bit computers the following bolded text “c:\program files (x86)\rsi\cms\drivers\wodbc.exe” should be entered into the Run box.
2. The WinLINK ODBC software main window should now be visible on your screen.
Enter a Profile name identifying your ShoreTel telephone system (i.e. ShoreTel or ShoreTelNY). Any name can be used. Please note spaces are not permitted in a profile name (i.e. please use an underscore instead of a space).
3. Press the Add or Modify an ODBC Connection button. The Windows ODBC Data Source Administrator dialog should now be visible on your screen.
A Database Source Name (DSN) containing the database details required for an ODBC connection to the ShoreTel telephone system must be created. Either a User DSN or a System DSN can be utilized. We recommend a System DSN be used as this will allow retrieval of CDR data to continue regardless of which user is logged into the computer.
4. Click the System DNS tab and then press the Add button. The Create New Data Source dialog will now be visible.
Locate and select the MySQL ODBC Driver compatible with your ShoreTel phone
system (i.e. MySQL ODBC 3.51 or MySQL ODBC 5.1 driver). The MySQL ODBC Driver version used will depend on what version your ShoreTel phone system is currently running. ShoreTel 7.0, 7.5 and 8.x use MySQL ODBC driver version 3.51.x, while ShoreTel 9.x and later uses MySQL ODBC driver version 5.1.x. Please note these options will only be visible if the MySQL ODBC driver has been installed on your
computer. If these options are not visible then locate and download the required MySQL driver. MySQL drivers can be found at one of the following locations.
MySQL ODBC 3.51: http://dev.mysql.com/downloads/connector/odbc/3.51.html MySQL ODBC 5.1: http://dev.mysql.com/downloads/connector/odbc/5.1.html
5. Press the Finish button to create the new data source. The MySQL Connector/ODBC Driver 3.51 or 5.1 dialog should now be visible on your screen (pictured below).
The Data Source and Description fields are utilized to identify and describe the ODBC connection being defined. Set the Data Source field to a name that identifies the ShoreTel server being utilized for this ODBC connection. For example if the customer has a single ShoreTel server then set this field to “ShoreTel CDR”. However if they have multiple ShoreTel servers and the server we are connecting to is in New York then use ShoreTel CDR NY. The Description field is optional and is used to provide a brief description identifying the connection we are configuring. We recommend you set this value to “ShoreTel CDR Database Table”. Note: the values entered into these fields are labels and do not impact the ODBC configuration.
The Server and Port fields identify the ODBC connection settings utilized to retrieve CDR records from the ShoreTel CDR database. Set the Server field to the IP Address or computer name of the ShoreTel Server. The Port field value specified must match the port value utilized by the ShoreTel Server. The default port value is 3306.
A User Name and Password is also required to connect to the ShoreTel CDR database. The default User Name is st_cdrreport. The default Password is passwordcdrreport.
Set the Database field to the ShoreTel CDR database name (i.e. shorewarecdr).
Press the Test button to confirm an ODBC connection to the ShoreTel CDR database can be successfully completed using the configuration settings you have specified. If the ODBC settings provided are incorrect a message box will be displayed indicating a connection attempt to the ShoreTel server has failed. Please verify the settings provided are correct and then re-test the configuration by pressing the Test button again. The following message box will be displayed if the connection settings provided are correct.
You have now successfully tested your ODBC connection to the ShoreTel Server. Press the OK button to create the DSN.
Press the OK button to close the ODBC Data Source Configuration window and return to the WinLINK ODBC main window.
6. Click the WinLINK ODBC DSN drop down list box to select the DSN created in steps 3-5 (i.e. ShoreTel CDR. The WinLINK ODBC main window should now resemble the
following.
7. Press the Properties button. The Properties dialog box should now be visible on your screen.
The Profile Name field should contain the profile name you specified in step 2. If this box is blank then enter a Profile name identifying your ShoreTel server (i.e. ShoreTel or ShoreTelNY) into the space provided (spaces are not permitted in a profile name - use an underscore instead of a space).
8. If a dial-up connection is required to connect the ShoreTel server then click the Connect tab and activate the Use Dial Up Connection option. Otherwise proceed to step 9.
Note: A dial-up connection is not usually required.
9. Click the Retrieval tab. The Retrieval settings window will now be visible on your screen.
Set the Destination Path & Filename option to the location where the retrieved CDR records are to be saved. We recommend you set this value to c:\bill\smdr.raw.
Alternatively, you can press the Browse button to locate and select the desired location.
Use the Overwrite existing file when merging new transfers option to identify if the file identified in the Destination Path & Filename option is to be overwritten each time CDR records are retrieved from the ShoreTel server. This option is typically only utilized during testing. Activating this option may cause records to be lost. As a result, we strongly recommend leaving this box unchecked.
Use the Polling Mode option to determine which CDR records are to be retrieved from the ShoreTel server. Set this option to “1 Most Recent Records”. This option instructs the WinLINK ODBC software to retrieve only new CDR records from the ShoreTel server. Using the All Records option will instruct the WinLINK ODBC software to retrieve all CDR records from the ShoreTel server. Warning: Using the All Records option could result in duplicate call records being stored in the Revolution Web Call Accounting database. As a result, this option should only be utilized if the Revolution Web Call Accounting software database has been corrupted and lost all call records.
The Unique Id field is only utilized when the Polling Mode option is set to “1 Most Recent Records”. Only CDR records on the ShoreTel server whose ID field is greater than the value in the Unique ID field will be retrieved. At the conclusion of each CDR
retrieved CDR records. For newly created profiles the Unique ID field should either be blank or set to minus 1 (-1).
Use the SQL Statement(s) text box to specify the SQL query utilized to retrieve CDR records from the ShoreTel server. The required SQL statements can be automatically generated by setting the Populate with drop down list box to either “ShoreTel 7.x or later” or “ShoreTel (CDR/Workgroups)” and then pressing the Set button. If your ShoreTel system is using workgroups we recommend you utilize the ShoreTel (CDR/Workgroups) option otherwise select the “ShoreTel 7.x or later” option.