Call Detail Report Summary

I find the CDR in FreePBX to be noisy, especially when there are ring groups and the like. As such, finding what I’m interested in from the CDR can be more of a chore than it should be. The CDR in Switchvox is pretty sweet, but, alas, I don’t have one. I think the FreePBX team is working on a more elegant solution; not sure if it will be included in the distro or be a commercial addon. In any event, I wrote up the little script below. It pulls data from asteriskcdrdb.cdr and shows only the info in which I’m interested. Use as you wish and let me know if you have any thoughts for improvement.

if [[ -z $1 ]]
cat << EOF

A simple script to display, or optionally email, a brief summary of a given day's CDR.

usage: $0 [datestring] <email>


  [datestring]  Required. e.g. 2013-12-17 or \$(date --date="1 day ago" +\%Y-\%m-\%d) for a nightly cronjob
  <email>       Optional. Enter an email address to produce an email instead of displaying to standard out. Useful for a nightly cronjob


function output() {
  echo "SELECT t.calldate,t.clid,t.dst,t.duration \
  FROM ( \
    SELECT * FROM asteriskcdrdb.cdr \
    WHERE calldate LIKE '$1%' AND lastdata NOT LIKE '%auto-confir%' AND dst!='pbdirectory' AND channel LIKE 'SIP%' AND dst NOT LIKE '*%' \
    ORDER BY duration DESC \
  ) \
  t \
  GROUP BY t.calldate \G" \
  | mysql \
  | sed 's/vmu/Voicemail - /;s/dst: s/dst: hang/;s/dst/destination/;s/clid/source/;s/hang/hang up/'

if [[ -z $2 ]]
  output $1
  output $1 | mail -s "Call Detail Report" $2