Scheduled cdr reports with cronjob

General Help
1

Hello all,

I’m trying to create csv files and email them via a cronjob. I’ve posted at the PIAF forum, but didn’t get any responses (http://pbxinaflash.com/community/index.php?threads/scheduled-cdr-reports-with-cronjob.13839/).

I’ve read up on http://pbxinaflash.com/community/index.php?threads/email-incoming-cdr-report-daily.12907/ but that sends the cdr info in the body of the email, not as a csv.

I’ve also read up on http://www.transnexus.com/index.php/how-to-push-cdr-files-from-asterisk-to-sdreporter
but loading Master-*.csv isn’t a solution because I need to collect all calls from the 14th of the previous month to the 13th of the current month and nothing more.

Any ideas?

Thanks guys.

2

You could run from cron:-

/bin/rm -rf /tmp/results.txt&&/usr/bin/mysql -u youruser -pyourpassword -D asteriskcdrdb -e “SELECT * INTO OUTFILE ‘/tmp/results.txt’ FIELDS TERMINATED BY ‘,’ OPTIONALLY ENCLOSED BY '”’ LINES TERMINATED BY ‘\n’ FROM cdr WHERE calldate BETWEEN ‘$(date -d “$(date) -1 month” +%Y-%m-%d)’ AND ‘$(date +%Y-%m-%d)’"

the day after you want your stuff.

(notes:-

mysql can only write where it is allowed to, neither /tmp/ nor /var/lib/mysql/ is a great choice but it’s a good place to start . . . .

cron should run under a limited shell so fully qualify anything called by it

)

3

( to be careful, ‘yourpassword’ is in clear text so ‘youruser’ should be a very restricted mysql user :wink: )

4

hmm, hard to escape the @ char. Version two:-

/bin/rm -rf /tmp/results.txt&&/usr/bin/mysql -uroot -yourusername -D asteriskcdrdb -e “SELECT * INTO OUTFILE ‘/tmp/results.txt’ FIELDS TERMINATED BY ‘,’ OPTIONALLY ENCLOSED BY '”’ LINES TERMINATED BY ‘\n’ FROM cdr WHERE calldate BETWEEN ‘$(date -d “$(date +%Y-%m-%d) -1 month” +%Y-%m-%d)’ AND ‘$(date +%Y-%m-%d)’"&& cat /tmp/results.txt |sed ‘s/\"//g’ > /your/wanted/file.csv;chown whatever:whatever /your/wanted/file.csv

(run the cron job as root or someone who has access to all these bits)

5

Thank you so much for your help with this. Do you need to scrub your third response?