NOTE: This article is applicable to Sky customers. If you are a MiCloud customer, see the Exporting Call Detail Records article.
Table of Contents
Overview
Requirements
Generating a Report
Parameters
Data in Output File
Examples of Use
Limitations
Overview
CDR Data Export is an API scripting feature that enables customers to extract CDR (Call Detail Record) data generated by their Sky phone system. This scripting feature enhances our customers’ ability to obtain and utilize CDR data for all of their users. Currently, the Sky Account Portal user interface provides a Usage Log report that provides CDR data for all users. However, the Usage Log report requires manual effort to export CDR data.
When using CDR Data Export, an HTTP request (customized URL) retrieves an export of a customer’s CDR data directly from the CDR database. The HTTP request is scripted with specific required (and optional) parameters to extract CDR data to a standard .CSV file. The CDR Data Export API can be used by a client-side application to automate the extraction of CDR data on a one-time or recurring basis.
Requirements
- Recent version of Firefox, Internet Explorer, Chrome, or Safari browser.
- An Authorized Contact role in the Sky phone system.
- Valid log in credentials for https://portal.shoretelsky.com/: username (typically your business email address) and user password.
- Permission to use CDR Data Export, which can be obtained via a request to Support.
Generating a Report
- Use the following base URL: https://portal.shoretelsky.com/DataExport/CDRData
- Add a "?" (question mark) followed by the required (and any optional) parameters to the base URL.
- Separate each parameter from the next parameter with an "&" (ampersand). See the Example URL section below.
- Submit the completed URL and enter valid credentials for username and password when prompted. For more information, see the Authentication Options section below.
- Save a local copy of the auto-generated CDR output .csv file
NOTE: CDR data, which is normally updated every one to three hours, is usually available for the entire previous day by 12 PM Eastern.
Authentication Options
Authentication is performed by transmitting the userid= and pass= parameters in the initial HTTP request headers via HTTP POST.
- If a web browser is being used to extract a report, the Account Portal will prompt for a username and password after the HTTP request is received.
- If the request is being made via programming, the POST headers should include the username and password with the original request. See the "wget" code in the Examples of Use section below.
Parameters
Required Parameters
The following table lists the two required parameters. Provide the startDate AND endDate parameters to submit a CDR Data Export request to extract only the Call Detail Records that are in the desired range of dates provided by these parameters.
NOTE: The time internal (range of dates) for exporting CDR data is limited to 1 week (7 days). Exporting data for a longer time interval is not supported and may result in a "timing-out" of resources.
| Parameter Syntax | Description |
|---|
| startDate= | The format is: YYYY-MM-DD |
| endDate= | The format is: YYYY-MM-DD |
Optional Parameters
The following table lists the optional parameters that can be coded when submitting a CDR Data Export request.
| Parameter Syntax | Description |
|---|
| lastId= |
The lastId parameter is designed specifically for customers who submit multiple requests per day and want to avoid receiving a subset of the same (overlapping) data in multiple output files.
- Note: Use of the lastId parameter is limited to requesting CDR data for the current date and previous date. When a value is supplied for the lastId parameter, the CDR Data Export process will ignore the values entered for the required startDate and endDate parameters.
|
| subscribers= | Any 10 digit phone number that belongs to the customer’s account. Multiple numbers can be specified in a comma-separated list. No parameter returns all subscriber phone numbers. The format is 9871234567 (no spaces or special characters). |
| connected= | Options are ‘yes’, ‘no’, and ‘all’ to enable including live calls, non-live calls, or all calls. This parameter defaults to yes. No parameter returns only live calls. |
| accountId= | Used to obtain CDR data for a different account other than the account to which the authenticating user belongs. Requires proper permissions set on the server side. This parameter defaults to the account of the authenticating user. |
| direction= | Options are ‘inbound’, ‘outbound’, and ‘forward’. No parameter returns all directions. |
Data in Output File
The following lists and describes the data items provided by a CDR Data Export output .csv file. When viewing the data in this .csv file, if any column displays the data as a formula, for example, "#NAME?" instead of the actual data value, see the Converting Formulas to Viewable Data in CSV Files article for helpful instructions.
- Location - Customer account location.
- Date - Date when the call was initiated (in mon/dd/yyyy format).
- Time - Time when the call was initiated (in hh:mm:ss 24-hour format).
- TN Originating - Telephone number of the profile placing the call (in 1NPANXXxxxx format).
- Direction - Direction of the call (INBOUND, OUTBOUND, INTERNAL, or FORWARD). Inbound and outbound indicate whether the subscriber placed or received the call. Internal indicates calls within an account. Forward indicates calls that were transferred.
- Originating City - City where the call was placed. For US and Canadian locations, the CITY is listed (in upper-case text). For other international locations, the country is abbreviation listed. For toll-free calls, this field is left blank.
- Originating State - State where the call was placed. For US and Canadian locations, the two-letter abbreviation for the state or province is listed. For other international locations, the country is listed. For toll-free calls, this field is left blank.
- TN Dialed - The phone number dialed to place the call.
- Destination City - City where the call was received. For US and Canadian locations, the CITY is listed (in upper-case text). For other international locations, the country is listed. For toll-free calls, this field is left blank.
- Destination State - State where the call was received. For US and Canadian locations, the two-letter abbreviation for the state or province is listed. For other international locations, the country abbreviation is listed. For toll-free calls, this field is left blank.
- Internal - Values are Yes and No. 'Yes' indicates internal calls made between users of an account within the Sky phone system. 'No' indicates all other (non-internal) calls.
- Call Type - Indicates the type of call (DOMESTIC, INTERNATIONAL, or TOLLFREE).
- Duration Minutes - Length of call expressed in minutes rounded to two decimal places.
- Charge - Cost associated with the call. Charges are normally incurred only for calls that are not included with the associated phone profile type, such as international calls and 900 numbers. Additionally, there are some phone numbers utilized by a customer's account that are not associated with a phone profile that can incur charges (see the Usage Without Services article).
- Account Code - When applicable, the account code associated with the call. Note that some customers have Authorization Codes or Client Matter Codes setup for groups of users and/or certain types of calls.
- Answered By Target = Values are Yes and No. “Yes” indicates the call was initially answered by the dialed (target) phone number, even if the call is subsequently forwarded to another phone number. “No” indicates the call was initially answered by a phone number other than the dialed (target) phone number. For "No," the phone number that initially answered the call is normally associated with an Auto Attendant, Ring Group, Shared Line Appearance, or other system component.
- Id = A unique integer value that is incremented as new rows of CDR data are added to the CDR database for a specific customer account. The purpose of this Id value is for customers to use with the optional lastId parameter. See the lastId parameter in the Optional Parameters section above for complete instructions.
Example Output File
In the example below, the last two columns of data, Answered by Target and Id, are not shown.
Examples of Use
The following is an example of a URL with only the two required parameters:
The following is an example of a URL with the two required parameters and the optional lastId parameter:
The following is an example of "wget" code used to submit an example URL with the use of optional parameters:
$ wget --user=user@shoretel.com --password=pass "https://portal.shoretelsky.com/DataExport/CDRData?startDate=2015-02-01&endDate=2015-02-15&direction=inbound&connected=true&subscribers=6464425780,6464421610"
The wget code above results in the following response:
HTTP request sent, awaiting response... 401 Unauthorized
Connecting to portal.shoretelsky.com ... connected.
HTTP request sent, awaiting response... 200 OK
Length: 1389 (1.4K) [text/csv]
Saving to: `CDRData@startDate=2014-02-01&endDate=2014-02-15&direction=inbound&connected=true&subscribers=6464425780,6464421610'
100%[====================================================================================================================>] 1,389 --.-K/s in 0s
2015-04-01 12:10:25 (11.6 MB/s) - `CDRData@startDate=2014-02-01&endDate=2014-02-05&direction=inbound&connected=true&subscribers=6464425780,6464421610' saved [1389/1389]
Limitations
The following are limitations associated with the CDR Data Export API:
- The maximum number of API requests per day is 24 (data is normally updated approximately every one to three hours).
- The time interval (range of dates) limit for exporting CDR data is 1 week (7 days). Exporting data for a wider range of dates (longer time interval) is not supported.
- All CDR data older than 13 months is not accessible via the CDR Data Export API. Data older than 13 months is archived and a formal request via a Support Case is required to obtain the data.
- All CDR data that is older than 7 years is deleted from the archives.
Please note that the data does not change after it is written so there is no benefit to downloading the same data more than once. Multiple downloads of the same data may cause the maximum number of requests to be exceeded.
