TIPS Generate daily report for inbound calls

Bill Clarke · Dec 26, 2018

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?

wardmundy · Dec 26, 2018

Here's the script that we run from /etc/crontab every day:

Code:

#!/bin/bash
mysql -u root -ppassw0rd asteriskcdrdb -e 'SELECT calldate, clid FROM cdr WHERE DATE(calldate) = SUBDATE(CURDATE(), 1) AND did <> ""'

Bill Clarke · Dec 27, 2018

wardmundy said:
Here's the script that we run from /etc/crontab every day:

Code:

#!/bin/bash mysql -u root -ppassw0rd asteriskcdrdb -e 'SELECT calldate, clid FROM cdr WHERE DATE(calldate) = SUBDATE(CURDATE(), 1) AND did <> ""'

Ok what fields do I need to alter each day

Bill Clarke · Dec 27, 2018

wardmundy said:
Here's the script that we run from /etc/crontab every day:

Code:

#!/bin/bash mysql -u root -ppassw0rd asteriskcdrdb -e 'SELECT calldate, clid FROM cdr WHERE DATE(calldate) = SUBDATE(CURDATE(), 1) AND did <> ""'

Can I get an example pls.

wardmundy · Dec 27, 2018

Don't alter anything. It will retrieve calls from the previous day.

dmcgrandle · May 13, 2019

Here is what I got working. I created a new file 'daily-report' in /root like this:

Code:

#!/bin/bash
(
echo "From: PBX <[email protected]>";
echo "To: Admin <[email protected]>";
echo "Subject: Yesterday's Call Report";
echo "MIME-Version: 1.0";
echo "Content-Type: text/html";
echo "Content-Disposition: inline";
echo "<html>"
echo "<body>"
echo "<pre style=\"font: monospace\">"
mysql -u root -ppassw0rd asteriskcdrdb -t -e 'SELECT calldate AS Timestamp, clid AS CallerID, did as DID, dst AS Destination, disposition AS Disposition, duration AS Duration FROM cdr WHERE DATE(calldate) = SUBDATE(CURDATE(), 1) AND did <> ""'
echo "</pre>";
echo "</body>";
echo "</html>";
) | sendmail -t

Then I added the following to /etc/crontab to make it run every night at 12:40am:

Code:

40 0 * * * root /root/daily-report > /dev/null 2>&1

I hope this helps someone. Note - it will send a blank email if there were no calls the previous day.

krzykat · May 24, 2019

wardmundy said:
Don't alter anything. It will retrieve calls from the previous day.

Ward, this is great, but it will not give accurate results for number of inbound calls if you don't have a 1-1 relationship to where the call goes. For example, if you have a ring group, it will show multiple records for one inbound call. Is there a way to limit this to one record per uniqueid so that a more representative answer is given?

krzykat · May 24, 2019

I think this will get it:

mysql -u root -ppassw0rd asteriskcdrdb -e 'SELECT calldate, clid FROM cdr WHERE DATE(calldate) = SUBDATE(CURDATE(), 1) AND did <> "" GROUP BY uniqueid'

TIPS Generate daily report for inbound calls

Bill Clarke

New Member

wardmundy

Nerd Uno

Bill Clarke

New Member

Bill Clarke

New Member

wardmundy

Nerd Uno

dmcgrandle

New Member

krzykat

Telecom Strategist

krzykat

Telecom Strategist

Members online

Latest Posts

Forum statistics