Of course, you will need a database in which to store your files. 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,
Processing Call Detail Records
54
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". 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
value="sql"/> .
3. Save the file and exit. Issue the reload mod_cdr_csv command at the fs_cli.
4. Issue the fsctl send_sighup command at the 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 the fs_cli.
Chapter 3
55
3. List the contents of your log/cdr-csv/ directory and note the presence of a rotated
Master.csv file, for example Master.csv.2011-03-02-16-25-21.
4. The rotated Master.csv file is the one to use for inserting 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.2011-03-02-16-44-29 | tr \" \' | psql -U postgres
cdr
5. Confirm the presence of the record in the cdr table with a simple SQL query like
SELECT * FROM cdr . Delete the rotated Master.csv file.
How it works...
The mod_cdr_csv sql template writes out CDRs in the format of a single SQL INSERT
statement per line. A sample record looks like this:
INSERT INTO cdr VALUES ("Michael Collins","1001","1007","defa
ult","2011-03-02 17:02:21","2011-03-02 17:02:23","2011-03-02
17:02:25","4","2","NORMAL_CLEARING","e4cfe0b2-4531-11e0-b634-d7bcff4e7b8a","e4d6b072-4531-11e0-b635-d7bcff4e7b8a", "1001");
These INSERT statements can be piped into a database's command line client. Note the use
of tr to translate double quotes to single quotes for compatibility with PostgreSQL.
Your production environment may have specific requirements when it
comes to things like single versus double quotes in PostgreSQL. Using
the Unix tr command is one method of handling the issue. You could
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 could also archive those to another disk volume as a backup.
