CronJob to send automatic custom CDR email

Remove the part “calldate AS Timestamp,”

add to your where clause “AND src NOT LIKE ‘Anonymous’”

change the SELECT to “SELECT DISTINCT”

Change your mail command to

mail -s "$(echo -e "Custom Call Report\nContent-Type: text/plain\nContent-Disposition: attachment; filename=report.txt")" [email protected]

Should I need to remove the “-H” when changing the mail command? I get a bunch of HTML code on the TXT file… :slight_smile:

Yes, just remove the -H.

mysql --help -H, --html Produce HTML output.

1 Like

Awesome! Now, one last thing…

Is it possible to add the date of the report, so in this case, yesterday’s date, to the subject and/or to the name of the attached .txt file? For example, subject Custom Call Report_2016_02_06, attachment Report_2016_02_06.txt.

Thank you in advance for the help! Really appreciate all the help…

Change the part after the | (the mail command) to add the date,

... | DATE=$(date "+%Y_%m_%d") mail -s "$(echo -e "$DATE Custom Call Report\nContent-Disposition: attachment; filename=report_$DATE.txt")"

To the preserve the easy to read “asci box” around the text output add -t (I believe that is undocumented in the man file :wink: ) as a mysql argument, not as pretty as -H but works over every mail client.

for the -s argument check out

echo “$(date --date=‘yesterday’ +‘Call Report_%Y_%m_%d’)”

for convenience emailing I suggest you install mutt , it has an -a (attach file) argument , hence redirect the output of the sql query into report.txt and then:-

echo "attachment for report of $(date --date=‘yesterday’ +’%Y/%m/%d’) "| mutt -s “$(date --date=‘yesterday’ +‘Call Report_%Y_%m_%d’)” -a report.txt – [email protected]

(edit:- as you can see, there are many ways to skin a cat in linux :wink: if you just want a CSV file perhaps for other use, look into adding to the mysql query

INTO OUTFILE ‘/tmp/report.txt’ FIELDS TERMINATED BY ‘,’ OPTIONALLY ENCLOSED BY ‘"’

mysql by default can only OUTFILE into it’s home folder /var/lib/mysql and /tmp, don’t forget to delete the file after use

)

@gforcevoip

That is an an extraordinarily restrictive report you are asking for, would you mind explaining exactly what you are trying to do? There might be a better solution, is it about some sort of security concern?

Hi dicko…

Thanks for the all the help. So, we run a marketing service for a Lawyer Buffet company, and we have the option for the callers to select to not be contacted anymore. Whenever they want, callers can “opt-out”. This report is needed to get those numbers that want to be “opted-out”, and make sure we delete them from our system so they don’t get contacted any more… So, it is a security concern, as well as a means of making sure we keep our word to our callers. :slight_smile:

I’m not sure what a “Lawyer Buffet” company is, but, are these data gathered by outbound unsolicited commercial calls or unsolicited inbound calls, and how is the “opt-out” choice made by the caller/callee?

Asterisk maintains a “blacklist” in it’s database that you should populate and add a look-up into it for your outbound calling process

The data is gathered from inbound calls into our system. If interested in the service, users would press 1 which would take them to a queue, if not, they would press 2, which would take them to an audio recording thanking them for their time and reassuring them that they will be removed from our system within 72 hours. When they press 2, in the backend, it’s sending them to an extension (DESTINATION) that plays that message. That’s why I needed the report for a specific destination. Hopefully that makes sense…

It does make sense, I would suggest adding a custom context for the donotcall extension that adds the calling number to the blacklist database and then add a call to app-blacklist-check in your [macro-dialout-trunk-predial-hook] context.

No emails or humans needed :wink:

1 Like

Woah :open_mouth:

Nice. I’ll investigate on how to do that. Thanks @dicko :slight_smile:

Also, thanks @billsimon and everyone who helped with this. Really appreciate all the help.

I hope it’s not too late or that I’m hijacking the thread, but is there a way to get the full report? I have this working as originally posted, but would like to get the full output including the info of who answered it, how long it was, etc., as you do if you run a manual report in the web gui. Also, to clarify, I’m not interested in one destination. I did figure out that removing the AND dst like ‘DESTINATION’ would list all calls.

I have this report sending me data as designed, but it’s not formatted for HTML. Its output is the HTML coding but not showing me an actual table. What am I missing?

I have used below mentioned command:

/usr/bin/mysql -u USERNAME -pPASSWORD -e “select calldate AS Timestamp, clid AS CallerID from cdr where calldate > date_sub(now(), interval 24 HOUR) AND dst like ‘PAGE8001’” -H asteriskcdrdb

And getting the report in the form of HTML Code. Where did go wrong?

You shouldactually read the thread, I posted here

To the preserve the easy to read “asci box” around the text output add -t (I believe that is undocumented in the man file :wink: ) as a mysql argument, not as pretty as -H but works over every mail client.

for the -s argument check out

echo “$(date --date=‘yesterday’ +‘Call Report_%Y_%m_%d’)”

for convenience emailing I suggest you install mutt , it has an -a (attach file) argument , hence redirect the output of the sql query into report.txt and then:-

echo "attachment for report of $(date --date=‘yesterday’ +’%Y/%m/%d’) "| mutt -s “$(date --date=‘yesterday’ +‘Call Report_%Y_%m_%d’)” -a report.txt – [[email protected]](mailto:[email protected])

(edit:- as you can see, there are many ways to skin a cat in linux :wink: if you just want a CSV file perhaps for other use, look into adding to the mysql query

INTO OUTFILE ‘/tmp/report.txt’ FIELDS TERMINATED BY ‘,’ OPTIONALLY ENCLOSED BY ‘"’

mysql by default can only OUTFILE into it’s home folder /var/lib/mysql and /tmp, don’t forget to delete the file after use

)

identify the bit that says ‘html’ , -H (or --html) vs. -t (or --table)

Now No luck with previously ran command
I am not getting any output now
Can you please guide me with the complete command to download the cdr report for a particular date at 11am for callerID “911” and destination can be anything. Then rest can be followed as per your above instructions but the basic command I am not getting.

Please guide

First step get all the caklls fir 911
Second step, add acondition for anly calls after a particular date
Third step add condition to calls before a later date.

Try a root cron job something like

0 11 * * 2   mysql asteriskcdrdb  -H -e "SELECT * FROM cdr WHERE  calldate> CURDATE()- INTERVAL 1 WEEK AND  dst='911'"|mutt -e "set content_type=text/html" -s "911 calls for the last week" [email protected]

You will need mutt installed and mail gets sent even if no calls made.

I am having mail command in the system and not mutt. Can not install mutt as I have to update the system for the same and I do not want to do that.
Can we achieve it using mail or mailx command