Akom's Tech Ruminations

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.