Frequently, it is necessary to put CDR information into a database such as PostgreSQL or other SQL and NoSQL databases. FreeSWITCH has various modules for writing CDRs directly to many databases, but the preferred architecture is writing CDRs to the disk or posting them to a web server, and then processing them so that they can be inserted into a database. Many engineering reasons lead to this architecture (for example, avoiding dependence on direct, real-time interaction with the database), and most of them relate it to integrity and resilience. This recipe discusses the recommended method of writing SQL-based CSV files and then using those to update a backend database.
Getting ready
Of course, you will need a database in which to store your CDRs. Any SQL-compliant database will work as long as you can use the command line to execute SQL statements. Create a database for your CDRs, and allow any necessary access. This is completely dependent upon the type of database you have. Consult your database documentation for specific instructions. You will also need a table for the CDRs. The following CREATE TABLE syntax for a PostgreSQL database will work for the existing sql template in cdr_csv.conf.xml:
CREATE TABLE cdr (
caller_id_name character varying(30), caller_id_number character varying(30), destination_number character varying(30), context character varying(20),
start_stamp timestamp without time zone, answer_stamp timestamp without time zone, end_stamp timestamp without time zone, duration integer,
billsec integer,
hangup_cause character varying(50), uuid uuid,
bleg_uuid uuid,
accountcode character varying(10), read_codec character varying(20), write_codec character varying(20) );
A similar CREATE TABLE command works for MySQL, as follows:
CREATE TABLE cdr (
caller_id_name varchar(30) DEFAULT NULL, caller_id_number varchar(30) DEFAULT NULL, destination_number varchar(30) DEFAULT NULL, context varchar(20) DEFAULT NULL,
start_stamp datetime DEFAULT NULL, answer_stamp datetime DEFAULT NULL, end_stamp datetime DEFAULT NULL, duration int(11) DEFAULT NULL, billsec int(11) DEFAULT NULL,
hangup_cause varchar(50) DEFAULT NULL, uuid varchar(100) DEFAULT NULL,
bleg_uuid varchar(100) DEFAULT NULL, accountcode varchar(10) DEFAULT NULL, domain_name varchar(100) DEFAULT NULL );
All the examples in this recipe will use a database name of cdr and a table name of cdr as well. The last thing to do is to set the sql template as the default CDR template. Follow these steps:
1. Open conf/autoload_configs/cdr_csv.conf.xml.
2. Change the default-template parameter to <param name="default- template" value="sql"/>.
3. Save the file and exit. Issue the reload mod_cdr_csv command at fs_cli. 4. Issue the fsctl send_sighup command at fs_cli to rotate the log files. You are now ready to create and process CDRs.
How to do it...
Follow these steps to get a call record into your new database table:
1. Make a test call from one phone to another, answer, wait a moment, and then hang up (you should now have at least one record in Master.csv).
2. Issue the fsctl send_sighup command at fs_cli (or cdr-csv rotate). 3. List the contents of your log/cdr-csv/ directory, and note the presence of a rotated Master.csv file. For example: Master.csv.2015-04-06-03-37-51. 4. The rotated Master.csv file is the one to use to insert records into your database.
You will need to use your specific database's command-line client to insert the records. For PostgreSQL, use a command like this:
cat Master.csv.2015-04-06-03-37-51 | tr \" \' | psql -U postgres cdr
5. Confirm the presence of the record in the cdr table with a simple SQL query, such as SELECT * FROM cdr. Then delete the rotated Master.csv file.
How it works...
The mod_cdr_csv sql template writes CDRs in the format of a single INSERT SQL statement per line. A sample record looks like this:
INSERT INTO cdr VALUES ("Giovanni Maruzzelli","1002","1005","d efault","2015-04-06 03:46:00","2015-04-06 03:46:01","2015-04-06 03:46:11","11","10","NORMAL_CLEARING","06d18352-52f3-4d90-836c- d385a10ea6e3","0a32de98-3318-43aa-8439-c64ddfa9c212", "1002");
These INSERT statements can be piped into a database's command-line client. Note the use of tr for conversion of double quotes into single quotes for compatibility with PostgreSQL.
Your production environment may have specific requirements when it comes
to things such as single versus double quotes in PostgreSQL. Using the tr
Unix command is one method of handling the issue. You can also modify the template to use single quotes instead of double quotes.
Finally, after confirming that the CDR was successfully inserted into the database, we deleted the rotated file. We can also archive those to another disk volume as a backup.
There's more...
In fact, you can set up a CDR database on a completely separate machine, and use basic tools such as fs_cli to rotate logs and scp or ftp to pull the files to the local database server. An intelligent script can then notify the system administrator of any issues. Also, as long as there is disk space on the FreeSWITCH server, no CDR records will be lost in the event of a failed connection between the CDR server and the FreeSWITCH server. CDRs will continue to be written to the disk on the FreeSWITCH server, and can be collected and processed when connectivity is re-established.
See also
f Refer to the Getting familiar with the "fs_cli" interface recipe in Chapter 4, External Control