Automated email with simplified call list - much better than the CDR Reports module...just kidding :)

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):

#!/bin/bash
/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 protected]

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… :wink:

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).

1 Like

A more advanced version of the script, which doesn’t show all lines of extensions of a ring group in the CDR, just one for non-answered calls and two for answered calls (bottom no-answer, top answered)

cdr-email.sh

#!/bin/bash
/usr/bin/mysql -u freepbxuser -pPASSWORD -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) GROUP BY ID, Status ORDER BY Timestamp DESC, ID DESC, Status ASC" -H asteriskcdrdb | mail -s "$(echo -e "Custom Call Report\nContent-Type: text/html")" [email protected]

Thanks so much for this. We were just mulling over how to automate sending out our custom CDR queries. This will fit the bill nicely.

As mentioned above, I use the Outbound-CNAM-module. If you dont use it, remove the “Userfield AS outgoing” in the last version of the script. I have 10 phones in a ring group and I hate it, when the CDR (GUI) shows me 10 lines (9 no-answer, 1 answered) for every incoming call…so I played around with the syntax. I have now only 1 line for non-answered calls and two lines for answered calls. You might have to adapt it to your needs :slight_smile:

1 Like

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.