Akom's Tech Ruminations

Various tech outbursts - code and solutions to practical problems

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.

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


  • 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. (
    1.  
    2. DROP TRIGGER IF EXISTS t_cdr_dst_fix;
    3. CREATE TRIGGER t_cdr_dst_fix BEFORE INSERT ON cdr
    4. FOR EACH ROW BEGIN
    5.    IF (NEW.dst = 's' OR NEW.dst = '~~s~~') AND NEW.lastapp = 'Dial' AND NEW.dcontext = 'dialout' THEN
    6.         SET NEW.dst = substring_index(substring_index(NEW.lastdata, '/', -1), ',', 1);
    7.    END IF;
    8. END;
    9.  
    (Note that in Asterisk 1.8, the dst value changed from 's' to '~~s~~')

    Fixing old records

    You probably have a lot of records that are missing dst and it'd be nice to recover them:
    1.  
    2. -- Check that the substitution logic will produce a valid number
    3. SELECT calldate, lastdata,
    4.  substring_index(substring_index(lastdata, '/', -1), ',', 1) number
    5.  FROM cdr WHERE (dst = 's' OR dst = '~~s~~') AND lastapp = 'Dial' AND dcontext = 'dialout' LIMIT 100;
    6.  
    7. -- How many bad records do we have?
    8. SELECT count(1)
    9.  FROM cdr WHERE (dst = 's' OR dst = '~~s~~')AND lastapp = 'Dial' AND dcontext = 'dialout' ;
    10.  
    11. -- Fix it
    12. SET autocommit = 0;
    13. UPDATE cdr SET dst = substring_index(substring_index(lastdata, '/', -1), ',', 1)
    14.  WHERE (dst = 's' OR dst = '~~s~~') AND lastapp = 'Dial' AND dcontext = 'dialout' ;
    15.  
    16.  
    17. -- Check that they now look good
    18. SELECT dst FROM cdr WHERE lastapp = 'Dial' AND dcontext = 'dialout'  ORDER BY calldate DESC;
    19.  
    20. commit; -- or rollback if they do not
    21.  


    And hopefully no more of that issue. (Beware: if you change the name of your dialout macro, this will stop working)

0 Trackbacks

  1. No Trackbacks

2 Comments

Display comments as (Linear | Threaded)
  1. Nice post...useful to solve my problem..thanks..
  2. You made my day!!!

Add Comment


You can use [geshi lang=lang_name [,ln={y|n}]][/geshi] tags to embed source code snippets.
Enclosing asterisks marks text as bold (*word*), underscore are made via _word_.
Standard emoticons like :-) and ;-) are converted to images.

To prevent automated Bots from commentspamming, please enter the string you see in the image below in the appropriate input box. Your comment will only be submitted if the strings match. Please ensure that your browser supports and accepts cookies, or your comment cannot be verified correctly.
CAPTCHA

What is the primary language of this blog? (Anti-SPAM question)


Submitted comments will be subject to moderation before being displayed.