asterisk users - Mar 2010 - How to add custom CDR fields to MySQL

Hi all,

I've been trying to add a custom mysql field to my CDR's, but I must
be doing something wrong.

I am using asterisk 1.4 and asterisk 1.6, in extensions.conf I add:

exten => h,1,Set(CDR(q931)=${HANGUPCAUSE})

This extension is executed, I can see it in the asterisk console.

I have added a new column in my MySQL database called q931. However,
the new field does not show up in my database or in the Master.csv
file.

Any help would be greatly appreciated.

Regards,

Alex

Hi,

i think that you should modify the cdr_addon_mysql module, otherwise you can
add it in the userfield.

2010/3/11 Alejandro Recarey <alexrecarey at gmail.com>
> Hi all,
>
> I've been trying to add a custom mysql field to my CDR's, but I
must
> be doing something wrong.
>
> I am using asterisk 1.4 and asterisk 1.6, in extensions.conf I add:
>
> exten => h,1,Set(CDR(q931)=${HANGUPCAUSE})
>
> This extension is executed, I can see it in the asterisk console.
>
> I have added a new column in my MySQL database called q931. However,
> the new field does not show up in my database or in the Master.csv
> file.
>
> Any help would be greatly appreciated.
>
> Regards,
>
> Alex
>
> --
> _____________________________________________________________________
> -- Bandwidth and Colocation Provided by http://www.api-digital.com --
> New to Asterisk? Join us for a live introductory webinar every Thurs:
>               http://www.asterisk.org/hello
>
> asterisk-users mailing list
> To UNSUBSCRIBE or update options visit:
>   http://lists.digium.com/mailman/listinfo/asterisk-users
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL:
http://lists.digium.com/pipermail/asterisk-users/attachments/20100312/ff099c52/attachment.htm

Hello Alex,

I'm struggling with the same problem and, not wanting to modify the CDR 
backend, I just put in a workaround in the form of a MySQL trigger. 
I'll describe what I did in case it helps someone, though I'm very 
inexperienced at making compound procedures in MySQL.

In my extensions.conf, I can do something like this:

   exten => s,1,Set(CDR(userfield)="flavor=cherry|color=maroon"

The result is that my CDR(userfield) is a pipe-delimited list of 
key=value pairs.  In my MySQL 5.0.45 database, I have altered my cdr 
table with a column called 'flavor' and another called 'color'. 
I have
also created a trigger thus:

DELIMITER //

CREATE TRIGGER cdr_insert BEFORE INSERT ON cdr
FOR EACH ROW
   BEGIN
     SET @numidx = LENGTH(NEW.userfield)
          - LENGTH(REPLACE(NEW.userfield, '|', '')) + 1;
     SET @idx = 0;
     WHILE @idx + 1 <= @numidx DO
       SET @idx = @idx + 1;
       SET @param = SUBSTRING_INDEX(
                   SUBSTRING_INDEX(NEW.userfield, '|', @idx),
'|', -1);
       SET @pos = LOCATE('=', @param);
       IF @pos > 0 THEN
         SET @key = SUBSTRING(@param, 1, @pos - 1);
         SET @value = SUBSTRING(@param, @pos + 1);

         CASE @key
           WHEN 'flavor' THEN SET NEW.flavor = @value;
           WHEN 'color' THEN SET NEW.color = @value;
         END CASE;

       END IF;
     END WHILE;
     SET NEW.userfield = '';
   END
//

DELIMITER ;

You can omit SET NEW.userfield = '' if you want to retain the userfield 
to prevent data loss.  Expand the CASE statement as necessary to 
enumerate all the fields you want to be able to specify via userfield, 
and make sure you've created the appropriate columns beforehand.  You 
can even specify things like 'dst' and 'dcontext', which you
wouldn't
normally be able to control.  The loop silently ignores elements of the 
pipe-delimited list that it does not recognize.  In particular, if the 
value of CDR(userfield) begins with a pipe, that doesn't create a problem.

So far, it appears to work.

Cheers,
Robert

 >     Hi all,
 >
 >     I've been trying to add a custom mysql field to my CDR's, but
I
 >     must be doing something wrong.
 >
 >     I am using asterisk 1.4 and asterisk 1.6, in extensions.conf I
 >     add:
 >
 >     exten => h,1,Set(CDR(q931)=${HANGUPCAUSE})
 >
 >     This extension is executed, I can see it in the asterisk console.
 >
 >     I have added a new column in my MySQL database called q931.
 >     However,
 >     the new field does not show up in my database or in the Master.csv
 >     file.
 >
 >     Any help would be greatly appreciated.
 >     Regards,
 >
 >     Alex