[asterisk-users] hostname in MySQL CDR records

[Steve Edwards]

On Wed, 31 Oct 2007, Jim Gottlieb wrote:

> I would like to send the CDR records from all our machines around the
> world to a single database.  But I need the hostname included with each
> record for monitoring purposes.
>
> Is there a better way than using the userfield and adding
> SetCDRUserfield for every call to set the userfield to the name of the
> host?

Personally, I think the "userfield" is a hack. I prefer to add properly 
named columns to the cdrs table using cdr_addon_mysql. It makes everything 
so much more obvious -- especially when you don't have to cram several 
values into the singularly obtuse userfield.

I prefer to "retrieve" the CDRs rather than "send" them. This way, you 
only have a single script to retrieve all of the remote CDRs and the 
script is simpler since you don't have to "poll" a directory and try to 
figure out if the remote transfer has finished so you don't process a 
partial file. It also makes it easier to handle a remote host that is 
temporarily unavailable.

I retrieve the CDRs from remote hosts using a script that looks like this 
snippet:

# for each host
         for     HOST in ${HOST_LIST}
         do

# mark the records to be exported
                 mysql\
                         ${USER_AUTH}\
                         --database=mumble\
                         --execute="update cdrs set disposition = 'EXPORTING'"\
                         --host=${HOST}

# dump the cdrs
                 mysqldump\
                         ${USER_AUTH}\
                         --host=${HOST}\
                         --no-create-info\
                         --skip-opt\
                         --where="disposition = 'EXPORTING'"\
                         mumble\
                         cdrs\
                         >/tmp/${HOST}.sql

# load the cdrs into our database
                 mysql\
                         ${USER_AUTH}\
                         --database=mumble\
                         --host=localhost\
                         </tmp/${HOST}.sql

# delete the exported records
                 mysql ${USER_AUTH}\
                         --database=mumble\
                         --execute="delete from cdrs where disposition = 'EXPORTING'"\
                         --host=${HOST}

# end of hosts loop
         done

I am "misusing" the existing disposition column, but I never use it in my 
application anyway :)

Thanks in advance,
------------------------------------------------------------------------
Steve Edwards      sedwards at sedwards.com      Voice: +1-760-468-3867 PST
Newline                                             Fax: +1-760-731-3000