chemicalx Posted May 8, 2009 Report Share Posted May 8, 2009 Hi All, Anybody made this work? I have emailed support with regards to this and I haven't received any answer yet from them. There maybe people here who have successfully integrated their CDR with an SQL package. Send some help this way please. Basically, I would want the CDR to write onto a table whether on the same or different machine. I have read the possibility of this on other threads in this forum but have not really read any substantial info yet on how to go about it. Any help will be greatly appreciated. Thanks. Quote Link to comment Share on other sites More sharing options...
mattlandis Posted May 8, 2009 Report Share Posted May 8, 2009 Hi All, Anybody made this work? I have emailed support with regards to this and I haven't received any answer yet from them. There maybe people here who have successfully integrated their CDR with an SQL package. Send some help this way please. Basically, I would want the CDR to write onto a table whether on the same or different machine. I have read the possibility of this on other threads in this forum but have not really read any substantial info yet on how to go about it. Any help will be greatly appreciated. Thanks. Hello, At this moment pbxnsip doesn't inject into a sql instance that i know of. You could possibly put the cdr text file and pull it into sql using a scheudled task from sql? just a thot. matt Quote Link to comment Share on other sites More sharing options...
pbx support Posted May 8, 2009 Report Share Posted May 8, 2009 Hello, At this moment pbxnsip doesn't inject into a sql instance that i know of. You could possibly put the cdr text file and pull it into sql using a scheudled task from sql? just a thot. matt Version 3.3.x is capable of writing the CDR to MySQL (no MS-SQL support yet). Please PM support@pbxnsip.com for the documentation on how to use it. Quote Link to comment Share on other sites More sharing options...
Vodia PBX Posted May 8, 2009 Report Share Posted May 8, 2009 Version 3.3.x is capable of writing the CDR to MySQL (no MS-SQL support yet). Please PM support@pbxnsip.com for the documentation on how to use it. Also check out email2db.com or any other tool that can read an email and extract information into a database. Quote Link to comment Share on other sites More sharing options...
chemicalx Posted May 11, 2009 Author Report Share Posted May 11, 2009 thanks for the reply guys. i will be sending pm to support@pbxnsip.com shortly for the documentation. at the same time, looking at other suggestions as well. we are currently getting our cdrs from the xml files on the cdr folder. however, we noticed over the past versions, the tags have changed and we are looking for ways that will somewhat make it easier. thanks all the same. Quote Link to comment Share on other sites More sharing options...
maslym Posted July 28, 2009 Report Share Posted July 28, 2009 Hi pbxnsip support, I followed the instructions on your wiki website to set up the latest release (3.4.0.3201) to send CDR to MySQL database and nothing happened. From the pbxnsip logfile, I could see "SQL client: Connect to xxx.xxx.xxx.xxx:3306". But there were no records dumped into database table. I tried tcpdump on the MySQL port on the database server and there were no packets sent from the pbxnsip server. It seems to me that pbxnsip server never sent CDR out. Any idea? Thank you, Yimin Quote Link to comment Share on other sites More sharing options...
maslym Posted July 28, 2009 Report Share Posted July 28, 2009 Now I could see data sent from the pbxnsip to the database server after I changed the password in user table to old format. But still records are not dumped into database table. I was wondering if the table format is mismatched. Thanks, Yimin Quote Link to comment Share on other sites More sharing options...
pbx support Posted July 29, 2009 Report Share Posted July 29, 2009 Now I could see data sent from the pbxnsip to the database server after I changed the password in user table to old format. But still records are not dumped into database table. I was wondering if the table format is mismatched.Thanks, Yimin This could be purely some permission issues on the the MySQL tables. You should be able to turn on some logging on the MySQL and see what the error is. Quote Link to comment Share on other sites More sharing options...
maslym Posted July 29, 2009 Report Share Posted July 29, 2009 The mysql user used by pbxnsip has all privileges on the CDR table. I can see the INSERT statement sent from pbxnsip. But still the record cannot be saved in the table. Following is the tcpdump output. ---------------------------------------------------------------------------------------------------------------------------------------- 13:35:05.671884 IP 216.251.151.100.52481 > 64.254.145.211.3306: S 517339025:517339025(0) win 5840 <mss 1460,sackOK,timestamp 345459692 0,nop,wscale 2> 0x0000 0050 ba8f e548 001e bed8 7540 0800 4500 .P...H....u@..E. 0x0010 003c bc7d 4000 3a06 410d d8fb 9764 40fe .<.}@.:.A....d@. 0x0020 91d3 cd01 0cea 1ed5 f791 0000 0000 a002 ................ 0x0030 16d0 9d2d 0000 0204 05b4 0402 080a 1497 ...-............ 0x0040 4bec 0000 0000 0103 0302 K......... 13:35:05.671930 IP 64.254.145.211.3306 > 216.251.151.100.52481: S 860864178:860864178(0) ack 517339026 win 5792 <mss 1460,sackOK,timestamp 2580708677 345459692,nop,wscale 0> 0x0000 0078 0c00 0940 0050 ba8f e548 0800 4500 .x...@.P...H..E. 0x0010 003c 0000 4000 4006 f78a 40fe 91d3 d8fb .<..@.@...@..... 0x0020 9764 0cea cd01 334f beb2 1ed5 f792 a012 .d....3O........ 0x0030 16a0 9434 0000 0204 05b4 0402 080a 99d2 ...4............ 0x0040 7d45 1497 4bec 0103 0300 }E..K..... 13:35:05.706017 IP 216.251.151.100.52481 > 64.254.145.211.3306: . ack 1 win 1460 <nop,nop,timestamp 345459701 2580708677> 0x0000 0050 ba8f e548 001e bed8 7540 0800 4500 .P...H....u@..E. 0x0010 0034 bc7e 4000 3a06 4114 d8fb 9764 40fe .4.~@.:.A....d@. 0x0020 91d3 cd01 0cea 1ed5 f792 334f beb3 8010 ..........3O.... 0x0030 05b4 d3dc 0000 0101 080a 1497 4bf5 99d2 ............K... 0x0040 7d45 }E 13:35:05.706706 IP 64.254.145.211.3306 > 216.251.151.100.52481: P 1:61(60) ack 1 win 5792 <nop,nop,timestamp 2580708712 345459701> 0x0000 0078 0c00 0940 0050 ba8f e548 0800 4508 .x...@.P...H..E. 0x0010 0070 c3d4 4000 4006 337a 40fe 91d3 d8fb .p..@.@.3z@..... 0x0020 9764 0cea cd01 334f beb3 1ed5 f792 8018 .d....3O........ 0x0030 16a0 dece 0000 0101 080a 99d2 7d68 1497 ............}h.. 0x0040 4bf5 3800 0000 0a35 2e30 2e33 372d 6c6f K.8....5.0.37-lo 0x0050 6700 3c00 0000 3a3d 604f 4961 2f60 002c g.<...:=`OIa/`., 0x0060 a208 0200 0000 0000 0000 0000 0000 0000 ................ 0x0070 007a 635f 362d 7e56 725a 4b4a 7100 .zc_6-~VrZKJq. 13:35:05.743336 IP 216.251.151.100.52481 > 64.254.145.211.3306: . ack 61 win 1460 <nop,nop,timestamp 345459710 2580708712> 0x0000 0050 ba8f e548 001e bed8 7540 0800 4500 .P...H....u@..E. 0x0010 0034 bc7f 4000 3a06 4113 d8fb 9764 40fe .4..@.:.A....d@. 0x0020 91d3 cd01 0cea 1ed5 f792 334f beef 8010 ..........3O.... 0x0030 05b4 d374 0000 0101 080a 1497 4bfe 99d2 ...t........K... 0x0040 7d68 }h 13:35:05.745075 IP 216.251.151.100.52481 > 64.254.145.211.3306: P 1:68(67) ack 61 win 1460 <nop,nop,timestamp 345459710 2580708712> 0x0000 0050 ba8f e548 001e bed8 7540 0800 4500 .P...H....u@..E. 0x0010 0077 bc80 4000 3a06 40cf d8fb 9764 40fe .w..@.:.@....d@. 0x0020 91d3 cd01 0cea 1ed5 f792 334f beef 8018 ..........3O.... 0x0030 05b4 4854 0000 0101 080a 1497 4bfe 99d2 ..HT........K... 0x0040 7d68 3f00 0001 85a6 0300 0000 0001 0800 }h?............. 0x0050 0000 0000 0000 0000 0000 0000 0000 0000 ................ 0x0060 0000 0000 0000 7072 6f76 6973 696f 6e00 ......provision. 0x0070 149f 1143 99fc 931b a0ff 6bfd b51f 1edc ...C......k..... 0x0080 9fd3 c299 04 ..... 13:35:05.745254 IP 64.254.145.211.3306 > 216.251.151.100.52481: . ack 68 win 5792 <nop,nop,timestamp 2580708750 345459710> 0x0000 0078 0c00 0940 0050 ba8f e548 0800 4508 .x...@.P...H..E. 0x0010 0034 c3d5 4000 4006 33b5 40fe 91d3 d8fb .4..@.@.3.@..... 0x0020 9764 0cea cd01 334f beef 1ed5 f7d5 8010 .d....3O........ 0x0030 16a0 c21f 0000 0101 080a 99d2 7d8e 1497 ............}... 0x0040 4bfe K. 13:35:05.745294 IP 64.254.145.211.3306 > 216.251.151.100.52481: P 61:66(5) ack 68 win 5792 <nop,nop,timestamp 2580708750 345459710> 0x0000 0078 0c00 0940 0050 ba8f e548 0800 4508 .x...@.P...H..E. 0x0010 0039 c3d6 4000 4006 33af 40fe 91d3 d8fb .9..@.@.3.@..... 0x0020 9764 0cea cd01 334f beef 1ed5 f7d5 8018 .d....3O........ 0x0030 16a0 c30f 0000 0101 080a 99d2 7d8e 1497 ............}... 0x0040 4bfe 0100 0002 fe K...... 13:35:05.783376 IP 216.251.151.100.52481 > 64.254.145.211.3306: P 68:79(11) ack 66 win 1460 <nop,nop,timestamp 345459719 2580708750> 0x0000 0050 ba8f e548 001e bed8 7540 0800 4500 .P...H....u@..E. 0x0010 003f bc81 4000 3a06 4106 d8fb 9764 40fe .?..@.:.A....d@. 0x0020 91d3 cd01 0cea 1ed5 f7d5 334f bef4 8018 ..........3O.... 0x0030 05b4 8b9e 0000 0101 080a 1497 4c07 99d2 ............L... 0x0040 7d8e 0700 0000 0272 6164 6975 73 }......radius 13:35:05.783538 IP 64.254.145.211.3306 > 216.251.151.100.52481: P 66:92(26) ack 79 win 5792 <nop,nop,timestamp 2580708789 345459719> 0x0000 0078 0c00 0940 0050 ba8f e548 0800 4508 .x...@.P...H..E. 0x0010 004e c3d7 4000 4006 3399 40fe 91d3 d8fb .N..@.@.3.@..... 0x0020 9764 0cea cd01 334f bef4 1ed5 f7e0 8018 .d....3O........ 0x0030 16a0 d863 0000 0101 080a 99d2 7db5 1497 ...c........}... 0x0040 4c07 1600 0003 ff13 0423 3038 5330 3142 L........#08S01B 0x0050 6164 2068 616e 6473 6861 6b65 ad.handshake 13:35:05.783554 IP 64.254.145.211.3306 > 216.251.151.100.52481: F 92:92(0) ack 79 win 5792 <nop,nop,timestamp 2580708789 345459719> 0x0000 0078 0c00 0940 0050 ba8f e548 0800 4508 .x...@.P...H..E. 0x0010 0034 c3d8 4000 4006 33b2 40fe 91d3 d8fb .4..@.@.3.@..... 0x0020 9764 0cea cd01 334f bf0e 1ed5 f7e0 8011 .d....3O........ 0x0030 16a0 c1c4 0000 0101 080a 99d2 7db5 1497 ............}... 0x0040 4c07 L. 13:35:05.783570 IP 64.254.145.211.3306 > 216.251.151.100.52481: R 93:93(0) ack 79 win 5792 <nop,nop,timestamp 2580708789 345459719> 0x0000 0078 0c00 0940 0050 ba8f e548 0800 4508 .x...@.P...H..E. 0x0010 0034 c3d9 4000 4006 33b1 40fe 91d3 d8fb .4..@.@.3.@..... 0x0020 9764 0cea cd01 334f bf0f 1ed5 f7e0 8014 .d....3O........ 0x0030 16a0 c1c0 0000 0101 080a 99d2 7db5 1497 ............}... 0x0040 4c07 L. 13:35:05.819090 IP 216.251.151.100.52481 > 64.254.145.211.3306: P 79:213(134) ack 92 win 1460 <nop,nop,timestamp 345459729 2580708789> 0x0000 0050 ba8f e548 001e bed8 7540 0800 4500 .P...H....u@..E. 0x0010 00ba bc82 4000 3a06 408a d8fb 9764 40fe ....@.:.@....d@. 0x0020 91d3 cd01 0cea 1ed5 f7e0 334f bf0e 8018 ..........3O.... 0x0030 05b4 1927 0000 0101 080a 1497 4c11 99d2 ...'........L... 0x0040 7db5 8200 0000 0349 4e53 4552 5420 494e }......INSERT.IN 0x0050 544f 2072 6164 6163 6374 5f70 6278 6e73 TO.radacct_pbxns 0x0060 6970 2028 6073 7461 7274 5f64 6174 655f ip.(`start_date_ 0x0070 7469 6d65 602c 6065 7874 656e 7369 6f6e time`,`extension 0x0080 602c 6072 656d 6f74 655f 6361 6c6c 5f69 `,`remote_call_i 0x0090 6460 2c60 6475 7261 7469 6f6e 6029 2056 d`,`duration`).V 0x00a0 414c 5545 5320 2827 3230 3039 3037 3239 ALUES.('20090729 0x00b0 3133 3335 3036 272c 2739 3035 272c 272a 133506','905','* 0x00c0 3937 272c 2731 2729 97','1') 13:35:05.819111 IP 64.254.145.211.3306 > 216.251.151.100.52481: R 860864270:860864270(0) win 0 0x0000 0078 0c00 0940 0050 ba8f e548 0800 4500 .x...@.P...H..E. 0x0010 0028 0000 4000 4006 f79e 40fe 91d3 d8fb .(..@.@...@..... 0x0020 9764 0cea cd01 334f bf0e 0000 0000 5004 .d....3O......P. 0x0030 0000 a065 0000 ...e.. 13:35:05.819638 IP 216.251.151.100.52481 > 64.254.145.211.3306: F 213:213(0) ack 92 win 1460 <nop,nop,timestamp 345459729 2580708789> 0x0000 0050 ba8f e548 001e bed8 7540 0800 4500 .P...H....u@..E. 0x0010 0034 bc83 4000 3a06 410f d8fb 9764 40fe .4..@.:.A....d@. 0x0020 91d3 cd01 0cea 1ed5 f866 334f bf0e 8011 .........f3O.... 0x0030 05b4 d220 0000 0101 080a 1497 4c11 99d2 ............L... 0x0040 7db5 }. 13:35:05.819645 IP 64.254.145.211.3306 > 216.251.151.100.52481: R 860864270:860864270(0) win 0 0x0000 0078 0c00 0940 0050 ba8f e548 0800 4500 .x...@.P...H..E. 0x0010 0028 0000 4000 4006 f79e 40fe 91d3 d8fb .(..@.@...@..... 0x0020 9764 0cea cd01 334f bf0e 0000 0000 5004 .d....3O......P. 0x0030 0000 a065 0000 ...e.. 13:35:05.820597 IP 216.251.151.100.52481 > 64.254.145.211.3306: . ack 93 win 1460 <nop,nop,timestamp 345459729 2580708789> 0x0000 0050 ba8f e548 001e bed8 7540 0800 4500 .P...H....u@..E. 0x0010 0034 bc84 4000 3a06 410e d8fb 9764 40fe .4..@.:.A....d@. 0x0020 91d3 cd01 0cea 1ed5 f867 334f bf0f 8010 .........g3O.... 0x0030 05b4 d21f 0000 0101 080a 1497 4c11 99d2 ............L... 0x0040 7db5 }. 13:35:05.820603 IP 64.254.145.211.3306 > 216.251.151.100.52481: R 860864271:860864271(0) win 0 0x0000 0078 0c00 0940 0050 ba8f e548 0800 4500 .x...@.P...H..E. 0x0010 0028 0000 4000 4006 f79e 40fe 91d3 d8fb .(..@.@...@..... 0x0020 9764 0cea cd01 334f bf0f 0000 0000 5004 .d....3O......P. 0x0030 0000 a064 0000 ...d.. Quote Link to comment Share on other sites More sharing options...
pbx support Posted July 29, 2009 Report Share Posted July 29, 2009 I do not think the TCP dump would help much here. I would suggest to download the "MySQL Administrator" tool for windows (if you are on windows) and enable logging on the MySQL server (Startup Variables->Log Files Page). That should tell you what the problem is. The mysql user used by pbxnsip has all privileges on the CDR table. I can see the INSERT statement sent from pbxnsip. But still the record cannot be saved in the table. Following is the tcpdump output. Quote Link to comment Share on other sites More sharing options...
maslym Posted July 29, 2009 Report Share Posted July 29, 2009 Hi pbxnsip support, I think I figured out the cause of the problem. The pbxnsip server is connecting to MySQL database as anonymous user. I set up a new mysql server on a window machine and created an anonymous user on it and now I can see CDR in the database. It seems the pbxnsip server doesn't use the username and password provided. Please check. BTW, usually our MySQL database is running on linux server. Thank you, Yimin Quote Link to comment Share on other sites More sharing options...
pbx support Posted July 30, 2009 Report Share Posted July 30, 2009 Hi pbxnsip support, I think I figured out the cause of the problem. The pbxnsip server is connecting to MySQL database as anonymous user. I set up a new mysql server on a window machine and created an anonymous user on it and now I can see CDR in the database. It seems the pbxnsip server doesn't use the username and password provided. Please check. BTW, usually our MySQL database is running on linux server. Thank you, Yimin That is interesting. Have you setup the global variables for mysql_user_name, mysql_password etc as explained under "configuring the PBX for MySQL" section https://www.pbxnsipsupport.com/index.php?_m...kbarticleid=389? Quote Link to comment Share on other sites More sharing options...
maslym Posted July 30, 2009 Report Share Posted July 30, 2009 That is interesting. Have you setup the global variables for mysql_user_name, mysql_password etc as explained under "configuring the PBX for MySQL" section https://www.pbxnsipsupport.com/index.php?_m...kbarticleid=389? Yes, I did. I set up all four global variables and I could find them in pbx.xml file. I did further test by removing the user from the MySQL server and the CDR was still dumped into the database table. But when I removed the anonymous user from the MySQL server and kept the user, there was no CDR dumped into the database table. This makes me believe that the pbxnsip server is using anonymous user to talk to the MySQL database. Please check. Yimin Quote Link to comment Share on other sites More sharing options...
Ryan Posted September 15, 2009 Report Share Posted September 15, 2009 I can confirm this... Even after setting the correct login credentials in pbx.xml, and testing the permissions manually, the pbx still cannot write to the database, unless I start MySQL with --skip-grant-tables. This is NOT feasible as noone wants to run MySQL without any kind of user authentication. When will this be fixed? Ryan Quote Link to comment Share on other sites More sharing options...
tanerultanir Posted October 12, 2009 Report Share Posted October 12, 2009 I use this procedures for CDR to MySQL http://localhost/reg_status.htm?save=save&..._db_name=DBNAME http://localhost/reg_status.htm?save=save&...name=TABLE_NAME http://localhost/reg_status.htm?save=save&..._user_name=root http://localhost/reg_status.htm?save=save&...d=ROOT_PASSWORD manualy change pbx.xml; <cdr_format>$f$t$i$v$m$l$R$S$C$A$x$y$F$T$r$E$W$w$B$b$e$o$c$d$s</cdr_format> beware of "disable grant tables" Also you can use this script to create DB and table; SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0; SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0; CREATE SCHEMA IF NOT EXISTS `PBX3` ; USE `PBX3`; -- ----------------------------------------------------- -- Table `DBNAME`.`TABLE_NAME` -- ----------------------------------------------------- CREATE TABLE IF NOT EXISTS `DBNAME`.`TABLE_NAME` ( `id` INT(11) NOT NULL AUTO_INCREMENT , `sip_from` VARCHAR(255) NULL , `sip_to` VARCHAR(255) NULL , `call_id` VARCHAR(255) NULL , `call_type` VARCHAR(45) NULL , `domain` VARCHAR(45) NULL , `lang` VARCHAR(45) NULL , `account` VARCHAR(45) NULL , `start_time` VARCHAR(45) NULL , `talk_duration` INT(11) NULL , `ring_duration` INT(11) NULL , `orig_trunk` VARCHAR(45) NULL , `dest_trunk` VARCHAR(45) NULL , `calling` VARCHAR(255) NULL , `called` VARCHAR(255) NULL , `redirect_dest` VARCHAR(255) NULL , `hold_duration` INT(11) NULL , `wait_duration` INT(11) NULL , `start_date_time` VARCHAR(45) NULL , `time` VARCHAR(45) NULL , `date` VARCHAR(45) NULL , `extension` VARCHAR(45) NULL , `direction` VARCHAR(1) NULL , `remote_call_id` VARCHAR(45) NULL , `duration` INT(11) NULL , `extn_duration` INT(11) NULL , `record_location` VARCHAR(255) NULL , PRIMARY KEY (`id`) ) ENGINE = MyISAM; SET SQL_MODE=@OLD_SQL_MODE; SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS; SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS; Quote Link to comment Share on other sites More sharing options...
hosted Posted October 25, 2009 Report Share Posted October 25, 2009 I just cant get this to work properly. If I disable security so anything can login it works fine. even with disabling table grants it fails. I can login from any SQL client just fine. however every time pbxnsip connects mysql logins and when it does a use database; mysql sends back Got packets out of order.. any idea why? I have a couple clients doing it. Dont understand what my problem is :/ even if I use a bad password it fails the login, but for some reason pbxnsip still asks for the use database; and it still says packets our of order.. kind of leaves me to think that pbxnsip is not logging into mysql period. Quote Link to comment Share on other sites More sharing options...
Philj Posted December 23, 2010 Report Share Posted December 23, 2010 FYI - after following this post and instructions and not receiving any data, I emailed support and found that MYSQL support is unavailable in version 4.x. Nice. I just cant get this to work properly. If I disable security so anything can login it works fine. even with disabling table grants it fails. I can login from any SQL client just fine. however every time pbxnsip connects mysql logins and when it does a use database; mysql sends back Got packets out of order.. any idea why? I have a couple clients doing it. Dont understand what my problem is :/ even if I use a bad password it fails the login, but for some reason pbxnsip still asks for the use database; and it still says packets our of order.. kind of leaves me to think that pbxnsip is not logging into mysql period. Quote Link to comment Share on other sites More sharing options...
mattlandis Posted December 23, 2010 Report Share Posted December 23, 2010 something like this should be in release notes. Matt Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.