CDR report every 5/10 minutes

FreePBX Development
1

Hello,

My goal is to generate a CDR report (possibly as a CSV file or similar) of all calls every 5 or 10 minutes.

I have been reading two old topic (which I can’t link since I am a new user) but honestly I know nothing about mySQL…

Is possible to do what I would like to achieve? I have been looking on google and stackoverflow so I think yes, but if someone can help me to correct my script it would be useful

/usr/bin/mysql -u ASTERISKCDRUSER -p ASTERISKCDRPASSWORD -e "my query'" > out.csv

Do you think it would work?

Also, I am not sure about how the query works if you have any resources about it please send me a link and I will try to help me out

This is the original script

/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]
2

Yes

Here is a link to outputting a MySQL query to a csv:

Here is a link about scheduling a job for a MySQL query:

MySQL Overview:
https://dev.mysql.com/doc/mysql-tutorial-excerpt/8.0/en/tutorial.html

3

yes the first link is where I took the export CSV.

So it looks like that now the problem is just with the query. Can someone say me how to filter the database with only the last 10 minutes calls? I think this should be enough, right?

4 
mysql -sb  asteriskcdrdb -e 'SELECT * FROM cdr  WHERE calldate >= NOW() - INTERVAL 10 MINUTE'|tr '\t' ','

would give you a comma separated list of calls made in the 10 minutes before now()

2 Likes
5

Asterisk can generate CDR CSV files for you directly and do it in parallel with the database so you can still use CDR Reports in FreePBX.

Look at asterisk/cdr.conf.sample at master · asterisk/asterisk · GitHub for how to add it to the config.

2 Likes
6

I have been looking in the asterisk documentation but the only file they seems to use is the /var/log/asterisk/cdr-csv/master.csv, but I tought that using freepbx was a better idea, if you think I should instead use the master.csv let me know

7

I guess this is the perfect solution!
Also using the command --skip-column-names I should be able to remove not needed columns :grinning_face_with_smiling_eyes:

8

the -bs removes header names and columnar ‘decorations’ , replace the * with a csv list of columns that you want, ( a list of which from :- mysql asteriskcdrdb -e 'describe cdr')

1 Like
9

It’s the same records, once written to the FreePBX CDR database and simultaneously written to the CSV file. Just giving you options to consider.

1 Like
10

thanks for your valuable help!

11

Unfortunately I don’t have to necessary insight and knowledge to decide which solution (taking the data from FreePBX vs from Asterisk) would work better.

From a my simple point of view, since asterisk is under the hood of freePBX, maybe it would be better to use the master.csv, also I think that string manipulation would be faster than extraction ofthe data from mysql. But anyway I would like to hear the perspective of more experienced people

If you have any suggestion please tell me.

12

FreePBX is not involved in writing the cdr/cel records, it is purely Asterisk. Grouping records by date is built into mysql, csv records will need additional ‘function’ , python, perl, java etc. all have libraries to do that, but given the baked in nature of mysql used by FPBX to do it’s reporting, I would stick with mysql it’s ‘string manipulation’ is also fast and comprehensive.

1 Like
13

actually I wasn’t able to find the -bs flag in the documentation of mysql
Anyway I saw that it is veryeasy to filter for col name,a s you said, example: SELECT calldate, ..., sequence FROM ....

14

They are actually two ‘flags’ (better referred to as ‘options’) , -b is a synonym for --batch and -s for --silent

https://dev.mysql.com/doc/refman/8.0/en/mysql-command-options.html

These will give you a tsv output appropriate for ‘batch’ processing, the tr '\t' ',' will then convert that to a csv as you asked for.

It is way less convoluted than using mysql’s ‘export’ that can only write to /var/lib/mysql or /tmp for security reasons.

15

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.