Log parser for extracting Call Detail Record (CDR)

Once due to some issue in my Asterisk service, my PBX stop inserting CDRs in the database. So I used the following bash script for extracting outbound calls.

The script first searches for the custom keyword `CDR(userfield)=Outbound` andretrievee its channel id and then again parse the log file for that channel id and find out the required CDRs column out of it.

INPUT

filename to parse.

OUTPUT

1. Destination Number
2. Source Number
3. Call Start Time
4. Asterisk Unique ID
5. Call End Time
6. Duration of a Call

#!/bin/bash

FILENAME=messages-20170707
LINE_COUNT=`grep "CDR(userfield)=Outbound" $FILENAME -A 2 | grep D10- | awk '{print $4}' | colrm 1 13 | tr -d [] | wc -l`
echo " Total Lines: $LINE_COUNT"

for ((i=1; i<=$LINE_COUNT; i++))
do
 #echo "hamid";
 CHANNEL_ID=`grep "CDR(userfield)=Outbound" $FILENAME -A 2 | grep D10- | awk '{print $4}' | colrm 1 13 | tr -d [] | sed -n ${i}p`
 #echo "Channel ID $i : $CHANNEL_ID"
 DST=`grep $CHANNEL_ID $FILENAME | grep D10- | awk '{print $8}'| colrm 14 | tr -d [`
 START_TIME=`grep $CHANNEL_ID $FILENAME | grep D10- | awk '{print $1,$2,$3}' | tr -d [] | xargs -i date -d "{}" "+%F %T"`
 SRC=`grep $CHANNEL_ID $FILENAME | grep D10- | awk '{print $9}' | colrm 1 9 | cut -c -4`
 #echo "SRC ---------------------- $SRC";
 UNIQUE_ID=`grep $CHANNEL_ID $FILENAME | grep D10- | awk '{print $10}' | colrm 1 13 | colrm 1 12 | cut -c -22`
 END_TIME=`grep $CHANNEL_ID $FILENAME | grep "exited non-zero" | awk '{print $1,$2,$3}' | tr -d [] |  xargs -i date -d "{}" "+%F %T"`
 START_UNIX=`grep $CHANNEL_ID $FILENAME | grep D10- | awk '{print $1,$2,$3}' | tr -d [] | xargs -i date -d "{}" +%s`
 END_UNIX=`grep $CHANNEL_ID $FILENAME | grep "exited non-zero" | awk '{print $1,$2,$3}' | tr -d [] | xargs -i date -d "{}" +%s`
 if [[ $SRC == Fwd* ]]
 then
  echo "Channel ID $i [$CHANNEL_ID]     :,,,,"
 else
  #echo "END $END_UNIX"
  #echo "START $START_UNIX"
  DURATION=`expr $END_UNIX - $START_UNIX`
  echo "Channel ID $i [$CHANNEL_ID]     :'$START_TIME','$END_TIME','$SRC','$DST','$UNIQUE_ID',$DURATION";
  echo -e "\"$START_TIME\",\"$END_TIME\",\"$SRC\",\"$DST\",\"$UNIQUE_ID\",\"$DURATION\"\r"
 fi
done

The task which consumed my lot of time is to find out the difference between two dates given in string format. I hope this script save your lot of time. Enjoy.

For inbound call CDR

#!/bin/bash

FILENAME=messages-20171213 # Log File to Parse
LINE_COUNT=`grep "D10-" UCIDs-20171213` # Asterisk Unique IDs

echo "\"datetime\",\"src\",\"dst\",\"dcontext\",\"channel\",\"dstchannel\",\"lastapp\",\"lastdata\",\"enddate\",\"duration\",\"billsec\",\"disposition\",\"amaflags\",\"accountcode\",\"userfeild\",\"uniqueid\",\"FWCallID\"";

for i in $LINE_COUNT
do
 #echo "hamid";
 CHANNEL_ID=`grep "1@ACD_ROUTE:6" $FILENAME -A 2 | grep $i | awk '{print $4}' | colrm 1 13 | tr -d []`
 DST=1
 SRC=`grep $CHANNEL_ID $FILENAME | grep OrigCallerid | awk '{print $10}'| colrm 1 16 | colrm 11`
 UNIQUE_ID=$i
 DURATION=0
 START_TIME=`grep $CHANNEL_ID $FILENAME| grep "GPCallID" | awk '{print $1,$2,$3}' | tr -d [] | xargs -i date -d "{}" "+%F %T"`
 END_TIME='0000-00-00 00:00:00'
 GPCallID=`grep $CHANNEL_ID $FILENAME | grep "GPCallID" | awk '{print $10}' | colrm 1 12 | cut -d '@' -f 1 | tr -d "\")"`
 CHANNEL=`grep $CHANNEL_ID $FILENAME | grep "GPCallID" | awk '{print $9}'| colrm 1 5 | cut -d '"' -f 1`
 DCHANNEL=`grep $CHANNEL_ID $FILENAME | grep "is ringing" | awk '{print $7}'`
 LASTDATE=`grep $CHANNEL_ID $FILENAME | grep "Queue" | awk '{print $10}' | tr -d "\")"`
 FWDCallID=`grep $CHANNEL_ID $FILENAME | grep "FWDCallID" | awk '{print $10}' | colrm 1 13 | cut -d "\"" -f 1`
 #START_UNIX=`grep $CHANNEL_ID $FILENAME | grep D10- | awk '{print $1,$2,$3}' | tr -d [] | xargs -i date -d "{}" +%s`
 #END_UNIX=`grep $CHANNEL_ID $FILENAME | grep "exited non-zero" | awk '{print $1,$2,$3}' | tr -d [] | xargs -i date -d "{}" +%s`
 #if [[ $SRC == Fwd* ]]
 #then
 # echo "Channel ID $i [$CHANNEL_ID]     :,,,,"
 #else
  #echo "END $END_UNIX"
  #echo "START $START_UNIX"
  #DURATION=`expr $END_UNIX - $START_UNIX`
  #echo "Channel ID $i [$CHANNEL_ID]     :'$START_TIME','$END_TIME','$SRC','$DST','$UNIQUE_ID',$DURATION";
  #echo "Channel ID $i [$CHANNEL_ID]     :'$START_TIME','$END_TIME','$SRC','$DST','$UNIQUE_ID',$DURATION, $GPCallID";
  echo "\"$START_TIME\",\"$SRC\",\"1\",\"ACD_ROUTE\",\"$CHANNEL\",\"$DCHANNEL\",\"queue\",\"$LASTDATE\",\"\",\"\",\"\",\"ANSWERED\",\"3\",\"newCust\",\"$GPCallID\",\"$UNIQUE_ID\",\"$FWDCallID\"";
 #fi
done