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
I used “mail -E” instead of “mutt -e” still no luck
After adding from Email ID, I could send the mail but its empty no reports attached or generated in “/var/lib/mysql” as well as in “/tmp” folder
mysql asteriskcdrdb -H -e “SELECT * FROM cdr WHERE calldate> CURDATE()- INTERVAL 1 WEEK AND dst=‘911’”|mail -E “set content_type=text/html” -r [email protected] -s “911 calls for the last week” [email protected]
What am doing wrong?
Not being able to install mutt bexause you ‘don’t want to’ means you will have to find another way.
Why i suggesred mutt, Is because it is a known solution, ‘mail’ is an unknown factor it might be from exim it might be from postfix it might be from sendmail, , some versions allow you to specify the mime format and if so it might be by some other switch and or syntax same goes for mailx. So I guess you will hsve to get out the man pages and other FMs for your particular machine 
that mysql query WILL generate an output , but only what you filter, leave out the “AND dst=‘911’” and you will get all the calls in the 168 hours previous to exactly when you run the query if you have any, identify the calls you want and write your own WHERE clause to suit, (leave out the date filter, you get them all 
). mysql sends its output to the ‘stdout’ file and here we are piping it through mutt, you can use anything else that will encapsulate the html produced with mime in the email. Mutt WILL work with the -e switch and html but leave out the -H and you would get a tab seperated values report, pipe that through tr '\t' ',' and you would have a comma seperated values report. Both would pass through any mail program without any format switches
I want a report to fetch me CDR report for specific time not for last 168 Hours. Every Monday’s 11am’s CDR report for CallerID 911. How can we do that
a cron job run at 0 11 * * 1 does that at 11:00 am every 1’st day of the week which is Monday, so yes you do want the calls from the previous 168 hours from that point in time.
There are several fields that might fit your CallerID need, by name by number by sender by recipient and by format ‘DESCRIBE cdr;’ and ‘SELECT * from cdr LIMIT 10;’ are mysql queries that should help you identify which field you want to filter on
Respectfully, I think you should spend a little time with basic mysql and bash tutiorials before any more over needy posts generally the answers to your questions are in the first few pages.
I tried following commands to fetch the CDR report for 8th Feb 2021 from 11am to 11:01am for destination “911” using below mentioned commands with success:
mysql -u root -pPASSWORD
SHOW DATABASES; USE asteriskcdrdb;
SHOW TABLES;
DESC cdr;
SELECT * FROM cdr;
SELECT * FROM cdr WHERE calldate > curdate() - INTERVAL 1 WEEK
SELECT * FROM cdr WHERE calldate BETWEEN ‘2021-02-08 11:00:0’ AND ‘2021-02-08 11:01:00’;
but the commands to filter the data for destination “911” as mentioned below is not working:
SELECT * FROM cdr WHERE calldate BETWEEN ‘2021-02-08 11:00:00’ AND ‘2021-02-08 11:01:00’ AND dst=‘911’;
**SELECT * FROM cdr WHERE calldate > CURDATE()- INTERVAL 1 WEEK AND dst=911’;
Please suggest where am I going wrong
Moreover the below command also not producing the report:
/usr/bin/mysql -u root -pPASSWrOD asteriskcdrdb -t -e “SELECT * FROM cdr WHERE calldate BETWEEN ‘2021-02-08 11:00:00’ AND ‘2021-02-08 11:01:00’”
Please guide
From a shell, post
mysql asteriskcdrdb -e "SELECT * FROM cdr WHERE calldate BETWEEN ‘2021-02-08 11:00:00’ AND ‘2021-02-08 11:01:00’;"|egrep -i "9|dst"
any cdr’s containing the string 9 made in that minute will be printed,. Rinse and repeat with 91 then 911
How’s the RTFM’ing going?
I am using below command to generate CDR Report for specific time and destination:
mysql asteriskcdrdb -e “SELECT * FROM cdr WHERE calldate BETWEEN ‘2021-02-08 11:00:00’ AND ‘2021-02-08 11:01:00’” | egrep -i “211|src” | egrep -i -v -E “9017” | cut -f1,2,3,4,8,10,12 > /mnt/final-cdr-report.txt
The issue is the Duration Field show time as “48” and I am not sure whether it is seconds or minutes. In the GUI it shows “0.48”.
Then why the deviation here.
Thank you for clarification.
This topic was automatically closed 31 days after the last reply. New replies are no longer allowed.