Akom's Tech Ruminations

Various tech outbursts - code and solutions to practical problems

Data Warehousing and Metrics with Asterisk PBX Asterisk

Posted by Admin • Sunday, February 1. 2009 • Category: Asterisk

An Asterisk installation I am doing for a client has the promise of two interesting developments in the near future:

  1. Writing a CDR/Voicemail status-tracking application that makes sure that all client voicemails are handled within one hour and keeps track of what was done
  2. A future custom metrics/data warehouse implementation that will use both the CDR and VoiceMail data

With that in mind (and the fact that I'm using Mysql master-master replication across offices) I looked into storing the voicemails in the database as well. This is fairly well-documented. I had this up and running in no time, and now for each voicemail I have a neat row in my MySQL table.

The problem is - how do I connect the row in CDR table to the matching voicemail row? There are no columns that have data to allow you to do match one to the other. Even timestamps, if you were so desperate, are not exactly the same! So this is how I did it.

There are actually two ways I've tried, and which one depends on what your needs are. Note that all of this is implemented in Asterisk 1.4

If you:
  1. use asterisk's comedian mail to listen to voice messages or you don't mind using the CDR userfield column for this, then use Method 2
  2. Otherwise use Method 1


Both of these methods assume that you are storing both cdr and voicemail in ODBC. Note that cdr_mysql.so is probably not an option because it's incompatible with voicemail_odbc, so you'll have to switch to using odbc for both and remove all mysql modules.

Prerequisites

  • Asterisk (I am using 1.4).
  • Using odbc for storage of CDR and voicemail. Modules:
    *CLI> module show like odbc
    Module                         Description                              Use Count 
    res_odbc.so                    ODBC Resource                            0         
    app_voicemail_odbc.so          Comedian Mail (Voicemail System) with OD 0         
    cdr_odbc.so                    ODBC CDR Backend                         0         
    func_odbc.so                   ODBC lookups                             0         
    4 modules loaded
    
  • You care about metrics, data warehousing, or just enough to match a row in CDR to the row in voicemessages

Method 1

  1. add a new column to voicemessages table to track the unique id from CDR so you can connect them when you need to:
    alter table voicemessages add column akom_uniqueid varchar(128) ;
  2. Add to func_odbc.conf:
    ; Sets the unique id of the source call after the voice mail is left
    ; this assumes that in the millisecond after voicemail is left and before this is
    ; invoked, no new voicemails show up (it's not entirely guaranteed, but
    ; error is unlikely except for very high volume call centers
    ;
    ; ARG1 - mailbox (or to read last num for)
    ; Write :
    ; ARG1 - msgid to update (for write only)
    ; VAL1 - uniqueid to set it to  (write only)
    [VMLEFT]
    dsn=mysql2
    read=SELECT id FROM voicemessages where akom_uniqueid IS NULL and mailboxuser = '${SQL_ESC(${ARG1})}' AND DIR like '%${ARG1}/INBOX' ORDER BY id DESC LIMIT 1
    write=UPDATE voicemessages SET akom_uniqueid = '${SQL_ESC(${VAL1})}' WHERE id = '${ARG1}' LIMIT 1 
    
  3. In your extensions.ael (or an include)... (you are using AEL by now, right?):
    macro leavevoicemail(mailbox, password) {
        Voicemail(${mailbox}|${password});
    
    UPDATE_ROW:
        switch(${VMSTATUS}) {
            case SUCCESS:
                Noop(Voicemail Left Successfully, proceeding to update row);
                Set(msgid=${ODBC_VMLEFT(${mailbox})});
                Noop(Updating message id for vm: ${msgid});
                Set(ODBC_VMLEFT(${msgid})=${UNIQUEID}); //Method 1
                //Set(CDR(userfield)=${msgid}); //Method 2
                break;
        };
        catch h {
            goto s|UPDATE_ROW;
        };
    };

Method 2

Basically do the same stuff as method 2, but omit adding a column and switch the lines in extensions labelled Method1 for Method2

Both

Now all that's left is to use the new macro you have whenever clients leave voicemail.
    7 => {
       &leavevoicemail(1234,4242);
    };


What this does

The moment the voicemail recording is finished, it gets written to the DB. The dialplan continues whether the user hung up or pressed # to end recording. The first thing we run is a query to get the id of the latest unread voicemail for this user. Clearly this is not 100% accurate in case there are hundreds of voicemails per second being left for this user, but I'm assuming that this is not a problem for the majority of installations. Once we have the unique id of the voicemail, we can leisurely record it somewhere. Method 1 records it on the voicemail row. Method 2 sets it in the CDR userfield.

Pros and Cons

Method 2 is more reliable but you are using up your userfield column. Clearly you can do some trickery to get around that, but we won't concern ourselves with that for now. So why is Method 1 unreliable? If you ever listen to your messages via comedian mail, and a message becomes "OLD", the row will get updated in DB. Except it's not an UPDATE - it's an INSERT and a DELETE, and it loses your extra column's data (Asterisk knows nothing about your creative additions to its schema).

Is there a Method 3?

Certainly.
If you can't or won't use either of the above, you can store the data elsewhere entirely. For example - you can create a mapping table with two columns (uniqueid, vmid) - and insert into this instead. Once you have this you can do a join of 3 tables, or if you don't like joins, you can have an offline process that migrates your data.

Trivia

Why didn't I just add a column to CDR and record the voicemail id there? After all, the CDR row never gets updated after the call, eliminating the problem with Method1! Simple. The CDR row doesn't exist until the dialplan finishes, so there is nowhere to record it yet.

0 Trackbacks

  1. No Trackbacks

1 Comments

Display comments as (Linear | Threaded)
  1. i want a EPABX system

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.