If you use FreePBX you might have wished that you could get a daily report of call activity e-mailed to you, if only to remind you to check for suspicious activity, so that you don’t get hacked and not know about it for weeks because you fell out of the habit of checking the log. It turns out that there is a fairly easy way to do it, as explained in a post by user Boolah in this thread in the PBX in a Flash forum:

EDIT (December, 2017): The method shown at that link and below doesn’t work at all in newer versions of FreePBX. It seems like every new version requires some tweaking. This is the line that works in FreePBX 14:

/usr/bin/mysql -u freepbxuser -ppassword -e "select calldate AS Timestamp, clid AS CallerID, did AS DID, dst AS Destination, disposition AS Disposition, duration AS Duration, userfield AS Userfield from cdr where calldate > date_sub(now(), interval 24 HOUR)" -H asteriskcdrdb | mail -a "From: PBX <asterisk@youraddress.com>" -a "Content-Type: text/html" -s "Custom Call Report" you@youremail.com

The above is all one long line. You must replace the above values:

-u freepbxuser -ppassword (that’s not a typo – there really is NO SPACE after the -p) – replace freepbxuser and password with the correct values for YOUR system. You will usually find these in one of two places. You can look in /etc/amportal.conf and look for the variables AMPDBUSER and AMPDBPASS — these will usually be near the bottom of the file in newer installs, in a “— CATEGORY: Bootstrapped or Legacy Settings —” section, but they can be anywhere in the file.

Another place they may be found is in the file /etc/freepbx.conf — in that file, look for lines similar to:

$amp_conf[‘AMPDBUSER’] = ‘freepbxuser’;
$amp_conf[‘AMPDBPASS’] = ‘password’;

Those will give you the values to use to replace freepbxuser and password

-a “From: PBX <asterisk@youraddress.com>” – replace asterisk@youraddress.com with a valid outgoing email address for your system. If you have a Dynamic DNS address, you may be able to use that after the @ in the email address. It mostly depends on how you send outgoing email from your system. Note that using “root” before the @ sign may not work, particularly if you are routing mail through an ISP’s mail servers – one major ISP now appears to silently drop any email that appears to originate from root@anything, so use “asterisk” or “pbx” or something else other than “root”.

-s “Custom Call Report” you@youremail.com – replace you@youremail.com with the address you want the reports emailed to.

What follows is the rest of the original article. The mysql line shown there probably will not work, unless perhaps you are running a very old version of FreePBX (end of edit).

This is very basic, but should get you the info you need. Add it as a cron to run once a day and it will email the date/time of the call along with the CNAM and CID for all incoming calls for the previous day:

mysql -u root -ppassw0rd asteriskcdrdb -e 'SELECT calldate, clid FROM cdr WHERE DATE(calldate) = SUBDATE(CURDATE(), 1) AND did <> ""' | mail -s "Yesterday's Calls" you@youremail.com

Replace the you@youremail.com with your actual email address.

When copying the above, note that it’s all one single line, even though it wraps in this blog post, and also beware that WordPress likes to change single and double quotation marks into the “prettified” versions, so make sure you change those characters back to the plain old ASCII versions or it won’t work correctly. Or, you could just visit the thread mentioned above and copy the line from there.