From time to time you may require creating custom reports that interact with external resources.
The Switchvox API provides access to the CDR record. Full documentation is available at https://api.switchvoxuc.com/.
If you have advanced reporting needs, you may be better suited to store the CDR records in your own database, allowing you to integrate with your other business data without requiring a database dip to your production phone system.
While all of the fields are self-explanatory, there are a few that may make your life a lot easier if you understand the structure.
{'server_start_time': '',
'to_number': '6000',
'origination': 'outgoing',
'to_account_id': '1600',
'to': 'Queue 6000 <6000>',
'cdr_call_id': '2630713',
'id': '2630713',
'from_account_id': '1888',
'uniqueid': '1551226356.892267',
'start_time': '2018-02-20 16:12:36',
'to_name': 'Queue 6000',
'total_duration': '3',
'from_number': '8675309',
'events': {'event': [] },
'from_name': 'Tommy Tutone',
'talk_duration': '0',
'from_': 'Tommy Tutone <8675309>'}The id field or more specifically the cdr_call_id is the ID for the call record (CDR). This can be used to reference the call event details.
The uniqueid field is can be used as well depending on your preferences.
The to_/from_ account_id options are related to the extension on your Switchvox.
This is also the internal ID of the extension and is unique across the system. If you have a high turnover or change users often, this ID allows you to keep historical references between user changes.
If however you just rename an extension, then you are not changing the account_id, and your historical data will not give you the results you want. Good administration = good reporting.
Normalizing your data: Another thing you may have to address depending on your database, or programming language and libraries of choice, is assuring you can insert a record even if the field is not available. This is because the API only populates fields if data is available. For example, *_account_id will only be populated if the call originated from an internal extension. Your injection logic should account for this.
In the example above we did not show all the events, however, the events element will have a list of all events for the specific call. Depending on your reporting needs, this will have a wealth of details and will be the source of very specific call flow data.
{'server_start_time': '',
'start_time': '2019-02-20 16:18:05',
'type': 'HANGUP',
'display': 'Call was hung up by WIRELESS CALLER <+8675309>',
}There are normally only 4 fields, server_start_time, start_time, type, and display. However, on incoming calls, you will get an additional field called incoming_did. If your building a database table and don't account for this you may get an insert error because your database table is missing a column.
Don't pay much attention to the server_start_time, it always appears to be empty, but may be used in the future.
If you are writing this data to a database, such as MySQL, you will want to create a separate table to store the CDR events. When storing this data use the cdr_call_id as a reference for each event, that way you can later perform a standard SQL query and pull all the information together.
Store this data in your database however you want. Manipulate it to your needs, use the *_account_id options to track individual users, regardless of if the extension was reassigned to a new user, use the cdr_call_id to reference related events to keep the call flow intact.
Once you have stored that data in a database of your choice, you can then perform SELECT statements against the data, to integrate with external data adding extra load to your phone system, and without having to worry about assigning users to the same extension.
The process is relatively straightforward for an experienced database administrator. If you do not have a database admin, don't worry, VOICE1 can build the integration storage for you, if you need assistance, drop us a line and we will get you going in short order.