Asterisk CDR records 's' for DST when Dialing from Macros
Posted by Admin • Friday, July 22. 2011
At some point in the Asterisk 1.6 evolution the CDR records stopped being useful in my setup for outgoing calls - instead of a nice number (eg 18005551212) in the dst field of the record, I now have an s. I log my CDR to MySQL and I do use a macro (with AEL) to dialout. My macro is even called dialout.
According to this bug, this is a known issue and no immediate fix is expected. Not wanting to rewrite the CDR UI to use yet another column, I figured that I can fix this with a MySQL trigger, so here is one possible workaround.
According to this bug, this is a known issue and no immediate fix is expected. Not wanting to rewrite the CDR UI to use yet another column, I figured that I can fix this with a MySQL trigger, so here is one possible workaround.
Fixing new records
First of all, we want all new records to have the destination phone number in dst instead of s. In my case, the cdr record contains the following:- lastapp: Dial
- lastdata: "SIP/someprovider"/18005551212,60,WTwtL(21600000)
- dcontext: dialout (this is the name of my macro. Do not change this once you set this up or this trigger will stop working!)
- dst: s
- DROP TRIGGER IF EXISTS t_cdr_dst_fix;
- CREATE TRIGGER t_cdr_dst_fix BEFORE INSERT ON cdr
- FOR EACH ROW BEGIN
- IF (NEW.dst = 's' OR NEW.dst = '~~s~~') AND NEW.lastapp = 'Dial' AND NEW.dcontext = 'dialout' THEN
- SET NEW.dst = substring_index(substring_index(NEW.lastdata, '/', -1), ',', 1);
- END IF;
- END;
- -- Check that the substitution logic will produce a valid number
- SELECT calldate, lastdata,
- substring_index(substring_index(lastdata, '/', -1), ',', 1) number
- FROM cdr WHERE (dst = 's' OR dst = '~~s~~') AND lastapp = 'Dial' AND dcontext = 'dialout' LIMIT 100;
- -- How many bad records do we have?
- SELECT count(1)
- FROM cdr WHERE (dst = 's' OR dst = '~~s~~')AND lastapp = 'Dial' AND dcontext = 'dialout' ;
- -- Fix it
- SET autocommit = 0;
- UPDATE cdr SET dst = substring_index(substring_index(lastdata, '/', -1), ',', 1)
- WHERE (dst = 's' OR dst = '~~s~~') AND lastapp = 'Dial' AND dcontext = 'dialout' ;
- -- Check that they now look good
- SELECT dst FROM cdr WHERE lastapp = 'Dial' AND dcontext = 'dialout' ORDER BY calldate DESC;
- commit; -- or rollback if they do not
Therefore I can extract the original destination from lastdata. String splitting is a little weird in MySQL, but substring_index() comes close. The following is a trigger that can take care of things. Note that you may need to change the macro name from dialout to whatever yours is called. (
Fixing old records
You probably have a lot of records that are missing dst and it'd be nice to recover them:And hopefully no more of that issue. (Beware: if you change the name of your dialout macro, this will stop working)
Nice post...useful to solve my problem..thanks..