asterisk users - Nov 2007 - hostname in MySQL CDR records

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?

Thanks...

On Wednesday 31 October 2007 19:49:24 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?
If you set systemname in asterisk.conf, that prefix will become part of the
uniqueid field.  You'll probably need to widen that field, though.

-- 
Tilghman

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