Continuing the discussion from
Scheduled cdr reports with cronjob:
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)
This stopped working after migrating from pbx in a flash. I’m getting the following error:
ERROR 1045 (28000) at line 1: Access denied for user ‘freepbxuser’@‘localhost’ (using password: YES)
The username and password was root and passw0rd…
So I checked /etc/freepbx.conf and /etc/asterisk/cdr_mysql.conf
the username freepbx user and password as noted in those files.
However, I’m still getting the error.
/bin/rm -rf /tmp/results.txt&&/usr/bin/mysql -ufreepbxuser -pT00secret -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' > /cdrreports/cdr-$filename.csv;chown root:root /cdrreports/cdr-$filename.csv
You will always get that error when the ACL in mysql doesn’t match the -u and -p and the client does not have a matching IP address to the mysql account.
If all the above are corrected, it should work, if you are not relying totally on CEL instead of CDR now.
I think this will essentially do the same thing without fighting bashisms and it uses freepbx internals so you don’t have to worry about “what’s the password”
Thanks guys, much appreciated! I’ll give the php scrip a shot!
This wouldn’t have worked, since there are varying days between months and what is “the last 30 days” (eg going back 30 days from the 14th of September would give a different start date compared with running the scripts on the 14th of March, since there are 31 days in August and 28-29 days in February).
However, the cdr people that I upload to decided that they wanted the csv files uploaded daily, so I just changed the query
SELECT * FROM asteriskcdrdb.cdr WHERE date(calldate) >= date(now()-interval 30 day);";
SELECT * FROM asteriskcdrdb.cdr WHERE date(calldate) >= date(now()-interval 1 day);";
I then ran the php file from a bash script that changes the name of the csv file to the daymonthyear.csv format. I run that bash script at midnight with a cronjob.
Does this script only work on freepbx 12? Or for that matter only on the freepbx distro?
I tried running it on freepbx11 running on the piaf distro, and got the error PHP Fatal error: Class ‘FreePBX’ not found in /root/run_csv.php on line 7
I posed on their forum, but I didn’t get any responses.
The database stuff uses PDO which is 12+. With 13 on the verge of stable there is no good reasom to be using 2.11 still