One of the shortcomings of freePBX is that there is no useful & complete call list available (GUI or email). Today I played around with a script from here
…yet, it is still not very useful.
my script (/root/cdr-email.sh):
/usr/bin/mysql -u freepbxuser -pMYPASSWORD -e “SELECT calldate AS Timestamp, clid AS Caller, disposition AS Status, dst AS Destination, duration AS Duration, src AS Source, did AS Target_inbound,lastapp AS LastApp,dcontext AS Context, dstchannel AS DestChannel, userfield AS outgoing, uniqueid AS ID from cdr where calldate > date_sub(now(), interval 24 HOUR) ORDER BY Timestamp DESC, ID DESC, Status ASC” -H asteriskcdrdb | mail -s “$(echo -e “Custom Call Report\nContent-Type: text/html”)” email@example.com
in etc/crontab I added
0 0 * * * root /root/cdr-email.sh > /dev/null 2>&1
it is supposed to send me an email at 00:00h every day
with a content like this
I have a ringgroup with several phones, therefore CDR always shows many lines just for one call. In the CDR GUI the phone which answers the call is even NOT listed on top…I could fix this in the email…it is shown on top now. The newest calls are listed on top. Yet, ideally all lines should be grey except for the last line (on top) for every ID group (call). Otherwise it is very hard to see, where the call went. Problem is you cannot change the html formatting, once it is generated by mysql. One would have to collect the output in a text file and do the html formatting in a second step. Theoretically, one could create wonderful call lists, with answered lines in green etc…
anyway…it took me already hours to accomplish black&white emails…maybe someone else with more advanced skills can improve the script…
EDIT: I use the Outbound-CNAM-module for reverse lookup of outgoing calls (for Cisco phones). If you dont use it, remove the “Userfield AS outgoing” in the script. In the image above (just one call), an incoming call rings all extensions of a ringgroup and is picked-up by a mobile phone (forward misc-destination).