Schedule and email specific cdr report query

[wzkds]

Hi all,

Has anyone had any success generating and email a specific cdr query with a cronjob? For instance, I would schedule it to run every Monday to pull the past 7 days with the following query:
between 0800 and 1700

Destination of a specific mailbox (eg vmu9000)
duration between 3 and 600 seconds
and disposition answered

I’ve tried with all of the cdr posts that I have been able to find on the forum and haven’t been able to successfully obtain this specific query.

[dicko]

try this script

#!/bin/env sh
rm -f /tmp/mysql
mysql asteriskcdrdb -e "SELECT * FROM cdr WHERE dst LIKE 'vmu9000' AND  calldate  >  DATE(NOW() - INTERVAL 7 DAY) and HOUR(calldate) BETWEEN 8 AND 17 INTO OUTFILE '/tmp/mysql' FIELDS TERMINATED BY ',' ENCLOSED BY '\"' LINES TERMINATED BY '\n'"
cat /tmp/mysql |mail -s 'Last Weeks Workday Calls' [email protected]

crontab line

0 7 * * 1 script.sh

If it gets to vmu9000 it must be answered, minimum call length is better handled by comedian mail, you probably should know about vmails longer than 5 minutes.