CronJob to send automatic custom CDR email

Hi all…

I would like to setup a cronjob that would run a command to take a daily custom CDR report and send it to an email address.

Here’s what I am trying to achieve:

CDR Report of calls only to a specific “Destination”, and to only send the “Caller ID” or “Source” part of the report.
Then get that data and send it in en email with a custom subject.

Is this even possible? Thank you in advance for any help you can provide. Thanks! :smile:

Here’s one you might like:

/usr/bin/mysql -u ASTERISKCDRUSER -pASTERISKCDRPASSWORD -e "select calldate AS Timestamp, clid AS CallerID from cdr where calldate > date_sub(now(), interval 24 HOUR) AND dst like 'DESTINATION'" -H asteriskcdrdb | mail -s "$(echo -e "Custom Call Report\nContent-Type: text/html")" [email protected]

Set the cron job for 23:59 daily and you will get the custom report e-mailed to you describing the previous 24 hours.

There’s some mild trickery in the mail command to make it send an HTML-formatted e-mail. The -H argument to the mysql command tells it to output and HTML table.

Replace the user and password in the command line with the correct username and password for the database, and replace the DESTINATION with your target.

3 Likes

?

maybe -D asteriskcdrdb. :wink:

1 Like

No, see my earlier comment. -H tells mysql to output in HTML mode. Then the last argument is just the database name. Try it! (The HTML table output feature is cool)

So it does !! My apologies :slight_smile: I RTFM ‘man mysql’ again and missed that before.

You are awesome Bill!

I did modify it one bit… instead of “clid”, I put “src”. This gives me only the ten digit number, where as using “clid” gave me an output of Called ID plus the ten digit number.

Now, is it possible to do the following before it sends it?

  1. Remove the TimeStamp from the table?

  2. Remove any entries that appear as “Anonymous”

  3. Remove duplicates

  4. Attach the output as a .txt in the email?

Also, I added “-r [email protected]” after mail and before the “-s SUBJECT”, and it works! It sends the email as coming from the email I specified, but I can’t figure out a way to add a name to the from address. Any ideas?

Thank you for any input and direction you can give. Really appreciate the help. Thank you in advance for any help you provide.

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?