TUTORIAL Email Incoming CDR Report Daily

smccloud

Member
Joined
Jun 17, 2013
Messages
131
Reaction score
14
I'm not sure if I'm just missing the configuration for it, but is it possible to send incoming CDR reports out daily via email? I am going to start doing computer repair as a side business and using my PBX for the contact number and I would just like to get a report of all calls for the previous day (or week).
 

Boolah

Guru
Joined
Nov 16, 2007
Messages
331
Reaction score
20
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:

Code:
mysql -u root -ppassw0rd asteriskcdrdb -e 'SELECT calldate, clid FROM cdr WHERE DATE(calldate) = SUBDATE(CURDATE(), 1) AND did <> ""' | mail -s "Yesterday's Calls" [email protected]

Replace the [email protected] with your actual email address.
 

smccloud

Member
Joined
Jun 17, 2013
Messages
131
Reaction score
14
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:

Code:
mysql -u root -ppassw0rd asteriskcdrdb -e 'SELECT calldate, clid FROM cdr WHERE DATE(calldate) = SUBDATE(CURDATE(), 1) AND did <> ""' | mail -s "Yesterday's Calls" [email protected]

Replace the [email protected] with your actual email address.

Yep, that will work fine for me. Thanks for the help. In theory I could output it to a CSV file but meh....Sadly, I had to call myself to test it :(

Next project is transition from a dedicated machine for my PBX to a VirtualBox VM running on my home server.
 

phinphan

Active Member
Joined
Oct 19, 2007
Messages
641
Reaction score
130
Thanks for the code, but I get an error saying: Error 1054 (42S22) at line1: unknown column 'did' in 'where clause'

any suggestions for fixing it. This would be a very handy utility.
 

smccloud

Member
Joined
Jun 17, 2013
Messages
131
Reaction score
14
Thanks for the code, but I get an error saying: Error 1054 (42S22) at line1: unknown column 'did' in 'where clause'

any suggestions for fixing it. This would be a very handy utility.
Try removing "and where did <> ""'"
 

Boolah

Guru
Joined
Nov 16, 2007
Messages
331
Reaction score
20
You need to be running FreePBX v2.10 or higher for it to work correctly. With previous versions of FreePBX it's not so easy to determine which are calls coming into a DID vs. calls coming into an extension.

If you're running a version of FreePBX older than v2.10, you could remove the AND did <> "" portion as smccloud suggests, but then you'll get all incoming calls to all extensions on your system (including internal extension to extension calls).
 

phinphan

Active Member
Joined
Oct 19, 2007
Messages
641
Reaction score
130
Thanks for the suggestions. It works fine and I dont have a lot of internal calls. Using the teletrigger app it gives me a convenient click to dial in an email.
 

bobh080850

Member
Joined
Aug 5, 2013
Messages
103
Reaction score
2
Just a little more on this question please. If I wanted to receive an email cdr activity report for Mondays from 8:00 am - 8:30 am and Wednesdays from 2:00 pm - 2:30 pm (to track cnam and cids for conference call atendees), what would the script look like? I would also need these emailed at the conclusion of each conference call.
 

bobh080850

Member
Joined
Aug 5, 2013
Messages
103
Reaction score
2
I have now had a chance to try this and am unable to get either the original script or the suggested amended script adjusting the original script to show any results. I have cut and pasted the cited scripts and replaced the [email protected] with my actual email address. That results in the error message: "Access denied for user 'root'@'localhost' (using password: YES)". So I replaced "-ppassw0rd" with "-p (my password)" but still can't get it to run. Here is what I have tried to run:

mysql -u root -p (my password) asteriskcdrdb -e ' SELECT calldate, clid FROM cdr WHERE DATE(calldate) = SUBDATE(CURDATE(), 1) AND((dayofweek(calldate) = '1' AND TIME(calldate) > '11:25' AND TIME(calldate) < '12.30') OR (dayofweek(calldate) = '3' AND TIME(calldate) > '19:15' AND TIME(calldate) < '20:45')) AND did <> ""' | mail -s "Meetings Attendance" [email protected]

I get an email but it is blank. What am I doing wrong?
 

Boolah

Guru
Joined
Nov 16, 2007
Messages
331
Reaction score
20
There's a type-o for one of your times, which is listed as 12.30 and should be 12:30.

With that change, the SQL should work. Make sure there is no space when specifying the MySQL password (i.e. you should specify your password like so: -pmypassword and not -p mypassword). Also note, that the password you need to enter is the MySQL password, not your Linux password, FreePBX password or any other password. This, by default, should be passw0rd and should NOT be changed as it can cause significant issues. To make sure that this is indeed your MySQL password, type this from the linux command prompt:

Code:
mysql -ppassw0rd

If you get a mysql> prompt, everything should work fine. If you get the error you mentioned above (
Access denied for user 'root'@'localhost' (using password: YES)), you've changed your MySQL password and will likely have more issues with your installation.

If you're still having trouble, run the same command as above from the linux command prompt, but leave off the | mail -s "Meetings Attendance" [email protected] portion and let us know what errors are generated.
 

bobh080850

Member
Joined
Aug 5, 2013
Messages
103
Reaction score
2
I did not realize that Asterisk had it's own password and I did not knowingly change it. So I changed the script back to "-ppass0rd" and corrected the time to "12:30 and it still sent bland emails to my email account. When I removed "| mail -s "Meetings Attendance" [email protected]" I got the following message: "ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)"

So just for reference sake here was the script after removing email info: mysql -u root -ppassw0rd asteriskcdrdb -e ' SELECT calldate, clid FROM cdr WHERE DATE(calldate) = SUBDATE(CURDATE(), 1) AND((dayofweek(calldate) = '1' AND TIME(calldate) > '11:25' AND TIME(calldate) < '12:30') OR (dayofweek(calldate) = '3' AND TIME(calldate) > '19:15' AND TIME(calldate) < '20:45')) AND did <> ""'

I also tried "mysql -ppassw0rd" and got "Access denied for user 'root'@'localhost' (using password: YES)"

So apparently somewhere I changed my mysql password. Is there any solution for this?
 

randy7376

Defnyddiwr Gweithredol
Joined
Sep 29, 2010
Messages
864
Reaction score
144
bobh080850

Reference: http://dev.mysql.com/doc/refman/5.0/en/resetting-permissions.html

C.5.4.1.3. Resetting the Root Password: Generic Instructions

The preceding sections provide password-resetting instructions for Windows and Unix systems. Alternatively, on any platform, you can set the new password using the mysql client (but this approach is less secure):
  1. Stop mysqld and restart it with the --skip-grant-tables option. This enables anyone to connect without a password and with all privileges. Because this is insecure, you might want to use --skip-grant-tables in conjunction with --skip-networking to prevent remote clients from connecting.
  2. Connect to the mysqld server with this command:
    shell> mysql
  3. Issue the following statements in the mysql client. Replace the password with the password that you want to use.
    mysql> UPDATE mysql.user SET Password=PASSWORD('MyNewPass')
    -> WHERE User='root';
    mysql> FLUSH PRIVILEGES;

    The FLUSH statement tells the server to reload the grant tables into memory so that it notices the password change.
You should now be able to connect to the MySQL server as root using the new password. Stop the server, then restart it normally (without the --skip-grant-tables and --skip-networkingoptions).
 

visionlogic

Guru? Nope
Joined
Oct 11, 2009
Messages
117
Reaction score
33
Although it doesn't apply in this case, if you happen to be running the Incredible Pi then the correct default root mysql password is "raspberry", i.e. the password switch is -praspberry
 
Joined
May 23, 2013
Messages
223
Reaction score
28
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:

Code:
mysql -u root -ppassw0rd asteriskcdrdb -e 'SELECT calldate, clid FROM cdr WHERE DATE(calldate) = SUBDATE(CURDATE(), 1) AND did <> ""' | mail -s "Yesterday's Calls" [email protected]

Replace the [email protected] with your actual email address.

So I'll admit I'm not good with mysql stuff, how would I make this list calls into a specific DID? I want to generate call logs by incoming DID to be emailed out, really all I want is the total calls but this would be okay if I can make it by DID. Thanks
 
Joined
May 23, 2013
Messages
223
Reaction score
28
Tablet so struggling...

From: AND did <> ""
To: AND did LIKE "xxxxxxxxxx"

Where x's are the did as FreePBX sees it. To get total number of calls Google 'mysql count'

That fixes the call logs, Thanks! Now to figure out the count command to get a total :)
 

michael14094

New Member
Joined
Sep 1, 2016
Messages
2
Reaction score
0
I know this is an older post, and I was able to get this working with no problem. However, I would like to do the same for outbound calls. I changed the field FROM in the script to TO but that did not work. Any help would be greatly appreciated.
 

Bill Clarke

New Member
Joined
Dec 26, 2018
Messages
4
Reaction score
0
Hopefully I can get a reply on this thread as it is an old post. I need to set up a daily email including all inbound calls, there destination or extension transferred to, and if the call was answered or went to VM. Is this possible?
 

Members online

Forum statistics

Threads
25,779
Messages
167,505
Members
19,199
Latest member
leocipriano
Get 3CX - Absolutely Free!

Link up your team and customers Phone System Live Chat Video Conferencing

Hosted or Self-managed. Up to 10 users free forever. No credit card. Try risk free.

3CX
A 3CX Account with that email already exists. You will be redirected to the Customer Portal to sign in or reset your password if you've forgotten it.
Top