Remote CDR

Remote CDR stopped working.

I got remote cdr working after following these instructions I found in this forum: wiki.freepbx.org/plugins/servlet/mobile?contentId=66256961#content/view/66256961

It worked great until I had to touch something ref eth0 and the firewall.

From mysql I can read/write to my remote cdr mysql table but freepbx can’t.

Please help !!!

Newbie

We would really have to know what you “touched”

Elaborate - we’re not mind readers.

There are a thousand ways this can go wrong, and every one of them has a slightly different solution. No one is going to start down a list telling you “Try this” a hundred times.

I started from scratch by following the link in my first post. FreePBX is not able to write to my remote db hosted at godaddy. If i log in via putty, i can from the mysql command line, connect to my remote db and read and write to my remote table.

in FreePBX under Advanced Settings Details, I changed all 3 values to Yes and under “Remote CDR Database” I entered all the correct information about my remote mysql server,

First i was pointing to a different mysql server but after responding to a FreePBX alert, ref to firewall, i lost connection. Im new to FreePBX and cant find that firewall section. I remember it had only 1 item and made ref to eth0 and being private, local, internet and a couple more. I selected the recommended value and i believe that’s when remote cdr stopped working. I then switch to my godaddy mysql server, which is where i really needed, and from mysql command line it works but FreePBX is not writing to my remote cdr.

Hope this explains the situation a bit more.

Thanks,

Newbie

There is nothing in the GUI that wiill reasonably help you,

You need to set a new mysq/whatever backend user that has the correct ACL’s to connect to and from the remote server, this is pertonent to sny cdr backend, odbc/postgres/maria /mssql

thanks dicko. Remember i mentioned that via putty I log in, open mysql console, and connect to my remote mysql db, open my cdr Table, read the table and insert a new record. Which tells me i’m using the right credentials and the mysql database hosted at GoDaddy allows remote users.

Is there a .conf file i have to modify besides /etc/odbc.ini ?

Thanks again.

You will need to verify that you can connect as the asterisk user to do the same and at a basic level:-

mysql --host=your.remote.host --user=freepbxuser --password=9557b1ae67a0afee93f33118f111008c asteriskcdrdb -e ‘show databases’

(Use your password generally from bash

grep password /etc/asterisk/res_odbc_additional.conf|cut -d '>' -f2

)

your firewall needs to pass tcp:3306 both ways between you and rour remote server.

that is the command i run once in via putty and i’m able to access the remote server, read and write. Which also confirms 3306 is open:

mysql --host=173.xxx.xxx.xxx --user=cellphxxxxxx --password=mycxxxxxx cellphxxxxx -e ‘show databases’

ran your grep command and returns my pw alright.

mysql --host=173.xxx.xxx.xxx --user=cellphxxxxxx --password=mycxxxxxx cellphxxxxx -e ‘show databases’

returns that you have a properly structred cdr table?

mysql --host=173.xxx.xxx.xxx --user=cellphxxxxxx --password=mycxxxxxx cellphxxxxx asteriskcdrdb -e ‘describe cdr;select * from cdr limit 6’

and the user/password are the ones that the asterisk user will be using

?

Eureka !! Victory is ours !! (People should say Eureka more often)

Thanks dicko. The problem was that I have two cdr tables in that remote db. One is called cdr and the other cdr_asterisk. The second i created based on the asterisk’s cdr Table structure. The first was created years ago when I used to write telephony software using Dialogic cards. FreePBX opend cdr by default and it has a different structure.

Now, I make a call, FreePBX answers and when i hangup it creates and empty record in cdr table at the remote server.

[[email protected] ~]# mysql --host=173.201.xxx.xxx --user=cellphxxxxx --password=CPxxx cellphxxx -e 'describe cdr;select * from cdr’
±------------±----------------------±-----±----±--------------------±---------------+
| Field | Type | Null | Key | Default | Extra |
±------------±----------------------±-----±----±--------------------±---------------+
| recid | mediumint(8) unsigned | NO | PRI | NULL | auto_increment |
| calldate | datetime | NO | MUL | 0000-00-00 00:00:00 | |
| clid | varchar(80) | NO | | | |
| src | varchar(80) | NO | MUL | | |
| dst | varchar(80) | NO | MUL | | |
| dcontext | varchar(80) | NO | | | |
| channel | varchar(80) | NO | | | |
| dstchannel | varchar(80) | NO | | | |
| lastapp | varchar(80) | NO | | | |
| lastdata | varchar(80) | NO | | | |
| duration | int(11) | NO | | 0 | |
| billsec | int(11) | NO | | 0 | |
| disposition | varchar(45) | NO | MUL | | |
| amaflags | int(11) | NO | | 0 | |
| accountcode | varchar(20) | NO | MUL | | |
| uniqueid | varchar(32) | NO | MUL | | |
| userfield | varchar(255) | NO | | | |
±------------±----------------------±-----±----±--------------------±---------------+
±------±--------------------±-------------------------------±-----------±----±---------±------------------------±-----------±-----------±--------------------±---------±--------±------------±---------±------------±--------------±----------+
| recid | calldate | clid | src | dst | dcontext | channel | dstchannel | lastapp | lastdata | duration | billsec | disposition | amaflags | accountcode | uniqueid | userfield |
±------±--------------------±-------------------------------±-----------±----±---------±------------------------±-----------±-----------±--------------------±---------±--------±------------±---------±------------±--------------±----------+
| 1 | 2017-08-16 21:21:02 | “0107:7862177142” <7862177142> | 7862177143 | s | ivr-3 | SIP/vitelityin-00000002 | | BackGround | custom/soda_welcome | 7 | 7 | ANSWERED | 3 | | 1502932862.5 | |
| 2 | 2017-08-15 19:43:34 | “0107:5616672861” <5616672861> | 5616672861 | s | ivr-3 | SIP/vitelityin-0000000c | | BackGround | custom/soda_welcome | 37 | 36 | ANSWERED | 3 | | 1502840614.24 | |
| 3 | 2017-08-15 12:45:17 | “0107:7862177142” <7862177142> | 7862177143 | s | ivr-3 | SIP/vitelityin-0000000b | | BackGround | custom/soda_welcome | 5 | 5 | ANSWERED | 3 | | 1502815517.23 | |
| 4 | 0000-00-00 00:00:00 | | | | | | q | | | 0 | 0 | | 0 | | | |
| 5 | 0000-00-00 00:00:00 | | | | | | zzz | | | 0 | 0 | | 0 | | | |
| 6 | 0000-00-00 00:00:00 | | | | | | aaa | | | 0 | 0 | | 0 | | | |
| 7 | 0000-00-00 00:00:00 | | | | | | | | | 0 | 0 | | 0 | | | |
| 8 | 0000-00-00 00:00:00 | | | | | | | | | 0 | 0 | | 0 | | | |
±------±--------------------±-------------------------------±-----------±----±---------±------------------------±-----------±-----------±--------------------±---------±--------±------------±---------±------------±--------------±----------+
[[email protected] ~]#

The last two records where just created by FreePBX after i renamed my two cdr tables.

recs 4 - 6 i added via mysql console at FreePBX using the same credentials i entered in odbc.ini and FreePBX’s Advanced settings.

rec 1-3 were added by FP to the original test db before the firewall change i made after August 15

Working now !!

Thank you dicko for all your help.

Newbie

Well, that would be a “duh. . .” no eureka needed just fix the duh

Lol, you are correct !!!

Thanks for all your help.

Newbie