<html><body style="word-wrap: break-word; -webkit-nbsp-mode: space; -webkit-line-break: after-white-space; ">Hi,<div><br></div><div>I managed to write a really short perl script to insert cdr records into mysql. I wanted to share it with the group...</div><div><br></div><div>Just call this from cron at whatever frequency you want to update your records.</div><div><br></div><div>DISCLAIMER: THIS IS A "QUICK AND DIRTY" SCRIPT. IT HAS NO BUILT IN ERROR CHECKING AND WAS JUST DESIGNED TO WORK FOR MY BASIC SETUP. YOU MAY NEED TO HEAVILY MODIFY IT TO WORK FOR YOU...</div><div><br></div><div><br></div><div>-Noah</div><div><br></div><div>-----------------------------------------------------------</div><div><div style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; font: normal normal normal 11px/normal Monaco; ">#!/usr/local/bin/perl</div><div style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; font: normal normal normal 11px/normal Monaco; "><span style="color: #a020f0">use </span>DBI;</div><div style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; font: normal normal normal 11px/normal Monaco; "><span style="color: #a020f0">use </span>Text::ParseWords;</div><div style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; font: normal normal normal 11px/normal Monaco; min-height: 15px; "><br></div><div style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; font: normal normal normal 11px/normal Monaco; ">#connect to database and define path to your log files</div><div style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; font: normal normal normal 11px/normal Monaco; color: rgb(0, 0, 255); "><span style="color: #000000">$</span><span style="color: #a000f0">dbh</span><span style="color: #000000"> </span><span style="color: #b22200">= </span><span style="color: #000000">DBI</span><span style="color: #b22200">-></span><span style="color: #a000f0">connect</span><span style="color: #b22200">( </span>'DBI:mysql:freeswitch'<span style="color: #b22200">, </span>'username'<span style="color: #b22200">, </span>'password' <span style="color: #b22200">) or </span><span style="color: #a020f0">die </span>"connect error"<span style="color: #000000">;</span></div><div style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; font: normal normal normal 11px/normal Monaco; color: rgb(0, 0, 255); "><span style="color: #000000">$</span><span style="color: #a000f0">LOG_DIR</span><span style="color: #000000"> </span><span style="color: #b22200">= </span>"/usr/local/freeswitch/log/cdr-csv"<span style="color: #000000">;</span></div><div style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; font: normal normal normal 11px/normal Monaco; min-height: 15px; "><br></div><div style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; font: normal normal normal 11px/normal Monaco; min-height: 15px; "><br></div><div style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; font: normal normal normal 11px/normal Monaco; color: rgb(178, 0, 34); ">#Make Freeswitch rotate the log files</div><div style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; font: normal normal normal 11px/normal Monaco; color: rgb(0, 0, 255); "><span style="color: #a000f0">system</span><span style="color: #b22200">(</span>"killall -HUP freeswitch"<span style="color: #000000">);</span></div><div style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; font: normal normal normal 11px/normal Monaco; min-height: 15px; "><br></div><div style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; font: normal normal normal 11px/normal Monaco; color: rgb(178, 0, 34); ">#prepare the insert statement - You may have to change this depending on the log file definintion in your freeswitch config.</div><div style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; font: normal normal normal 11px/normal Monaco; color: rgb(0, 0, 255); "><span style="color: #000000">$</span><span style="color: #a000f0">insert_sql</span><span style="color: #000000"> </span><span style="color: #b22200">= </span><span style="color: #000000">$</span><span style="color: #a000f0">dbh</span><span style="color: #b22200">-></span><span style="color: #000000">prepare</span><span style="color: #b22200">(</span>"insert into cdr (caller_id_name, caller_id_number, destination_number, context, start_timestamp, answer_timestamp, end_timestamp, duration, billsec, hangup_cause, uuid, bleg_uuid, accountcode) values (?,?,?,?,?,?,?,?,?,?,?,?,?)"<span style="color: #b22200">);</span></div><div style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; font: normal normal normal 11px/normal Monaco; min-height: 15px; "><br></div><div style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; font: normal normal normal 11px/normal Monaco; color: rgb(178, 0, 34); ">#get a list of all the Master files with a datestamp on the end. (These are created when log files rotated above)</div><div style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; font: normal normal normal 11px/normal Monaco; ">@<span style="color: #a000f0">log_files</span> <span style="color: #b22200">= </span><$LOG_DIR/Master.csv.*><span style="color: #b22200">; </span></div><div style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; font: normal normal normal 11px/normal Monaco; min-height: 15px; "><br></div><div style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; font: normal normal normal 11px/normal Monaco; color: rgb(178, 0, 34); ">#Loop through the files and insert into the table;</div><div style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; font: normal normal normal 11px/normal Monaco; color: rgb(160, 0, 240); "><span style="color: #a020f0">foreach </span><span style="color: #000000">$</span>file<span style="color: #000000"> </span><span style="color: #b22200">(</span><span style="color: #000000">@</span>log_files<span style="color: #b22200">){</span></div><div style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; font: normal normal normal 11px/normal Monaco; color: rgb(0, 0, 255); "><span style="color: #b22200"><span class="Apple-tab-span" style="white-space:pre"> </span></span><span style="color: #a000f0">print </span>"$file\n"<span style="color: #b22200">;</span></div><div style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; font: normal normal normal 11px/normal Monaco; color: rgb(0, 0, 255); "><span style="color: #b22200"> <span class="Apple-tab-span" style="white-space:pre"> </span></span><span style="color: #a000f0">open</span><span style="color: #b22200">(</span><span style="color: #000000">IN</span><span style="color: #b22200">, </span><span style="color: #000000">$</span><span style="color: #a000f0">file</span><span style="color: #b22200">) or </span><span style="color: #a020f0">die </span>"cant open $file\n"<span style="color: #b22200">;</span></div><div style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; font: normal normal normal 11px/normal Monaco; color: rgb(160, 32, 240); "><span style="color: #b22200"> <span class="Apple-tab-span" style="white-space:pre"> </span></span>while <span style="color: #b22200">(</span><span style="color: #000000"><IN></span><span style="color: #b22200">){</span></div><div style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; font: normal normal normal 11px/normal Monaco; color: rgb(160, 0, 240); "><span style="color: #b22200"> <span class="Apple-tab-span" style="white-space:pre"> </span>(</span><span style="color: #000000">$</span>caller_id_name<span style="color: #b22200">, </span><span style="color: #000000">$</span>caller_id_number<span style="color: #b22200">, </span><span style="color: #000000">$</span>destination_number<span style="color: #b22200">, </span><span style="color: #000000">$</span>context<span style="color: #b22200">, </span><span style="color: #000000">$</span>start_stamp<span style="color: #b22200">, </span><span style="color: #000000">$</span>answer_stamp<span style="color: #b22200">, </span><span style="color: #000000">$</span>end_stamp<span style="color: #b22200">, </span><span style="color: #000000">$</span>duration<span style="color: #b22200">, </span><span style="color: #000000">$</span>billsec<span style="color: #b22200">, </span><span style="color: #000000">$</span>hangup_cause<span style="color: #b22200">, </span><span style="color: #000000">$</span>uuid<span style="color: #b22200">, </span><span style="color: #000000">$</span>bleg_uuid<span style="color: #b22200">, </span><span style="color: #000000">$</span>accountcode<span style="color: #b22200">, </span><span style="color: #000000">$</span>read_codec<span style="color: #b22200">, </span><span style="color: #000000">$</span>write_codec<span style="color: #b22200">) = </span><span style="color: #000000">quotewords</span><span style="color: #b22200">(</span><span style="color: #0000ff">","</span><span style="color: #b22200">, </span><span style="color: #a02000">0</span><span style="color: #b22200">, </span><span style="color: #000000">$</span>_<span style="color: #b22200">);;</span></div><div style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; font: normal normal normal 11px/normal Monaco; color: rgb(160, 0, 240); "><span style="color: #b22200"> <span class="Apple-tab-span" style="white-space:pre"> </span></span><span style="color: #000000">$</span>insert_sql<span style="color: #b22200">-></span><span style="color: #000000">execute</span><span style="color: #b22200">(</span><span style="color: #000000">$</span>caller_id_name<span style="color: #b22200">, </span><span style="color: #000000">$</span>caller_id_number<span style="color: #b22200">, </span><span style="color: #000000">$</span>destination_number<span style="color: #b22200">, </span><span style="color: #000000">$</span>context<span style="color: #b22200">, </span><span style="color: #000000">$</span>start_stamp<span style="color: #b22200">, </span><span style="color: #000000">$</span>answer_stamp<span style="color: #b22200">, </span><span style="color: #000000">$</span>end_stamp<span style="color: #b22200">, </span><span style="color: #000000">$</span>duration<span style="color: #b22200">, </span><span style="color: #000000">$</span>billsec<span style="color: #b22200">, </span><span style="color: #000000">$</span>hangup_cause<span style="color: #b22200">, </span><span style="color: #000000">$</span>uuid<span style="color: #b22200">, </span><span style="color: #000000">$</span>bleg_uuid<span style="color: #b22200">, </span><span style="color: #000000">$</span>accountcode<span style="color: #b22200">);<span class="Apple-tab-span" style="white-space:pre"> </span></span></div><div style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; font: normal normal normal 11px/normal Monaco; color: rgb(178, 34, 0); "> <span class="Apple-tab-span" style="white-space:pre"> </span>}</div><div style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; font: normal normal normal 11px/normal Monaco; color: rgb(178, 0, 34); "><span style="color: #b22200"> <span class="Apple-tab-span" style="white-space:pre"> </span></span>#Delete the log file since we've finished with it.</div><div style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; font: normal normal normal 11px/normal Monaco; color: rgb(160, 0, 240); "><span style="color: #000000"> <span class="Apple-tab-span" style="white-space:pre"> </span></span>close<span style="color: #b22200">(</span><span style="color: #000000">IN</span><span style="color: #b22200">);</span></div><div style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; font: normal normal normal 11px/normal Monaco; color: rgb(160, 0, 240); "><span style="color: #b22200"> <span class="Apple-tab-span" style="white-space:pre"> </span></span>unlink<span style="color: #b22200">(</span><span style="color: #000000">$</span>file<span style="color: #b22200">);</span></div><div style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; font: normal normal normal 11px/normal Monaco; color: rgb(178, 34, 0); ">}</div><div style="margin-top: 0px; margin-right: 0px; margin-bottom: 0px; margin-left: 0px; font: normal normal normal 11px/normal Monaco; min-height: 15px; "><br></div></div><div><br></div></body></html>