How to show the source DID in FreePBX call detail reports

 

Important
This is an edited version of a post that originally appeared on a blog called The Michigan Telephone Blog, which was written by a friend before he decided to stop blogging. It is reposted with his permission. Comments dated before the year 2013 were originally posted to his blog.

This article was originally published in July, 2011. This functionality may or may not be already present in newer versions of FreePBX.

NOTE:  If you have installed a separate FreePBX CDR report tool module and it does not already include this functionality, you may need to do this before you follow these instructions: Copy this article (starting with the paragraph just before “[custom-from-trunk-accountcode]”) and paste it into a text editor, then use the editor’s search-and-replace to search for the string “accountcode” and change every occurrence of it to “did” (without the quotes in both cases), and follow those instructions.  That way you’ll be saving your DID information to the did field created in the CDR database by the module. I cannot tell you if you need to do this or not, because the last version of FreePBX I used was 2.9, and I have no plans to upgrade.

Another thing I am posting just so I can find it later…

Many people have wished for a way to see the source DID in FreePBX call detail reports.  It turns out there is a pretty easy way to do it IF you are not using Account Codes in your system, and have no plans to do so.  Many FreePBX users never use account codes so the following method will work for them.

Asterisk stores its CDR data in /var/log/asterisk/cdr-csv/Master.csv.  If you know the format in which it stores its data, you realize that the first field, accountcode, isn’t used on most systems.  Since Asterisk currently doesn’t store the source DID for a call, we can repurpose the accountcode field to store our source DID information.

So, there are two things that need to be done.  The first is to get the DID into the accountcode and to do that, we simply add a new context to /etc/asterisk/extensions_custom.conf as follows:

[custom-from-trunk-accountcode]
exten => _X!,1,Set(CDR(accountcode)=${EXTEN})
exten => _X!,n,Goto(from-trunk,${EXTEN},1)
exten => h,1,Macro(hangupcall,)

NOTE: The above assumes that all your DID’s are entirely numeric.  It will fail (and calls may not reach your inbound routes) if you do not first strip off any non-numeric characters, or change the context to accept them.  For example, if a provider sends a + at the start of the DID (NOT the Caller ID, which is more common), then try changing both occurrences of _X! to _[+X]!

Then for each of our trunks, we go into FreePBX and change the context= line from context=from-trunk to:

context=custom-from-trunk-accountcode

If by some chance you are already sending calls from one or more of your trunks to a custom context in extensions_custom.conf rather than from-trunk, you can simply add one of the following lines to that custom context. If you add it as the first line, use:

exten => _X!,1,Set(CDR(accountcode)=${EXTEN})

And make sure that any subsequent line is not also numbered “1”.  If it’s NOT the first line, use:

exten => _X!,n,Set(CDR(accountcode)=${EXTEN})

That should get your DID into the accountcode field of /var/log/asterisk/cdr-csv/Master.csv

To actually display the field, I refer you to this article on the PSU VoIP blog:

Modify FreePBX call reports to show destination channel

Read that article and the comments underneath, because it explains the principle of displaying additional fields in the FreePBX CDR.  This is how I set up my /etc/asterisk/call-log-table.php file:

calldate", "10%", "center", "SORT", "19");
        $FG_TABLE_COL[]=array ("Source Channel", "channel", "14%", "center", "", "32");
        $FG_TABLE_COL[]=array ("Source DID", "accountcode", "10%", "center", "SORT", "20");
        $FG_TABLE_COL[]=array ("Source", "src", "10%", "center", "SORT", "30");
        $FG_TABLE_COL[]=array ("CLID", "clid", "20%", "center", "", "80",'','','','',
          '','filter_html');
        $FG_TABLE_COL[]=array ("Dest.", "dst", "10%", "center", "SORT", "30");
        $FG_TABLE_COL[]=array ("Dest. Channel", "dstchannel", "14%", "center", "", "32");
        $FG_TABLE_COL[]=array ("Disposition", "disposition", "6%", "center", "", "30");
        if ((!isset($resulttype)) || ($resulttype=="min"))
          $minute_function= "display_minute";
        $FG_TABLE_COL[]=array ("Duration", "duration", "6%", "center", "SORT", "30",
          "", "", "", "", "", "$minute_function");

        $FG_TABLE_DEFAULT_ORDER = "calldate";
        $FG_TABLE_DEFAULT_SENS = "DESC";

        // This Variable stores the argument for the SQL query
        $FG_COL_QUERY='calldate, channel, accountcode, src, clid, dst, dstchannel, disposition,
          duration';
        $FG_COL_QUERY_GRAPH='calldate, duration';

        // The variable LIMITE_DISPLAY define the limit of record to display by page
        $FG_LIMITE_DISPLAY=25;

        // Number of column in the html table
        $FG_NB_TABLE_COL=count($FG_TABLE_COL);

        // The variable $FG_EDITION define if you want process to the edition of the
        // database record
        $FG_EDITION=true;

        //This variable will store the total number of columns
        $FG_TOTAL_TABLE_COL = $FG_NB_TABLE_COL;
        if ((isset($FG_DELETION) && $FG_DELETION) || $FG_EDITION)
          $FG_TOTAL_TABLE_COL++;

        //This variable define the Title of the HTML table
        $FG_HTML_TABLE_TITLE=" - Call Logs - ";

        //This variable define the width of the HTML table
        $FG_HTML_TABLE_WIDTH="100%";
?>

As always you should copy and paste the code, so you get complete lines including anything that WordPress truncates on the display.

The above shows the Source DID column just to the right of the Source Channel column.  I also made Source DID a sortable column, so you can show your usage by DID by clicking on the “Source DID” column heading.

At the start I said that you can only use this if you’re not using Account Codes on your system.  Actually, that may not be strictly true.  A DID would normally only be saved for an incoming call, while an Account Code would normally only be saved for an outgoing call.  So, it may be possible to make that field do double duty, but since I don’t use Account Codes and don’t know anyone who does, I’ll leave that as an exercise for the reader.

4 thoughts on “How to show the source DID in FreePBX call detail reports

  1. ooh ooh! I use them!

    My systems I build use the accountcode to handle the billing for calls transiting my systems with arbitrary source and destination. The accountcode is usually their billing telephone number, or other unique identifier used to collate billing records, even if the caller is outpulsing their cellphone or something.

    1. Okay, so ONE person I know uses that field! 🙂 But then again, you’re not running FreePBX (as far as I know).

  2. NOTICE: All comments above this one were imported from the original Michigan Telephone Blog and may or may not be relevant to the edited article above.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.