We recently installed a billing system, which read cdr_csv (Master.csv) into the system. Unfortunately, by default, the FreePBX installation only enables cdr_mysql, which store only some of the fields and not in the right order. We need to convert it to native format. Any quick way to do that?
who’s native format?
A lack of details provides a lack of answers. Don’t know what version of ANYTHING you are running.
http://freepbx.org/forum/freepbx/installation/so-you-have-a-problem-and-want-help
Sorry about that. I’m running the following: FreePBX 2.5.1.1 w/ Asterisk 1.4.23.1.
Native Format has the CSV with the following order (pulled from http://www.voip-info.org/wiki/view/Asterisk+cdr+csv)
1)accountcode: What account number to use: Asterisk billing account, (string, 20 characters)
2)src: CallerID number (string, 80 characters)
3)dst: Destination extension (string, 80 characters)
4)dcontext: Destination context (string, 80 characters)
5)clid: CallerID with text (80 characters)
6)channel: Channel used (80 characters)
7)dstchannel: Destination channel if appropriate (80 characters)
8)lastapp: Last application if appropriate (80 characters)
9)lastdata: Last application data (arguments) (80 characters)
10)start: Start of call (date/time)
11)answer: Answer of call (date/time)
12)end: End of call (date/time)
13)duration: Total time in system, in seconds (integer)
14)billsec: Total time call is up, in seconds (integer)
15)disposition: What happened to the call: ANSWERED, NO ANSWER, BUSY, FAILED
16)amaflags: What flags to use: see amaflags::DOCUMENTATION, BILL, IGNORE etc, specified on a per channel basis like accountcode.
Here is a sample output from Master.csv
"",“3109366596”,“3347”,“from-did-direct”,""“Princesa Peters”" <3109366596>",“SIP/172.16.16.93-c439f9c0”,“SIP/3347-11cb2540”,“ResetCDR”,“w”,“2009-02-11 17:21:47”,“2009-02-11 17:21:50”,“2009-02-11 17:22:24”,37,34,“ANSWERED”,“DOCUMENTATION”,“1234372907.16005”,""
"",“2548”,“2526”,“from-internal”,""“Darling Higgins”" <2548>",“SIP/2548-c4567be0”,“SIP/252601-11ae0da0”,“Dial”,“SIP/2526&SIP/252601|30|Ttr”,“2009-02-11 17:21:55”,“2009-02-11 17:22:00”,“2009-02-11 17:22:26”,31,26,“ANSWERED”,“DOCUMENTATION”,“1234372915.16021”,""
All the fields are in the the correct order as stated. The issue you might have is that there are two additional fields added at the end. The next field is the UniqueId field, and the last one is useridfield which the documentation you point to also clearly states are possible:
In some cases, uniqueid is appended:
* uniqueid: Unique Channel Identifier (32 characters)
Note: You can edit the source file cdr_csv.c to enable CSV_LOGUNIQUEID (it’s disabled by default)
(same goes for userfield)
So you didn’t take that into account when you wrote your code (or the billing system you are using didn’t) that those extra fields might exist.
For the record NONE of this is controlled by FreePBX, that is how your copy of asterisk and the asterisk-addon were compiled with those options enabled.
If you had taken the time to review the asterisk source for your install you would have seen that.
The cdr_csv.c has a routine called build_csv_record which is what is responsible for putting all the fields in that order and it can’t be changed by FreePBX. More specifically the compiler directives CSV_LOGUNIQUEID and CSV_LOGUSERFIELD determine if those additional fields are added or not.
You will find this page of more help: http://www.voip-info.org/wiki/view/Asterisk+billing which by the way was a direct link at the bottom of the page you provided.
If you want the ultimate source in information take some time and review the Asterisk source code or go to asterisk.org and check those pages.
The order is not correct. This is how the table is built in mysql, and when I try to export using phpmyadmin, it stays in the same order. I can reorder VIA excel, but I’d rather try another way.
CREATE TABLE cdr (
calldate datetime NOT NULL default ‘0000-00-00 00:00:00’,
clid varchar(80) NOT NULL default ‘’,
src varchar(80) NOT NULL default ‘’,
dst varchar(80) NOT NULL default ‘’,
dcontext varchar(80) NOT NULL default ‘’,
channel varchar(80) NOT NULL default ‘’,
dstchannel varchar(80) NOT NULL default ‘’,
lastapp varchar(80) NOT NULL default ‘’,
lastdata varchar(80) NOT NULL default ‘’,
duration int(11) NOT NULL default ‘0’,
billsec int(11) NOT NULL default ‘0’,
disposition varchar(45) NOT NULL default ‘’,
amaflags int(11) NOT NULL default ‘0’,
accountcode varchar(20) NOT NULL default ‘’,
uniqueid varchar(32) NOT NULL default ‘’,
userfield varchar(255) NOT NULL default ‘’
);
ok maybe I’m missing something but the order of the fields in MySQL has nothing to do with the order of the fields for the log file master.csv located at /var/log/asterisk/cdr_csv/. These files are directly written out to by asterisk.
If you are NOT getting them automatically written check that the module cdr_csv.so is being loaded in asterisk and it will do it for you.
So what you’re missing is that I didn’t activate the cdr_csv output until a few months of production. I’m trying to export the mysql CDR data to be the same format as cdr_csv so I can capture the backlog before we activated the csv output, and so that we can import it in the Billing System.
ah, it’s all in the details. That one was not mentioned till now.
So what you really need to do is learn to use the cli for MySQL just enough to get it to do what you need.
Here is the general statement to use
SELECT [fields or *] INTO OUTFILE '/tmp/result.text’
FIELDS TERMINATED BY ‘,’ OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n’
FROM [table] WHERE [condition]
Note that if you use * it will use declared field order if you want a different order list them in that order.
Note that even while doing this the export still might have a issue with any text that has embedded quotes in it.
That worked. I know that this is ticky tacky, but maybe in the future, change the order of the SQL table declaration in SVN, so it’ll be an easier path for people who go through the same thing? It won’t mess up any functionality, but would’ve made my life so much easier.
Thanks for the help!
That will probably not happen unless they throw the whole table out and start a new one from scratch. The order of the fields is important to the upgrade process and the backup and restore process.
The way it works now is you can take a table with 15 fields export it and in a newer version add an additional field at the end and when you import that exported data it will fill the fields it has data for and leave that new added field blank. If the order is changed then many things that rely on it will break.
Your need for this is minor compared to the huge base of people who would upgrade. So that is how it would be judged.
Just did some research. It’s going to be a dump, recreate the table w/ correct order, and then import back. Nothing should break because it looks at the field name when you reimport. But nonetheless, I see where a lot of things can go wrong, so a select with the right order will work just fine. Thanks for the help.
There is not reason to reorder the fields, and as far as nothing breaking, there may be hundreds (thousands) of people out there with custom scripts already using the tables expecting that order. As a general rule of thumb, we DO NOT make changes like this unless there are good reasons, so we don’t break other people’s work. This is basic SQL usage, you can get the order you want, and the labels you want, with phpMyAdmin or any other tools or CLI access that allows you to query the db.
Hope this person isn’t back for more assistance a year from now complaining that a recent upgrade totally trashed his database. Of course, there won’t be any mention of the fact that the fields in his table were reordered to improve things. 
We do all of our billing in Crystal Reports, and the current database structure is JUST FINE!!!
Bill/W5WAF