Remote CDR

biesys · August 21, 2017, 3:28am

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

dicko · August 21, 2017, 5:23am

We would really have to know what you “touched”

cynjut · August 22, 2017, 3:19pm

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.

biesys · August 30, 2017, 3:54am

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

dicko · August 30, 2017, 5:51am

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

biesys · August 31, 2017, 7:39pm

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.

dicko · August 31, 2017, 9:14pm

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.

biesys · August 31, 2017, 9:38pm

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.

dicko · August 31, 2017, 11:27pm

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

?

biesys · September 1, 2017, 7:04pm

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.

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

biesys · September 1, 2017, 7:16pm

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

biesys · September 1, 2017, 7:33pm

Working now !!

Thank you dicko for all your help.

Newbie

dicko · September 1, 2017, 10:41pm

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

biesys · October 4, 2017, 12:24pm

Lol, you are correct !!!

Thanks for all your help.

Newbie